LevelFive-Studio rips out the cloud foundation and rebuilds helix-tribune on AWS

* docs: add CLAUDE.md for repo navigation

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* infra: scaffold SST + Fargate Dockerfile

* db: add Drizzle schema and migration, swap supabase-js dep

Translates the Supabase schema in backend/schema.sql to a Drizzle ORM
definition targeting AWS RDS/Aurora Postgres. Lays the foundation for
Stages D/E, which rewrite the routes and middleware off supabase-js.

- backend/src/db/schema.ts: 16 tables translated 1:1 with schema.sql.
  user_id columns that previously referenced auth.users(id) are now
  plain text (Clerk owns identity). RLS policies, the handle_new_user
  trigger, and Supabase grants are not modelled - access control moves
  to the Express layer.
- backend/drizzle.config.ts: drizzle-kit config with a dummy default
  DATABASE_URL so `generate` works without env setup.
- backend/drizzle/0000_init.sql: initial migration with all tables,
  indexes (including GIN on shared_with), check constraints, and
  foreign keys. Prepended CREATE EXTENSION pgcrypto. Manually appended
  the two circular FKs (documents.current_version_id and
  document_edits.chat_message_id) that Drizzle could not emit inline.
- backend/package.json: dropped @supabase/supabase-js; added
  drizzle-orm, drizzle-kit, pg, @types/pg, @clerk/backend. Added
  db:generate / db:migrate / db:push / db:studio scripts.

The legacy backend/schema.sql is kept in place until upstream merges
clear; Stage D will start removing supabase.ts and the route consumers.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* auth: swap Supabase verification for Clerk JWT + add Drizzle client

- Drop @supabase/supabase-js from middleware/auth.ts; verify Clerk JWTs
  with @clerk/backend (verifyToken), supporting both JWKS and offline
  jwtKey verification.
- First-request profile bootstrap (in-memory cache) replaces the old
  Supabase handle_new_user trigger.
- Add backend/src/lib/db.ts exporting Drizzle client + pg.Pool +
  withTransaction helper.
- Delete backend/src/lib/supabase.ts. Routes and several libs still
  import it; Stages E1/E2 will rewrite them.

tsc --noEmit fails (42 errors), all confined to files Stage E1/E2 will
rewrite. db.ts and middleware/auth.ts typecheck cleanly.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* routes: migrate simpler routes to Drizzle (Stage E1)

Rewrite user, downloads, workflows, and projectChat routes - plus the
access, userSettings, and userApiKeys libs - to query Postgres directly
via Drizzle instead of Supabase. Brings the backend tsc error count from
40 to 24; remaining errors are all in Stage E2 files (chat, projects,
documents, tabular, chatTools, documentVersions).

- Lib helper signatures keep a trailing optional `_db` arg so out-of-scope
  E2 callers don't all immediately break on extra-argument errors before
  Stage E2 lands.
- Workflows route now backfills sharer display names via the Clerk
  Backend API instead of the Supabase admin listUsers call.
- `/user/account` no longer calls supabase.auth.admin.deleteUser; it
  removes the local profile row and leaves Clerk-side deletion to a
  separate path.
- userApiKeys keeps the AES-GCM crypto unchanged; only the DB plumbing
  swapped to Drizzle's onConflictDoUpdate.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* routes: migrate heavy routes + chatTools to Drizzle (Stage E2)

Rewrites projects, documents, tabular, chat, chatTools, and
documentVersions to use Drizzle queries against the shared db
singleton. Drops the trailing _db?: unknown bridge parameter that
Stage E1 added to access/userSettings/userApiKeys helpers.

Behavioral notes:
- Member email->id lookups in projects.ts and tabular.ts now use
  Clerk's users.getUserList({ emailAddress: [...] }) and
  users.getUser(id) instead of Supabase's auth.admin.listUsers.
- chat.ts /chat OR-filter converted from Supabase's PostgREST .or()
  string syntax to Drizzle's or(eq, inArray).
- Dead buildTabularContext helper removed from tabular.ts (no
  callers anywhere).
- jsonb inserts retain as any casts to match Stage E1 pattern.

tsc --noEmit -p backend: 0 errors.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* storage+email: swap R2 for S3, Resend for SES (Stage F)

