🏛️ Architecture & Design

📋 Summary

Midshift is a single web application (a "modular monolith") built on Next.js and hosted on Vercel, with Supabase providing the database, logins and file storage. Each client gets its own copy: its own Vercel project and its own Supabase project, so client data is never mixed, but every upgrade, database change and configuration step must be repeated once per client by hand. The code is organised sensibly into pages, server request handlers and a shared library of business rules, and the business rules that matter most (deck parsing, file-name matching, link building) sit in well-tested, self-contained modules. The main structural weaknesses are inconsistency in where data is written (some screens write straight to the database from the browser, bypassing the server-side rules other screens go through), multi-step changes that are not wrapped in database transactions, a handful of very dense request handlers, and a large in-repository test double (src/app/qa/store.ts) that must be kept in lock-step with every API route by hand. At today's scale (one agency team per deployment) performance is adequate; several queries load whole tables without limits and will degrade, and in places silently truncate, as data grows.

🎯 Rating

🟡 ●●●○○ 3 / 5 — Amber. A coherent, conventional architecture with clear module boundaries and unusually well-documented intent, held back by inconsistent write paths, no transactional integrity for multi-row changes, per-client manual operations, and several unbounded queries that will not scale gracefully.

🔍 Findings

1. Architectural style and boundaries

The style is a modular monolith deployed as serverless functions: one codebase, one build, one deployment unit per client; no internal services, queues or event bus. [Observed] There is no separate service layer or repository layer between route handlers and Supabase: handlers and pages call supabase.from(...) directly (the five most-queried tables are campaigns ×103, ad_units ×97, users ×90, unit_files ×43, campaign_members ×28 call sites). [Observed] grep -rho "\.from('[a-z_]*')" src | sort | uniq -c. This is idiomatic for Next.js + Supabase and keeps code direct, but it means database shape is spread across ~170 files (see Module 13).

The domain library is the strongest part of the design: rules that are easy to get wrong are isolated and pure — copy-deck-parser.ts (85% covered), asset-match.ts (98%), utm-template.ts (100%), retention.ts, discipline.ts, placement.ts — and are explicitly declared as the "one rule" that every caller reads. [Observed] BRIEF coverage facts; ARCHITECTURE.md §1 invariants, verified against imports for asset-match (used by upload-previews, review files, supplier register and render-push).

2. Architecture diagram

3. Data model

45 tables in supabase/baseline.sql [Observed]. The core is campaigns → ad_units → (ad_unit_frames, unit_files, unit_comments, approvals); everything is scoped by campaign_id with on delete cascade, and access is governed by campaign_members through RLS. The diagram shows the 20 most important entities and their foreign keys (verified line by line from create table/references clauses; later-added FKs at baseline.sql:2092, :2115, :2172).

Design observations:

• Derived, not stored, status. Campaign board status, the "final" file and each person's worklist are computed on read (src/lib/campaign-derive.ts, final-load.ts, for-you.ts). [Observed] This removes a class of stale-status bugs but moves cost to read time (see §6).
• Name as join key. ad_units.ad_name is the join between copy decks, uploaded file names and records (src/lib/asset-match.ts). [Observed] A business-meaningful natural key used for matching is fragile by nature; the code compensates with careful normalisation and an exact-match-first rule.
• Schema residue. Tables with no readers are kept deliberately (studio_crits, studio_crit_comments, app_errors, briefs/review_comments pipeline, checklist_items). [Observed] CLAUDE.md "Parked decisions"; ARCHITECTURE.md §10. Roughly a fifth of the schema is parked or orphaned, which adds cognitive load for a new team. [Inferred]

4. Pattern adherence and violations

Stated pattern (CLAUDE.md "Conventions"): writes RLS might reject go through an API route with getUser + canEditCampaign, then the service-role client. Measured adherence:

Other concrete examples:

