cpatpa rips out the rented database and rebuilds the plumbing in-house

This is the additive first step of the Phase 2 cutover from Supabase
to plain Postgres + Auth.js. No existing behaviour changes; the
existing Supabase paths still work. The new tables and infrastructure
land alongside the upstream schema and become the target state that
later sub-phases migrate onto.

Added:
- backend/src/lib/db.ts: a single pg Pool per process, with query,
  transaction, ping, and closePool helpers. Reads DATABASE_URL or the
  discrete PG* vars. SSL controlled by DATABASE_SSL.
- backend/src/lib/migrate.ts: tiny SQL-file applier with a
  pip_migrations tracking table. Each file runs inside a transaction.
  No down-migrations: recovery from destructive change is restore from
  backup. CLI entry runs via `npm run migrate --prefix backend` and
  will run on container boot from Phase 7.
- backend/migrations/0001_extensions.sql: pgcrypto and citext.
- backend/migrations/0002_users.sql: public.users with onboarding
  fields (jurisdictions, practice_areas, sectors, office_location,
  timezone), role and status, custom_instructions,
  ai_disclaimer_at, and a touch_updated_at trigger.
- backend/migrations/0003_org_settings.sql: single-row organisation
  configuration. Seeded with the canonical PA system prompt
  (AU English, AGLC4, hallucination guards, AI reminder), the AU
  jurisdictions, the PA practice areas and sectors taken from
  piperalderman.com.au, a 7-year default retention, the
  piperalderman.com.au email allowlist, and the external-AI banner
  text.
- backend/migrations/0004_audit_events.sql: append-only audit trail
  referencing public.users with ON DELETE SET NULL so audit history
  survives user deletion.
- Dependencies: pg, @types/pg, bcryptjs, @types/bcryptjs.
- docs/developer/03-database-schema.md and 08-migrations.md.
- backend/.env.example documents the new DATABASE_URL,
  DOCX_*, and JSON_*_LIMIT variables.

The Auth.js frontend cutover, backend middleware swap, and the
user_id text -> uuid FK migration (H1) are the next sub-phases.

Migrations 0005-0011 create the remaining PIP application tables.
Every user_id is now uuid REFERENCES public.users(id) ON DELETE
CASCADE, closing audit finding H1. The previous user_id text columns
that had no foreign key allowed orphaned data after user deletion.

Added migrations:

  0005_user_profiles_and_api_keys.sql  user_profiles, user_api_keys
  0006_projects.sql                    projects, project_subfolders
  0007_documents.sql                   documents, document_versions,
                                       document_edits (chat_message_id
                                       FK added later in 0008)
  0008_chats.sql                       chats, chat_messages, document
                                       _edits.chat_message_id FK
  0009_workflows.sql                   workflows, hidden_workflows,
                                       workflow_shares
  0010_tabular_reviews.sql             tabular_reviews, tabular_cells,
                                       tabular_review_chats,
                                       tabular_review_chat_messages
  0011_rls.sql                         ENABLE + FORCE ROW LEVEL SECURITY
                                       on every backend-owned table;
                                       revokes any inherited grants to
                                       anon/authenticated

L5 closed by 0011_rls.sql. No row-level policies are defined; the
application connects via a BYPASSRLS role and is itself the access-
control boundary. RLS sits in place as defence-in-depth so any future
direct grant fails closed. The role provisioning commands are
documented in the migration file and in docs/developer/08-migrations.md.

L1 closed in backend/src/lib/userApiKeys.ts. USER_API_KEYS_ENCRYPTION
_SECRET is now required (minimum 32 characters); the previous fallback
to SUPABASE_SECRET_KEY is removed. Rotating the Supabase key no longer
renders stored model provider API keys undecipherable.

backend/schema.sql is marked deprecated. PIP defines its schema only
through the incremental migration files. The Supabase-flavoured file
is retained as a historical reference only.

End-to-end verification against a fresh Postgres 16 database:
- All 11 migrations apply cleanly in order.
- 20 tables created (19 application tables + pip_migrations).
- RLS enabled on every backend-owned table; pip_migrations excluded.
- org_settings seeded with 10 jurisdictions, 17 PA services, 16 PA
  sectors, 7-year retention, piperalderman.com.au allowlist.
- /health and /ready return ok with db: true.
- POST /auth/register grants admin role to BOOTSTRAP_ADMIN_EMAIL on
  first registration.
- POST /auth/register rejects disallowed domains and short passwords
  with clear messages.