- Rewrite backend/src/lib/storage.ts to use native AWS S3 via the default credential chain (Fargate task role). Drop R2 endpoint, forcePathStyle, and explicit access-key env vars. BUCKET reads S3_BUCKET_NAME with a transitional R2_BUCKET_NAME fallback.
- Add backend/src/lib/email.ts wrapping @aws-sdk/client-sesv2 with a sendEmail helper that no-ops when SES_FROM_ADDRESS is unset.
- Swap deps in backend/package.json: drop resend, add @aws-sdk/client-sesv2. Refresh package-lock.json.
- No existing Resend call sites in backend/src - helper added for future use.

tsc --noEmit -p backend: 0 errors.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* frontend: swap Supabase auth for Clerk (Stage G)

Replaces every frontend Supabase auth touchpoint with Clerk equivalents. ClerkProvider wraps the app, clerkMiddleware protects routes, and Clerk's hosted SignIn/SignUp components render at /login and /signup via catch-all segments. mikeApi reads the bearer token from window.Clerk.session, and hooks/components that previously called supabase.auth.getSession() now use useAuth().getToken() from @clerk/nextjs. AuthContext is removed; consumers read user/userId from Clerk's useAuth/useUser hooks directly.

Notes:
- @clerk/nextjs 7.3.3 requires react ~19.2.3; project pins 19.2.0, so npm install was run with --legacy-peer-deps. Runtime works correctly.
- tagWIdsOnRenderedDom in DocxView refactored to accept a token argument since it runs outside React hooks.
- tsc --noEmit -p frontend: clean. Lint: 104 problems (-1 vs baseline).

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* frontend: swap OpenNext Cloudflare adapter for AWS (Stage H)

* docs: update README and CLAUDE.md for AWS stack (Stage I)

* docs: warn about gh pr create defaulting to upstream on forks

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* backend+frontend: delete Clerk user on account close; drop --legacy-peer-deps

- DELETE /user/account now removes the Clerk identity before nuking the
  local user_profiles row. Without this the frontend's "Delete account"
  flow only cleared the profile, leaving the Clerk user able to sign
  back in. Exports getClerkClient + new forgetUser helper from the auth
  middleware so the route can reuse the same cached Clerk client and
  evict the just-deleted user from the in-process bootstrap caches.
- Bump react/react-dom to ^19.2.6 so npm install no longer needs
  --legacy-peer-deps for @clerk/nextjs@7.3.3 (which peer-deps ~19.2.3).
  CLAUDE.md and README.md updated to drop the workaround note.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

---------

Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

The original file mixed SST v2 syntax (SSTConfig, stacks(), app.stack)
with v3 constructs (sst.aws.Vpc, sst.aws.Postgres, ...). `npx sst diff`
fails immediately on the top-level imports. Rewrites to the v3
$config({ app(), async run() }) form with the platform shim reference,
and along the way:

- Adds sst.aws.Cluster between Vpc and Service (required in v3)
- Switches loadBalancer.public boolean to loadBalancer.ports listener
  array, forwarding 80/http -> 3001/http
- Uses single `image` instead of `containers` map (single-container
  service)
- Threads DATABASE_URL, S3_BUCKET_NAME and all Clerk/model-provider
  secret values into the Fargate task as env vars matching what the
  backend code reads from process.env
- Wires NEXT_PUBLIC_API_BASE_URL + NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY
  through to the Nextjs build
- Adds bastion: true to the VPC so the Drizzle init migration can be
  run against the proxy from a developer laptop via `sst tunnel`
- Adds a FrontendUrl SST secret to break the api<->web URL cycle for
  CORS (set after first deploy, then redeploy)

HTTPS on the API and a custom domain on the Nextjs site are not wired
yet - flagged inline in the config and tracked as a follow-up.

Also commits the root package-lock.json generated by `npm install` for
the sst CLI.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

- SST app renamed mike -> helix-tribune so resource prefixes match the
  product's new name from day one (no deploys have happened yet, so
  this is free; renaming after a stack exists would force replacement)
- Provider region us-east-1 -> eu-west-2 to match the LevelFive AWS
  account defaults and put UK users closer to the data plane
- backend/src/lib/{storage,email}.ts AWS_REGION fallbacks and README
  example also flipped to eu-west-2 so local dev matches prod

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>