• Non-atomic multi-step writes. There is exactly one RPC call in the app (service.rpc('log_share_link_access'), src/app/preview/[token]/page.tsx:51); every multi-row change is a sequence of separate PostgREST calls. Campaign creation in the browser inserts the campaign, then sections, then phases, then audiences (new-campaign-modal.tsx:107-200) — a failure part-way leaves a half-built campaign. The copy import deletes a unit's frames then re-inserts them (import-copy/route.ts:381-392); a failed insert leaves units without cards (the code reports it, but cannot roll back). The codebase compensates with hand-written "undo" writes (e.g. ad-unit PATCH restoring copy_status at route.ts:196-199; card-writes.ts Writes.one with an undo closure). [Observed] This is compensating-transaction logic in application code, which is correct only as long as every path remembers it.
• No optimistic concurrency control. The ad-unit PATCH reads current state and writes without a version/updated_at check; two people editing the same unit last-write-wins. [Observed] no updated_at/version predicate in src/app/api/campaigns/[id]/ad-units/[unitId]/route.ts. Optimistic UI on the client rolls back on server error (convention in CLAUDE.md), but cannot detect a concurrent overwrite. [Inferred]
• Dense handlers. The ad-unit PATCH is only 245 lines but has cyclomatic complexity 66 and up to ~12 sequential database round-trips per request (auth ×3–4, campaign kind, concept job, current row, frames, template, update, frame updates, undo, two activity writes). [Observed] BRIEF lizard facts; route.ts:31-227. Similar density: brief PATCH (CCN 64), review file PATCH (59), render-push (53), jobs POST (52), cron purge (44). Business rules have leaked into handlers rather than into src/lib, which is why API code is 0.9% unit-covered while src/lib is 67%.
• Worklist mega-function. src/lib/for-you.ts forYouItems (CCN 109) runs ≥7 sequential queries (for-you.ts:101-170), mixing user-client and service-client reads, and computes every person's Home list from raw rows each request.
• Errors from reads are often discarded. e.g. for-you.ts:119-127, cron/overdue/route.ts:44-50 destructure data and ignore error; a failing query yields "nothing to do" rather than an error. The write side is guarded by a test (src/lib/write-results.test.ts), the read side is not. [Observed]

Positive patterns worth recording: a schema-vs-code column test (src/lib/schema-columns.test.ts), a write-result test, a harness/route contract check (scripts/harness-contract.js), consistent error/permission helpers, and very thorough "why" comments (12,361 comment lines in src). [Observed]

5. Concurrency and execution model

The ARCHITECTURE.md topology section still describes one cron (ARCHITECTURE.md §2 table), while vercel.json defines two. [Observed] Docs/code drift.

6. Performance-sensitive paths and bottleneck candidates

Bounded reads that are done well: activity feeds are limited (history/page.tsx:25 .limit(500), admin/page.tsx:55 .limit(300)), the import-copy updates run in bounded concurrent chunks (import-copy/route.ts:325-328, 8 at a time), and storage removals are chunked by 100 (purge-review/route.ts:134). [Observed]

Real-world latency, throughput and database size: Not measurable from repository — requires access to Vercel function metrics and the Supabase project(s). [Unavailable]

7. Scalability characteristics

• Per tenant: sized for one agency team. Read paths assume the whole working set fits in one response (P1, P2); this is fine for tens of campaigns and will degrade, and in places truncate silently, past ~1,000 rows per query. [Inferred]
• Horizontal: stateless functions scale automatically on Vercel; the binding constraints are Postgres connection/PostgREST throughput and Storage API call volume (P4), both governed by the Supabase plan. [Inferred]
• Storage: review uploads >50 MB need the Supabase Pro plan; bucket cap 250 MB per file (migration 050) must be matched by a dashboard setting. [Observed] CLAUDE.md Migrations note; baseline.sql:1613.
• Across tenants: see §9 — scale-out in customers is linear operational effort, not code.

8. Coupling and cohesion

