The capstone. Every deferred problem from the last six apps comes due at once: the queue that app 6's retro promised, tenant isolation for the first time, and observability/CI/CD treated as first-class requirements instead of an afterthought — because at this point, "it works on my machine" stops being a sufficient definition of done.

1. Problem framing

Companies (tenants) sign up, invite teammates, and send usage events from their own product via an API key. Each tenant needs a dashboard showing counts of their events over time — and must never, under any circumstance, see another tenant's data, even if a future engineer writes a buggy query.

2. PRD

Goal: Let a tenant ingest usage events via API key and view an aggregated analytics dashboard, with strict data isolation between tenants.

Non-goals:

• Real-time streaming dashboards (a few minutes of ingestion lag is acceptable)
• Custom query builder / ad-hoc SQL for tenants
• Usage-based billing/metering calculations
• Cross-tenant benchmarking features

User stories:

• As a tenant owner, I want to invite teammates with a role (owner/admin/member).
• As a developer at a tenant, I want to generate an API key and send events via a simple HTTP call.
• As any tenant member, I want a dashboard of event counts by day.
• As the platform, tenant A must never be able to see tenant B's data — even given an application bug.

Success metrics:

• Ingestion endpoint accepts an event in under 100ms (p99), independent of dashboard load
• Dashboard reflects new events within 5 minutes
• Zero cross-tenant data exposure, verified by an explicit isolation test in CI

MVP cut line: signup + team invites + API keys + event ingestion + daily aggregate dashboard + tenant isolation. Streaming dashboards and billing are deferred.

3. Wireframes & UX flow

Screen inventory:

4. HLD