- POST /auth/verify-credentials returns the same 401 for wrong
  password and unknown email (no user enumeration).
- users_ensure_profile trigger auto-creates the user_profiles row.
- audit_events table records user.register and user.login.credentials.

Begins the route-migration sub-phase by converting the four lib
helpers that every route depends on. Helper signatures keep their
db parameter as optional and ignored so callers compile unchanged;
they get cleaned up once each calling route migrates.

  src/lib/access.ts          checkProjectAccess, ensureDocAccess,
                             ensureReviewAccess,
                             filterAccessibleDocumentIds,
                             listAccessibleProjectIds. Switched to
                             pg queries. listAccessibleProjectIds
                             now uses the @> JSONB containment
                             operator on shared_with (the PostgREST
                             cs equivalent), still parameterised.

  src/lib/userApiKeys.ts     getUserApiKeyStatus, getUserApiKeys,
                             saveUserApiKey. Insert/upsert/delete
                             now via parameterised SQL with ON
                             CONFLICT (user_id, provider).

  src/lib/userSettings.ts    getUserModelSettings, getUserApiKeys.
                             tabular_model preference now read with
                             a single parameterised SELECT against
                             user_profiles.

  src/lib/documentVersions.ts loadActiveVersion,
                             attachActiveVersionPaths,
                             attachLatestVersionNumbers. The
                             attach* helpers use ANY($1::uuid[]) for
                             batched lookups.

End-to-end verified against the Phase 2 schema in Postgres 16:
  - saveUserApiKey upsert and delete behave correctly;
    AES-256-GCM round-trip is intact (sk-test-abc-123-xyz saved
    encrypted and decrypted back identically).
  - getUserApiKeyStatus surfaces 'user' / null sources correctly.
  - listAccessibleProjectIds returns empty for a user with no
    projects.
  - checkProjectAccess returns {ok:false} for a non-existent
    project id.
  - Backend still boots, /ready returns ok, /auth/verify-credentials
    signs in an existing user with the previously-reset password.

Routes still use the Supabase JS client for their own queries and
will migrate one or two at a time in the next commits.

Three routes converted from the Supabase JS client to parameterised
pg queries.

backend/src/routes/downloads.ts
  - 70-line file, two db.from calls. Migrated.

backend/src/routes/user.ts
  - /user/profile (GET/POST/PATCH), /user/api-keys (GET/PUT),
    /user/account (DELETE).
  - ensureProfileRow uses INSERT ... ON CONFLICT DO NOTHING.
  - loadProfile parameterised, monthly-credit reset still applied
    inline.
  - PATCH builds the UPDATE column list dynamically from the
    supplied fields.
  - DELETE /user/account now removes the public.users row directly.
    Schema-level ON DELETE CASCADE on every owned table tears down
    the data; audit_events.user_id is ON DELETE SET NULL so history
    is preserved. Storage-object cleanup and re-auth confirmation
    land in the follow-up M1 commit.

backend/src/routes/workflows.ts
  - Largest of the three (430 lines, 15 db.from calls).
  - Sharer-name lookup no longer uses Supabase Auth admin; a single
    SELECT id, display_name, email FROM users WHERE id = ANY($1)
    returns names with fallback to email.
  - workflow_shares upsert built as a multi-row VALUES with ON
    CONFLICT (workflow_id, shared_with_email) DO UPDATE.

End-to-end verification against Postgres 16:
  - /user/profile GET auto-creates row, PATCH applies display name
    and organisation.
  - /user/api-keys GET reports state, PUT openai encrypts and
    stores, GET shows source=user.
  - /workflows POST creates, GET lists (count=1), PATCH renames,
    POST /share with two emails persists two share rows, GET
    /shares lists both, POST /hidden inserts, GET /hidden returns
    the id, DELETE /workflows/<id> returns 204.

Type-check clean. Backend boots and serves all migrated routes.

projects.ts, documents.ts, chat.ts, projectChat.ts, tabular.ts, and
chatTools.ts are next.

Largest single route conversion so far. 881 lines, 29 db.from calls,
all now parameterised SQL via lib/db.

Highlights:
- GET / lists own + shared via shared_with @> $::jsonb (PostgREST cs
  equivalent), then per-project count queries for documents, chats,
  tabular_reviews.
- POST / inserts and returns the new row with documents:[] attached.
- GET /:id loads project + documents + folders, attaches active
  version paths via the already-migrated documentVersions helpers.
