Portfolio backend design for ingesting messaging activity events and exposing recipient timelines, message timelines, and campaign summaries.

This repository contains the design, API contract, and initial Go/PostgreSQL implementation for the project:

• DESIGN.md explains the domain model, data model, tradeoffs, idempotency behavior, pagination model, security baseline, operations, and testing strategy.
• openapi.yaml defines the REST API contract.
• db/migrations/001_create_message_events.sql contains the initial PostgreSQL schema.
• cmd/api contains the HTTP API executable.
• internal/activity contains validation, idempotency, and pagination behavior.
• internal/postgres contains explicit SQL repository methods.

• Immutable event ingestion for messaging activity.
• Provider-scoped idempotency with conflict detection.
• Chronological timelines ordered by occurred_at, id.
• Cursor-based pagination and its late-arrival consistency tradeoff.
• Raw campaign event summaries with explicit analytics semantics.
• PostgreSQL schema constraints and indexes matched to query patterns.
• Practical production boundaries without overengineering the sample.

• POST /events
• GET /recipients/{recipient_id}/activity
• GET /messages/{message_id}/timeline
• GET /campaigns/{campaign_id}/summary
• GET /healthz
• GET /readyz

event_id is unique per provider, not globally unique. The service treats (provider, event_id) as the idempotency key.

Duplicate ingestion only returns 200 OK when the canonical payload matches the existing event. A reused idempotency key with different event data returns 409 Conflict.

Campaign summaries return raw event counts. Repeated opens and clicks are counted as repeated observations. Unique recipient counts and derived rates are future analytics extensions.

Timeline pagination is stable for a fixed dataset but not a snapshot guarantee. Late-arriving events with older occurred_at values may require clients to refresh from the beginning.

The intended implementation stack is:

• Go
• PostgreSQL
• REST over HTTP
• Docker Compose
• SQL migrations
• Behavior-focused automated tests

The implementation should stay small and explicit: HTTP handlers for request and response concerns, service logic for validation and idempotency decisions, and repository methods with explicit SQL.

Start PostgreSQL and the API with Docker Compose:

docker compose up --build

The API listens on http://localhost:8080.

For local Go development without running the API container, start PostgreSQL, apply the migration, then run the server:

psql "$DATABASE_URL" -f db/migrations/001_create_message_events.sql
go run ./cmd/api

By default the server uses:

ADDR=:8080
DATABASE_URL=postgres://postgres:postgres@localhost:5432/messaging_activity?sslmode=disable

go test ./...

The same command is available as:

make test

PostgreSQL integration tests are opt-in. Start the Compose database, then pass a test database URL:

docker compose up -d postgres
TEST_DATABASE_URL=postgres://postgres:postgres@localhost:5432/messaging_activity?sslmode=disable go test ./internal/postgres

To run the full suite, including database-backed end-to-end tests, pass the same environment variable to go test ./...:

TEST_DATABASE_URL=postgres://postgres:postgres@localhost:5432/messaging_activity?sslmode=disable go test ./...

Convenience targets are also available:

make docker-up
make test-db
make test-all-db