Three moving pieces now, not one: the web app, the ingestion endpoint (technically part of the same Next.js app, but conceptually distinct — it's public, high-volume, and has different latency requirements), and a persistent worker process consuming the queue — the same "second deployable service" pattern app 5 introduced for the WS server, now used for background aggregation instead of realtime fan-out.

5. Data model / ERD

Every single table carries tenant_id — that's the shared-tables isolation model (ADR-1). EVENT is raw and high-volume; AGGREGATED_STAT is the small, fast-to-read rollup the dashboard actually queries — the same "materialize for reads" instinct as app 6's FEED_ITEM, applied here to analytics instead of a social feed.

6. API design + flow mapping

Flow: ingest an event — fast accept, async aggregate

Flow: worker aggregation — idempotent under at-least-once delivery

Queues like this one guarantee at-least-once delivery, not exactly-once — the same job can be delivered twice. Checking event.processed before aggregating is what makes redelivery safe; without it, a redelivered job would double-count that event forever.

7. LLD

Module breakdown:

app/api/ingest/route.ts        — public ingestion endpoint
app/api/dashboard/route.ts     — reads AGGREGATED_STAT only, RLS-scoped
worker/aggregate.ts            — standalone process consuming the BullMQ queue
lib/tenant-context.ts          — sets Postgres session tenant_id for RLS on every request

Edge cases worth naming explicitly:

• API key storage — never store the raw key; store a hash (key_hash) and a short key_prefix for display ("sk_live_ab12…"), same principle as password storage.
• Idempotent aggregation — handled above via the processed flag; this is the queue-era version of app 4's webhook idempotency problem — at- least-once delivery shows up everywhere async processing does.
• Tenant isolation is enforced twice — once in application code (every query includes WHERE tenant_id = :currentTenant) and once at the database layer via Postgres Row-Level Security, which rejects any query that doesn't match the session's tenant context. Two independent layers, so one missed WHERE clause in application code doesn't leak data.
• Ingestion under a bad/revoked key — reject with 401 before touching the database for anything except the key lookup itself; don't leak whether a key format is "close" to valid.
• Queue backpressure — if the worker falls behind, the queue grows, but ingestion keeps succeeding (that's the entire point of decoupling ingest from aggregation) — the dashboard just shows slightly stale data until the worker catches up.

8. NFRs

• Performance: ingestion p99 under 100ms (insert + enqueue only, no aggregation on the critical path); dashboard reads under 200ms (reading a small precomputed table, never scanning raw events).
• Security / authz: Postgres Row-Level Security as defense-in-depth beneath application-level tenant scoping; API keys hashed at rest; per-key rate limiting reusing the Redis token-bucket pattern from app 6.
• Observability — what pages someone at 3am: queue depth exceeding a threshold (worker falling behind or down), ingestion error-rate spike, and a single tenant's ingestion rate suddenly 100x their baseline (possible runaway integration or abuse). Structured JSON logs carry tenant_id and request_id on every line, so an incident can be traced from a customer report back to the exact ingest → queue → aggregate path.
• Cost: raw EVENT rows are retained for 90 days then deleted (a scheduled job, not a forgotten table growing forever); AGGREGATED_STAT is retained indefinitely since it's orders of magnitude smaller. This is a stated decision made during planning, not a surprise discovered later when the events table hits four terabytes.

9. ADRs

ADR-1: Shared tables + tenant_id + Postgres RLS, not database-per-tenant
Context: database-per-tenant gives the strongest isolation but means
         migrating, backing up, and monitoring N databases instead of one.
Decision: one database, tenant_id on every table, Row-Level Security
         policies enforced by Postgres itself as a second layer beneath
         application-level filtering.
Consequence: much simpler operations at this scale; isolation now depends
             on RLS policies being correctly defined once, rather than N
             separate databases being correctly provisioned every time.

ADR-2: Ingestion writes raw + enqueues async aggregation
Context: aggregating synchronously on every ingested event would make the
         public ingestion endpoint's latency depend on aggregation cost.
Decision: ingestion only inserts the raw event and enqueues a job; a
         separate worker does the aggregation.
Consequence: finally justifies the queue infrastructure flagged as needed
             back in app 6's retro — this is the app whose requirements
             actually force it, not a default reached for early.

ADR-3: Dashboard reads only ever hit AGGREGATED_STAT, never EVENT
Context: scanning raw events live for a dashboard chart doesn't scale past
         a small ingestion volume.
Decision: the dashboard query only ever touches the precomputed rollup
         table.
Consequence: same "materialize for reads" tradeoff as app 6 — the read path
             stays fast regardless of ingestion volume, at the cost of
             eventual (not immediate) consistency between an ingested event
             and its appearance on the dashboard.

ADR-4: Aggregation is idempotent via a processed flag on the source event
Context: BullMQ (like most queues) guarantees at-least-once delivery.
Decision: the worker checks event.processed before aggregating and skips
         redelivered jobs.
Consequence: safe under redelivery, at the cost of one extra column and a
             row lock during aggregation.

ADR-5: Raw event retention: 90 days, then deleted
Context: an unbounded raw-events table is both a cost problem and,
         eventually, a query-performance problem.
Decision: a scheduled cleanup job deletes EVENT rows older than 90 days;
         AGGREGATED_STAT rows are kept indefinitely.
Consequence: historical daily/weekly/monthly counts remain available
             forever even though the raw events they were built from don't
             — an explicit, planned tradeoff instead of an accidental one.

10. Deploy plan

• Environments: local (Postgres + Redis via Docker, worker run alongside the app), Preview (per PR — app, worker, and RLS policies all deployed together), Production.
• CI: typecheck, lint, tests — including an explicit tenant isolation test: seed two tenants, attempt a cross-tenant read as tenant A against tenant B's data, assert it returns nothing, both at the application-query level and with RLS enforced. Migrations that drop or alter columns require a manual approval step rather than auto-applying, since a bad migration here risks every tenant's data at once, not just one app's.
• Definition of done: sign up a tenant, invite a teammate, generate a key, send events via curl, see the dashboard update within a few minutes — and the isolation test passes in CI.
• Rollback: the app and worker roll back independently; queue jobs already enqueued keep processing against whichever worker version is currently running, so job payloads are kept backward-compatible across deploys rather than assuming instant, synchronized upgrades.

11. Retro — "at 10x"

At 10x ingestion volume, the single Postgres EVENT table and the queue become the bottleneck together — I'd look at partitioning EVENT by tenant or by time, and likely move ingestion ahead of Postgres entirely onto a purpose-built ingestion pipeline (Kafka or similar) with Postgres only receiving the already-aggregated result. At 10x the tenant count, a different problem shows up: a noisy-neighbor tenant with a traffic spike degrading ingestion latency for everyone else sharing the same database and worker pool — the fix is per-tenant rate limiting and quotas on the ingestion endpoint (the same Redis token-bucket pattern from app 6, now applied per-tenant instead of per-user), and eventually separate resource pools for the largest customers. Both of these are exactly the kind of answer that's only credible because the six apps before this one already established the underlying patterns — a queue, a token bucket, a materialized read table — this app just recombines them.