Rails-owned table ownership map

rulebase-api2/src/db/rails-schema.ts declares 166 public.* tables that Rails owns. Every declaration is a persistence dependency that keeps API2 coupled to Rails DDL and, for many tables, to Rails write paths. This page classifies each table and sequences the work to make API2’s persistence independent.

Methodology

For each declared table we measured, on origin/main (2026-09-09):

• API2 files — files importing the table’s rails* const from src/db/rails-schema.ts, plus files with raw SQL touching public.<table>. Note that several api2 files import api2-native tables aliased as railsX from src/db/schema.ts (e.g. dashboards as railsDashboards); those are not public-schema dependencies and were excluded.
• API2 ops — Drizzle insert/update/delete calls and raw INSERT INTO/UPDATE/DELETE against the table.
• Rails refs / Rails write files — files under rulebase-api/app|lib referencing the classified model class, and files containing Model.create/insert_all/upsert/find_or_create write calls.

Static analysis cannot see callbacks, acts_as_tenant associations, after_commit hooks, or writes issued through save/update on fetched records, so Rails write evidence is a floor, not a ceiling. Treat “Rails-coupled” (a live model exists) as dual-written until the write paths are verified — the safe default is to assume both services can write.

Classes

Summary

After this PR the declared surface drops to 156 tables.

Domain ranking: traffic × coupling

Traffic tier reflects request-path reads and ingest-write volume (structural signals: api2 file coupling, write ops, and the GoodJob traffic evidence in goodjob-traffic); each work package must confirm actual production counts (pg_stat_user_tables, GoodJob execution counts) before cutover. Coupling = dual-write or live-Rails-model surface.

Retired in this PR: public.dashboard_* instance tables

Evidence for retirement:

• Rails deleted all custom-dashboard models, controllers, blueprints, and factories in #7095 (2026-05-30). Only the widget-type catalog (dashboard_widget_types, dashboard_widget_type_groups) remains live in Rails routes.
• API2 dashboard code uses only the api2.dashboard_* copies created and backfilled by drizzle migration 0060. The railsDashboard* names in src/routes/dashboards.ts and src/lib/homepage-dashboard.ts alias api2-native tables, not the public ones.
• Zero api2 imports of the public-table consts; zero raw SQL touches public.dashboard_*; all FKs are intra-family (verified live — no FK from outside the set points into it).

Shipped here:

1. rulebase-api migration 20260909225243_deprecate_legacy_dashboard_tables renames the 8 dead instance tables to deprecated_dashboard_* — a forward-only, non-destructive, instantly reversible cut (rename back on rollback). safety_assured is justified in-file: no code references the tables, so nothing live can break; any hidden consumer fails fast instead of reading stale data.
2. All 10 dead rails* declarations removed from src/db/rails-schema.ts (the two catalog consts too — API2 never used them; the catalog stays Rails-owned).
3. scripts/verify-rails-table-retirement.ts (pnpm db:verify-table-retirement(:local|:infisical)) — read-only audit that a table set is dead in code and in the expected physical state; reusable for every package below.

No runtime flag is needed: the change removes no live behavior. The flag equivalent is the staged rollout itself — rename now, observe, drop later. Rollback: db:rollback (or a follow-up rename migration per the forward-only rule). Follow-up: after a ≥2-week bake with no Sentry noise, drop deprecated_dashboard_* in a second migration; re-run the verifier with --expect=absent first.

Known blind spot (pre-merge sign-off): confirm no BI/warehouse or data-sync job reads public.dashboard_* — external consumers are not visible in the repo.

Work packages

Each package is a sequence of PR-sized steps following the existing package convention: API2 takes writes behind a PostHog rollout flag (fail-closed) or Flipper kill switch where safe-state-on applies, with an outbound_delivery_claims-style single-winner contract where Rails still initiates. Generic steps per table family:

1. Verify — production pg_stat + GoodJob counts; run pnpm db:verify-table-retirement / write-path audit on both services.
2. Single writer — cut one write edge at a time behind a flag; keep dual-write or read-repair during the window.
3. Backfill/reconcile — a reconciliation job or script; compare counts.
4. Bake — observe ≥1 week at 100%; then remove the flag and the dead path. Remove the rails* declaration last.

WP-1 — zendesk_webhook_ticket_syncs (smallest true dual-write)

Rails ZendeskWebhook.record_event! inserts on ingest, then ZendeskWebhookTicketSync#handoff_to_api2 hands off synchronously via Api2ZendeskWebhookTicketSyncEnqueuer (no GoodJob since ENG-2506); API2 reads/updates/deletes rows and its recovery sweep re-arms stranded ones. Target: API2 ingest creates and owns the rows; Rails stops inserting.

• PostHog api2_owns_zendesk_webhook_ticket_syncs (owner: platform; review ≤90 days) gating the Rails insert; API2-side creates the row inside its enqueue path.
• Rollback: flag off → Rails resumes creating rows; API2 already tolerates pre-existing rows.

WP-2 — QA result persistence (qa_agent_evaluations, qa_agent_evaluation_scorecard_results, qa_agent_evaluation_scorecard_criterion_results, ..._check_results, ..._check_kb_citations, ..._kb_citations, qa_evaluation_requests, evaluation_scorecard_criterion_result_comments)

API2 is the primary writer (QA agent pipeline); Rails keeps models for reads/serialization. Highest per-row volume after conversations.

• Per family: enumerate Rails write sites (mostly backfill/contest paths), move each behind its own flag (api2_owns_qa_*); Rails models convert to read-only.
• Verify row parity per org before deleting any Rails write path.

WP-3 — Conversation ingest persistence (conversation_parts + detail tables, conversation_threads/thread_items, conversation_field_values, conversation_part_attachments, assignments, conversation_teams, tags, conversation_tags)

Highest-traffic domain; most dual-write edges. Split per part-type (attachments, notes, chat details, email details, call details, assignments) — each is its own flag-gated PR inside the package.

WP-4 — Provider config & catalog (organization_data_sources, data_sources, zendesk_brands, integrations, connections, jira_* catalog)

Mixed admin-write (Rails UI) + sync-write (API2). Requires an explicit write-arbitration decision per table before cutover — flag this to product if sync-time upserts conflict with admin edits.

WP-5 — Identity read graph (organizations, accounts, memberships, roles, permissions, membership_scopes, workspaces, api_keys, invitations, sessions, workos_* events)

Formalize as Rails-owned read-only projections: API2 never writes (the current raw writes are auth/session flows being ported — finish those ports under the native-session packages, then freeze). Do not move the tables.

WP-6 — Long tail (knowledge_base_*, sheets/test_*/cell_*, employees/teams/groups/partners, customers/complaints, clients, disputes, audit_events, SLA tables, credits, digests)

Same recipe per family, ordered by write volume measured in WP-1..4.

WP-7 — Never migrate

flipper_features/flipper_gates (shared kill-switch store, read via raw SQL in src/lib/flipper.ts), pg_search_documents (pg_search gem), active_storage_blobs/attachments (ActiveStorage), data_migrations, schema_migrations, ar_internal_metadata, versions, and dashboard_widget_types/dashboard_widget_type_groups (live Rails catalog).

Appendix: per-table classification

Rails refs/writes are heuristic file counts — a nonzero count means “verify before touching”, zero means “no evidence found”. retired-this-pr rows are renamed to deprecated_* by this PR’s migration.