- GET /:id/people replaces db.auth.admin.listUsers({ perPage: 1000 })
  with a single SELECT id, email, display_name FROM users WHERE
  lower(email) = ANY($1). Owner is resolved by id. Members are
  resolved by email; missing accounts surface as
  display_name: null in the response.
- PATCH /:id builds the UPDATE column list dynamically from supplied
  fields, with shared_with normalised to lowercase + deduped, capped
  at 100 entries.
- DELETE /:id relies on schema-level ON DELETE CASCADE (Phase 2
  migrations 0007-0010) to tear down documents, chats, reviews,
  folders.
- /:id/folders POST + PATCH + DELETE. Cycle detection on parent
  reassignment walks up the proposed ancestor chain; folder delete
  moves direct documents to root in a transaction before removing
  the folder.
- handleDocumentUpload (shared with documents.ts) migrated. Its db
  parameter is retained for the still-Supabase documents.ts call
  site and ignored.

End-to-end verification against Postgres 16:
  - POST creates project, returns 201 with documents:[].
  - GET / returns count=1 with doc_count/chat_count/review_count=0
    and is_owner=true.
  - GET /:id returns the full project payload with documents and
    folders arrays.
  - GET /:id/people returns owner (with display_name) and members
    (display_name null for not-yet-registered emails).
  - PATCH rename applies.
  - Folder create, subfolder under parent, cycle detection on
    PATCH (400 with 'Cannot move a folder into itself or a
    descendant'), folder delete 204.
  - Project delete 204; GET / returns count=0.

Remaining: documents.ts (16), chat.ts (12), projectChat.ts (4),
tabular.ts (19), chatTools.ts (12). Then @supabase/supabase-js comes
out of the backend.

1046-line file converted from the Supabase JS client to parameterised
pg queries. 16 db.from calls migrated.

Key changes:
- handleDocumentUpload duplicate removed. Both routes (single-docs
  POST and projects POST) now import the shared implementation from
  projects.ts. This also drops ~140 lines of duplicate
  countPdfPages/extractStructureTree code.
- Edit-resolution flow (accept / reject tracked changes):
  - The pending-edits count uses `SELECT count(*)::text` instead of
    the Supabase PostgREST head-count pattern.
  - status / resolved_at updates use parameterised UPDATE.
  - All idempotent-already-resolved and not-found-in-docx branches
    still emit the same response shapes for backward compatibility.
- DELETE /single-documents/:id keeps the storage-fanout cleanup
  (download every version row's storage_path + pdf_storage_path,
  delete the underlying object) before removing the documents row.
- POST /:id/versions:
  - Computes the next version_number with `ORDER BY version_number
    DESC NULLS LAST LIMIT 1`.
  - Conditionally rewrites documents.filename to honour a
    user-supplied display_name, preserving the original or uploaded
    extension.

End-to-end verification against Postgres 16:
  - GET /single-documents lists own docs with storage_path attached.
  - GET /single-documents/:id/versions returns current_version_id
    plus the versions list.
  - PATCH version display_name applies.
  - DELETE returns 204 and removes the row + cascades.
  - download-zip rejects an empty array with 400.
  - Existing /projects and /workflows routes still pass regression.

Remaining on Supabase JS: chat.ts (12), projectChat.ts (4),
tabular.ts (19), chatTools.ts (12).

Closes the chat and assistant-tools surface. ~20 db.from sites across
chatTools.ts (3285 lines) converted to parameterised SQL, plus full
rewrites of routes/chat.ts and routes/projectChat.ts.

Key changes:
- routes/chat.ts:
  - GET /chat replaces the .or() PostgREST filter that
    string-concatenated project ids with
    'user_id = $1 OR project_id = ANY($2::uuid[])', still
    parameter-safe.
  - chat_messages inserts now JSON.stringify content/annotations so
    they reach Postgres as proper JSONB rather than relying on the
    Supabase client's serialisation.
- routes/projectChat.ts:
  - Full rewrite. Same flow, all queries through pg.
- lib/chatTools.ts:
  - enrichWithPriorEvents, generateDocx, loadCurrentVersionBytes,
    runEditDocument (with multi-row bulk INSERT for document_edits
    using positional placeholders), the replicate_document inline
    block (bulk INSERTs for documents and document_versions with
    multi-row VALUES + per-row UPDATE for current_version_id),
    buildDocContext, buildProjectDocContext, and buildWorkflowStore
    all converted.
  - Public functions that previously required a Supabase client
    keep their db parameter as optional and ignored, so the
    still-Supabase tabular.ts keeps compiling unchanged until its
    own migration.

End-to-end verification against Postgres 16:
- /ready ok with db: true.
- GET /chat initially empty.
- POST /chat/create returns a uuid.
- GET /chat/:id returns chat + empty messages array.
- PATCH rename applies.
- GET /chat lists 1 with the new title.
- DELETE returns 204.
- GET /chat returns empty.
- Regression: /projects, /workflows, /single-documents still pass.

Remaining on Supabase JS: tabular.ts (19). After that, drop
@supabase/supabase-js from the backend entirely.

Closes the final backend route on Supabase JS. 1733-line tabular.ts
with 49 PostgREST query sites converted to parameterised SQL.

Highlights:
- GET / collapses three Supabase queries (own + shared-by-project +
  shared-by-email) into the same pattern used elsewhere
  (shared_with @> $::jsonb). Per-review cell count now uses a single
  SELECT COUNT(DISTINCT document_id) GROUP BY review_id instead of
  N round trips.
- POST / and PATCH /:reviewId build the bulk tabular_cells insert
  with multi-row VALUES positional placeholders, same shape as the
  edits insert in chatTools.
- /:reviewId/people drops db.auth.admin.listUsers({ perPage: 1000 })
  in favour of a single SELECT id, lower(email::text), display_name
  FROM users WHERE lower(email::text) = ANY($1::text[]). No more
  Supabase Auth admin dependency anywhere in the backend.
- Streaming /:reviewId/generate writes all status transitions and
  cell content through parameterised UPDATE. The cell-update SSE
  events are unchanged.

Dependency removal:
- backend/src/lib/supabase.ts deleted.
- Every ReturnType<typeof createServerSupabase> type alias collapsed
  to `unknown`. Affected files: lib/access.ts, lib/userApiKeys.ts,
  lib/userSettings.ts, lib/documentVersions.ts, lib/chatTools.ts,
  routes/projects.ts. The aliases were transitional only.
- @supabase/supabase-js uninstalled from backend/package.json.

End-to-end verification against Postgres 16:
- tabular: POST create, GET list, GET detail, GET people (owner
  display_name + member display_name), PATCH rename, GET chats,
  DELETE returning 204.
- Full regression sweep: /chat, /projects, /workflows,
  /single-documents, /user/profile all still return the expected
  shapes and 2xx codes.

The backend no longer depends on the Supabase JS client at all.
Auth is Auth.js (HS256 JWT verification via jose); data access is
plain pg with parameterised SQL.

Remaining for Phase 2:
- M1 (account-deletion teardown of storage objects + re-auth
  confirmation).
- Frontend Supabase dependencies (frontend already migrated, but
  may have stray imports to audit).

DELETE /user/account is now properly destructive and properly gated.

Re-authentication:
- Credentials users (password_hash present) must supply `password`
  in the JSON body. Missing returns 401 with code password_required;
  wrong returns 401 with detail "Incorrect password" (timing-safe
  via bcrypt.compare).
- Entra-only users (no password_hash) must supply `confirm_text`
  matching their lowercase email. Missing or wrong returns 401 with
  code email_confirmation_required and the expected email in the
  detail so the UI can echo it back. Defeats casual misuse of a
  still-valid session via browser extension, stolen Bearer, or
  shared device.

Storage teardown:
- Before deleting the row, enumerate every storage_path and
  pdf_storage_path across the user's document_versions (joined to
  documents). Set of unique paths is best-effort deleted after the
  cascade commits. Failures are logged but do not block the user
  delete; orphaned storage objects are easier to detect later than
  missing data is to recover.

Audit:
- audit_events row written inside the same transaction as
  DELETE FROM users so the actor identity survives the cascade
  (audit_events.user_id is ON DELETE SET NULL).
- Metadata captures storage_objects count, had_password,
  had_entra so an admin scrolling the log can see the deletion's
  scope without joining to the now-missing user row.

Frontend:
- Account > Delete Account panel grew password + email-confirm
  inputs. Validates locally that at least one is non-empty before
  enabling the destructive button. On 401 the server's detail is
  surfaced inline (with the expected email for Entra-only users).
- mikeApi.deleteAccount now takes an options object
  { password?, confirmText? }.

End-to-end verified against Postgres 16:
- Credentials: missing pw 401, wrong pw 401, correct pw 204 with
  full cascade (project/document/version all removed) and audit
  row with metadata { storage_objects: 2, had_password: true,
  had_entra: false }.
- Entra-only: missing confirm 401, wrong email 401, correct email
  204. Two audit rows recorded across both deletions.

Closes audit finding M1.