QA harness ships in production builds. The /qa/* pages are gated by notFound() in src/app/qa/layout.tsx:11, but layouts do not apply to route handlers. src/app/qa/api/[...path]/route.ts, src/app/qa/state/route.ts, src/app/qa/reset/route.ts and src/app/qa/demo/*/route.ts contain no NEXT_PUBLIC_QA check, and the production proxy does not block /qa. [Observed] They operate only on an in-memory fixture store (no production data or credentials), so the impact is exposure of invented fixture data and unneeded attack surface, but the code comment "this file never ships: the QA routes 404 unless NEXT_PUBLIC_QA=1" (src/app/qa/store.ts:157-158) is not true of the route handlers. [Inferred] that they answer on a production deployment; confirming requires a request against a live deployment.

9. Deployment model: single-tenant, one deployment per client

[Observed] ARCHITECTURE.md §2; docs/NEW_DEPLOYMENT.md §1 ("One codebase, many deployments — never a fork").

The model is a reasonable choice for a handful of enterprise clients but becomes the dominant operating cost beyond roughly 5–10 deployments without a migration runner and fleet-level deployment tooling. [Inferred]

🧾 Evidence

Commands run (from /home/user/midshift-audit):

• grep -n "create table" supabase/baseline.sql → 45 tables; grep -c "create policy" → 95; grep -c "create or replace function" → 9.
• FK extraction per table via awk '/create table if not exists <t> \(/,/^\);/' + grep references; plus grep -nE "add column.*references" supabase/baseline.sql (lines 2092, 2110, 2115, 2172).
• grep -rlE "@/lib/supabase/|@supabase/" src | grep -v ^src/lib/supabase | wc -l → 168; per module: client 24, server 114, service 98, middleware 1.
• grep -rl createServiceClient src/app/api | wc -l → 68 (67 route.ts); find src/app/api -name route.ts | wc -l → 83.
• Loop: route files without createServiceClient that call insert/update/delete/upsert → 13 files (listed in §4).
• Loop: browser-client files with write counts → 24 files, e.g. new-campaign-modal.tsx writes=5, ad-units-grid.tsx writes=5, utm-generator.tsx writes=2.
• grep -rn "\.rpc(" src → 1 call (src/app/preview/[token]/page.tsx:51).
• grep -rn "\.range(\|\.limit(" src/app src/lib | grep -v src/app/qa | wc -l → 17.
• find src/app/api -name route.ts | xargs wc -l | sort -rn → total 7,872; largest import-copy 436, review/files/[fileId] 314, render-push 260, ad-unit [unitId] 245.
• wc -l src/app/qa/store.ts → 3,349; find src/app/qa -name "*.ts*" | xargs wc -l → 5,553.
• grep -n "NEXT_PUBLIC_QA" src/app/qa/api/[...path]/route.ts src/app/qa/state/route.ts src/app/qa/reset/route.ts → no matches (gate only in src/app/qa/layout.tsx:11).
• Files read: src/proxy.ts, src/lib/supabase/{client,server,service,middleware,env}.ts, src/lib/api-auth.ts, src/lib/for-you.ts:95-175, src/lib/final-load.ts, src/lib/handover-zip.ts, src/app/api/cron/purge-review/route.ts, src/app/api/cron/overdue/route.ts, src/app/api/campaigns/[id]/ad-units/[unitId]/route.ts, src/app/api/campaigns/[id]/import-copy/route.ts (query lines), src/app/api/campaigns/[id]/upload-previews/route.ts (query lines), src/app/(app)/campaign/[id]/campaign-drop-zone.tsx:57-130, next.config.ts, vercel.json, ARCHITECTURE.md §§1–3, 9–11, docs/NEW_DEPLOYMENT.md headings.
• Complexity, coverage and duplication figures: BRIEF measured facts (lizard, vitest coverage, jscpd).

⚠️ Risks

✅ Recommendations