Upstream sync log

A running record of every upstream commit that affects dev's schema, with the per-decision reasoning. Future syncs append entries here so we can reconstruct why dev's migrations look the way they do.

This is dev's complement to MikeMigrate/notes/upstream-sync/*.md. The notes file records the full per-migration narrative (cherry-pick state, conflicts, application changes); this log is the schema-side index.

Read order: newest entry on top.

---

2026-06-12 — upstream 1fa0554ea519bd164f7c43ae8b5824842b130877

Subject: "Refactor ProjectPageParts and ProjectPageHeader components ..." (PR #178 bundle: soft-deleted document versions, legal-research feature flag, LLM adapter rework, UI refactors)

Schema changes upstream made (schema.sql + oss-migrations/20260610_soft_deleted_document_versions.sql):

• document_versions.storage_path → nullable; new deleted_at / deleted_by columns; partial index document_versions_active_document_id_idx (... where deleted_at is null) — soft-delete tombstones for version files.
• user_profiles.legal_research_us boolean not null default true — Features > Legal Research > Jurisdiction > US toggle (gates CourtListener tools in chat via getLegalResearchUsEnabled).
• (Carried quote_model / mfa_on_login again — both remain skipped per the 44e868e and 3a10943 entries.)

Decision: APPLIED (both deltas).

• 0000_initial.sql updated for fresh databases; new 0009_soft_deleted_versions_and_legal_research_flag.sql for existing databases (adapted from upstream's oss-migration; no Supabase-only content in this one).
• Backend/frontend wiring for legal_research_us adapted into dev's idiom: GET/PATCH /user/profile in dev's route shape, updateLegalResearchUs in dev's UserProfileContext; upstream's PROFILE_SELECT fallback cascade not adopted (dev applies migrations at deploy and selects the column directly, matching the fast_model/0004 precedent).

2026-06-12 — upstream 3a109432000f97b636ddbcd8c2f267461cde030c

Subject: "feat: implement multi-factor authentication (MFA) setup and verification flow"

Schema changes upstream made (schema.sql + oss-migrations/20260606_oss_schema_diff.sql):

• user_profiles.mfa_on_login boolean not null default false — user preference for upstream's Supabase Auth app-level MFA login gate.

Decision: SKIPPED.

• Dev did not adopt app-level Supabase MFA (the feature this column serves). Dev's production auth provider is Entra, where MFA/step-up is enforced by the identity provider (Conditional Access), not application code; Supabase Auth MFA APIs (auth.mfa.*, factor enrolment) have no equivalent on dev's other providers. Inline divergence comments at the rejection sites in 0000_initial.sql, backend/src/middleware/auth.ts, and backend/src/routes/user.ts.
• The rest of the commit (user data export/cleanup libs + routes, safeError log redaction) is schema-neutral and WAS adopted.

2026-06-12 — upstream 44e868eb4235b131dd3f3169042e5436ce792a7a

Subject: "Add courtlistener intergration, liquid glass redesign, UI improvements, version control, various fixes"

Schema changes upstream made (schema.sql + oss-migrations/20260606_oss_schema_diff.sql):

• user_profiles.title_model, user_profiles.quote_model — new model preference columns.
• user_api_keys provider check widened with openrouter, courtlistener.
• Document metadata moved: documents.filename/file_type/size_bytes/ page_count/structure_tree dropped; document_versions gains filename/file_type/size_bytes/page_count, loses display_name.
• New courtlistener_citation_index / courtlistener_opinion_cluster_index tables + indexes; Supabase anon/authenticated revokes for them.

Dev decisions:

• title_model / quote_model: skipped. Dev's fast_model (0004) covers the lightweight-task preference; nothing reads quote_model. Inline comment at the rejection site in 0000_initial.sql.
• Provider check: applied + extended. 0008 swaps the constraint to dev's full set (upstream's five + azure_openai); 0006's CREATE updated for fresh installs.
• Document metadata move: applied with deferred drops. Fresh schema (0000) follows upstream; 0008 adds the document_versions columns and backfills from display_name + the parent documents row (adapted from upstream's oss-migrations file), and relaxes documents.filename NOT NULL. The actual column DROPs (documents legacy metadata columns, document_versions.display_name) are DEFERRED to a follow-up migration per the §4.3 rule — run 0008 everywhere and verify before landing the drop.
• CourtListener tables: applied (0000 for fresh, 0008 for existing); the anon/authenticated revokes were NOT ported (no Supabase roles on dev — see 0005_postgres_roles.sql).
• Upstream's backend/oss-migrations/20260606_oss_schema_diff.sql lands in dev's tree verbatim via the cherry-pick as upstream documentation; dev's migration runner only executes backend/migrations/000X_*.sql.

---

2026-06-12 — upstream 4f3384334a01fa0e759a964e7569e7b9ff207f01

Subject: "Update document UI, tabular reviews, and storage caching"

Schema changes upstream made (in upstream's backend/schema.sql):

• tabular_reviews.document_ids jsonb — explicit per-review document membership (replaces inferring membership from cells / project docs).

Dev decisions:

• Applied. Git's rename detection auto-merged the column into 0000_initial.sql (fresh installs); 0007_tabular_review_document_ids.sql adds it idempotently for existing databases. Backend usage in routes/tabular.ts auto-merged cleanly and falls back to the legacy cell-derived membership when the column is NULL.

---

2026-05-13 — upstream f39f175273e398ffa4781b7c4a604bec6de3126e

Subject: "Sync deployment and project page fixes"

Schema changes upstream made (in upstream's backend/schema.sql):

Removal only — upstream deleted all RLS policies, the current_user_id_text() / current_user_email() / email_is_shared() / project_is_accessible() helper functions, and the enable row level security statements (~708 lines), replacing them with a comment that authorization is now enforced by the backend verifying the user's JWT.

Decisions for dev:

Item	Decision	Rationale
RLS removal	NOT APPLICABLE (nothing to do)	Dev's numbered migrations never contained RLS, and dev's authorization has been app-layer (backend/src/lib/access.ts) since the fork. Upstream has converged on the model dev already uses. No numbered migration is needed; there is no applicable schema delta.

(Sync branch: upstream-sync/mikeOssOrig-f39f175; full narrative in MikeMigrate/notes/upstream-sync/mikeOssOrig-f39f175.md.)

2026-05-08 — upstream ba6f7711449de47bd25cad7ac21bdc2a53355963

Subject: "Sync security and backend profile updates"

Schema changes upstream made (in upstream's backend/schema.sql):

1. Removed two columns from user_profiles:
   • claude_api_key text
   • gemini_api_key text
2. Added new table user_api_keys with AES-GCM at-rest encryption (encrypted_key, iv, auth_tag), provider check (claude | gemini), unique(user_id, provider).
3. ~600 lines of Supabase RLS policies and auth.uid()/auth.jwt() helper functions covering most public tables.

Decisions for dev:

Item	Decision	Rationale
Upstream's backend/schema.sql file	REJECTED	Dev uses numbered migrations as the source of truth (see MIGRATION_KNOWLEDGE.md §2.2). Never import upstream's schema.sql — it would drift from the numbered migrations.
New user_api_keys table	APPLIED (with adaptation) — see 0006_user_api_keys.sql.	Encryption-at-rest for provider keys is a clear security win. Dev's adaptation: no auth.users FK, no RLS (access control in app layer), provider check extended to dev's 4-provider set (`claude
Removal of claude_api_key, gemini_api_key from user_profiles	DEFERRED to a follow-up migration after the one-shot data move	Dev has user data in these columns (and also openai_api_key and the four azure_openai_* columns added in 0003). They get emptied by backend/scripts/migrate-user-api-keys.ts (run after this migration), and dropped by a 0007_drop_legacy_provider_keys.sql only after the script has been verified.
RLS policies + auth.uid()/auth.jwt() helpers	NOT APPLICABLE	Azure Postgres has no auth.uid(). Equivalent intent (per-user/per-share access scoping) is enforced in backend/src/lib/access.ts at the application layer. Existing dev migrations (0000_initial.sql's TODO(entraid) headers, 0005_postgres_roles.sql) already document this divergence.

Application changes that accompany the schema work (separate commits on the migration branch upstream-sync/mikeOssOrig-ba6f771):

• backend/src/lib/userApiKeys.ts — encryption library (Key Vault primary, env-var fallback for local dev only; AES-256-GCM; supports all 4 providers; serialises Azure OpenAI's compound shape).
• backend/scripts/migrate-user-api-keys.ts — one-shot script that reads every user's plaintext provider keys, encrypts them, inserts rows into user_api_keys, and UPDATEs the legacy columns to NULL. Idempotent — re-running sees no rows to migrate. Wired as npm run migrate:user-api-keys.
• Refactor of backend/src/lib/userSettings.ts, backend/src/routes/user.ts, backend/src/routes/diagnostics.ts to read provider keys via userApiKeys.getApiKey(...) instead of selecting plaintext columns. routes/user.ts no longer returns plaintext keys to the client — it returns {configured: bool} per provider (security improvement beyond upstream's scope).
• helmet + per-route rate limiters in backend/src/index.ts (taken from upstream's same commit — applied in a separate logical commit on this branch).

Process to drop the legacy plaintext columns (separate, later commit):

1. Ensure 0006_user_api_keys.sql has been applied in every environment.
2. Run npm run migrate:user-api-keys in each environment until it reports zero rows remaining.
3. Land 0007_drop_legacy_provider_keys.sql:
   • alter table public.user_profiles drop column if exists claude_api_key, drop column if exists gemini_api_key, drop column if exists openai_api_key, drop column if exists azure_openai_endpoint, drop column if exists azure_openai_api_key, drop column if exists azure_openai_api_version, drop column if exists azure_openai_deployment;
4. Audit git grep for any remaining references to those column names before merging step 3.