RFC: Schema integrity and cleanup
- Status: Draft
- Owner: Jeff (jeffg)
- Created: 2026-10-05
Summary
The core schema stores many facts in two or three places. No constraint keeps the copies in agreement. The application code carries that burden, and the copies drift.
Production data confirms the drift:
- 712 data-field values belong to a different vendor than their field.
- 310 task steps belong to a different vendor than their task.
- 373 projects disagree with themselves about whether they are archived.
- 463,560 data-field values (49%) are empty strings that mean "no value".
This RFC proposes eight workstreams. Each workstream ships as small, independent PRs. Workstream 1 adds guardrails that stop new drift. Workstreams 2 to 8 fix the defects with the highest cost, in order of payoff.
Most of the new constraints are cheap. Production holds no violating row for four of the ten invariants in this RFC. Three more invariants govern delete behavior, so no stored row can violate them. The remaining three need a bounded cleanup first.
The RFC does not propose a redesign. The large structural changes (Lanes retirement, a new status model, typed data-field columns) are listed as separate decisions in the "Deferred decisions" section.
Benefits
| Benefit | What changes for the team or the customer | Delivered by |
|---|---|---|
| Tenant isolation enforced by the database | The database rejects a child row whose vendor differs from its parent's vendor. Production holds 1,022 such rows today in two tables. The RLS RFC becomes deployable without breaking writes. | W3 |
| Fewer customer-facing bugs | The two task-visibility flags stop being confused. Three portal bugs (#10773, #12704, #12735) came from that confusion. The flags disagree on 276,307 tasks, so the confusion has real consequences. The dashboard Activities tile shows real data again. It has counted a table that stopped receiving writes on 2026-01-22. | W2 |
| Tests describe production | Tests run against the schema that migrations produce, including 59 RLS policies, 35 unique constraints and 27 partial indexes that the test database lacks today. A model that disagrees with the database fails CI. | W1 |
| One answer per lifecycle question | Each fact (archived, completed, published) has one authoritative column. Today 373 projects disagree between their status and their archive timestamp. | W4 |
| Less defensive code | One write boundary for data-field values replaces scattered defensive parsing. "No value" stops having four representations. One predicate replaces at least four copies of the portal-visibility rule. | W2, W6 |
| No silent data loss on delete | Deleting a user no longer deletes the projects and playbooks that user created or owns. Deleting a lookup row fails instead of deleting customer data. Vendor offboarding becomes one statement. | W5 |
| Smaller schema to understand | Dead tables, dead columns and prod-only backup tables disappear. That includes 1.0 GB of frozen activity tables and 11 backup tables that hold copies of customer rows outside any model. | W7 |
| Lower write cost | 35 duplicate indexes go away. Each removed index removes work from every insert and update on its table. | W8 |
| Future flexibility | Typed data fields, a single status model and RLS all depend on the constraints in this RFC. Those later projects become smaller and safer. | W3, W4, W6 |
Evidence and scope
The evidence comes from four sources:
- The local schema at the head of
main, including a throwaway database built from migrations and a throwaway database built withcreate_all. - A grep of this repository,
crm-integrationsandcustomer-api. - The production read replica on 2026-10-06: catalog statistics and aggregate counts only. No row content left the database. Appendix A lists the measurements.
- Git history and linked issues.
Two limits apply to the production numbers:
- The replica is a hot standby. A hot standby records no write statistics. Hard deletes are therefore estimated from gaps in
idsequences, which are an upper bound. - Index scan counts cover queries on the replica only. An index with zero scans on the replica can still serve the primary.
Statements marked "(inferred)" are not directly verified. All other statements are observed.
Invariants
The schema enforces each rule after this RFC lands. The last column counts the production rows that violate the rule today.
| # | Rule | Mechanism | Violating rows in production |
|---|---|---|---|
| 1 | Every row in or_tasks, or_modules, or_task_steps and or_data_field_values has a tenant. | NOT NULL on the tenant column | 235 (or_modules only) |
| 2 | A task step has the same vendor as its task. A data-field value has the same vendor as its field. | Composite foreign key on (parent_id, vendor) | 310 steps, 712 data-field values |
| 3 | Deleting a vendor deletes all of that vendor's data. | ON DELETE CASCADE on every foreign key to or_vendors | Not applicable |
| 4 | Deleting a user never deletes another row. | ON DELETE SET NULL on every actor column | Not applicable |
| 5 | A lookup row that is in use cannot be deleted. | ON DELETE RESTRICT on every foreign key to a lookup table | Not applicable |
| 6 | A playbook series has at most one live published version. | Partial unique index | 0 |
| 7 | A playbook version is never both draft and published. | CHECK constraint | 0 |
| 8 | A task belongs to a project or to a playbook, never to both. | CHECK (num_nonnulls(project_id, playbook_id) <= 1) | 0 |
| 9 | A task has at most one task mapping. | Unique constraint on or_task_mappings.or_task | 0 |
| 10 | The ORM models and the migrated schema agree. | CI gate (W1) | 389 differences |
A data-field value that matches the type of its field is an eleventh rule. The application enforces that rule in W6. The database cannot enforce it without typed columns, which are a deferred decision.
Current state
Problem 1: nothing keeps models, migrations and tests in agreement
Tests build their database with
ORBaseModel.metadata.create_all(app/api/conftest.py:195). Migrations never run in the test suite.The test schema differs from the migrated schema:
Object create_allMigrated RLS policies 0 59 Unique constraints 99 134 Partial indexes 65 92 idcolumns without a default14 5 At least 12 test files contain workarounds for missing
iddefaults.Alembic's
compare_metadatareports 389 differences between the models and the migrated schema. Examples:- 68 indexes declared on models do not exist in the database.
- The model declares
or_data_field_values.field_idasNOT NULL. The database allowsNULL. - 19 column types differ.
app/api/base/schema_drift.pydetects only missing tables and missing columns.Production holds 22 tables that no migration creates. 11 of them are one-off backup tables from past data fixes.
Production holds 11 constraints marked
NOT VALIDthat nobody validated.
Problem 2: tenant keys have gaps and disagreements
or_tasks.vendor,or_modules.vendorandor_task_steps.vendorallowNULL. In production, 235 modules have no vendor. No task and no step lacks a vendor.or_data_field_valueshas no tenant column. Itsfield_idallowsNULL, but no production row usesNULL.Tenant disagreements in production:
Relationship Rows whose vendor differs from the parent's vendor Task step to task 310 Project data-field value to its field 712 Task to the status row it references 64 Task to project 2 Whether any of these rows reached another vendor's user is unknown. A likely source is copying templates between vendors without remapping every reference (inferred).
212 project data-field values point at a project that no longer exists.
The newer domains (SMS, journeys, CRM field sync) use 50 composite foreign keys of the form
(parent_id, vendor_id). The core tables use none.The RLS RFC (
rls-tenant-isolation.md) depends on these columns. A restrictive policy hides rows with aNULLtenant and rejects inserts without one.The RLS RFC's template C references
or_task_steps.or_task_id. The real column istask_id.
Problem 3: one fact, several columns
- Task visibility and task kind use nearly identical names:
or_tasks.task_is_internalmeans "hidden from the portal".or_tasks.is_internalmeans "internal task" as opposed to "customer task".- The two flags disagree on 276,307 of 2,979,793 tasks (9.3%). Both flags carry meaning. Neither flag is redundant.
- 11 files filter only on the first flag. 8 files filter only on the second. 10 files filter on both.
- Issues #10773, #12704 and #12735 trace to this confusion.
- Project archive state lives in three places:
- The status code
PROJECT_ARCHIVE(24 files). project_archived_dts(41 files).archived_at(0 references, and 0 production rows set).- In production, 313 projects have the archived status without
project_archived_dts. 60 projects haveproject_archived_dtswithout the archived status.
- The status code
- Playbook archive state lives in three places:
is_archived,archived_dateandarchived_at. In production, all 3,880 archived playbooks use onlyis_archived.archived_dateandarchived_atare never set. - 13 tables carry both a boolean flag and a timestamp for the same state.
- Playbook version state uses two booleans,
is_draftandis_published. No constraint enforces one published version per series. The rule lives in a docstring and a row lock. 23 call sites build their own copy of that filter. Production has no violation today. - A task's parent is stored twice: in
or_tasks.project_idandor_tasks.playbook_id, and in theor_task_mappingstoor_module_mappingschain. 47 files use the chain. 80 files use the columns. 15 files use both. Production has no task with both columns set and no task with two mappings. or_projects.healthandor_projects.engagementduplicateproject_healthandproject_engagement. The application writes both pairs.- In a 20% production sample, every legacy value is a JSON string that contains encoded JSON.
- In the same sample, no legacy value equals its counterpart in the new pair.
- The project export (
export_projects_service.py:175-197) reads the legacy pair. The export can therefore show a different health than the application (inferred).
or_tasks.task_statusdefaults to1. Noor_object_statusrow hasid1 in production. An insert that relies on the default fails.
Problem 4: foreign keys delete data that should survive
- 80 foreign keys to
useruseON DELETE CASCADE. About 45 of them are actor columns. Examples confirmed in production:or_projects.created_by,project_owner_user,project_completed_byandproject_archived_by.or_tasks.completed_by.user.created_by_user_id, which points atuseritself. Deleting a user deletes every user that user created.
- 26 foreign keys to lookup tables use
ON DELETE CASCADE. Examples confirmed in production:or_projects.project_statusandor_tasks.task_statusreferenceor_object_status.or_task_steps.step_typereferencestask_types.
- The
downgrade()of migration2025_12_10_5d3f32d30eacdeletes atask_typesrow. Running that downgrade in production would delete every List-form step of every customer. - Today the application hard-deletes
userrows in two places:- Organization deletion (
app/api/onramp_admin/organizations/helpers.py:309). - Removal of an invited customer who never logged in (
invite_customer_controller.py:73).
- Organization deletion (
- Neither path is likely to delete a project that another user depends on (inferred). The risk is latent. Any new hard delete, support script or manual fix triggers the cascade.
- 26 foreign keys to
or_vendorsdo not cascade. The organization-deletion helper therefore deletes rows by hand, and it depends on the actor cascades to remove the rest. For example, comments disappear only through the cascade onor_comment.created_by_user_id.
Problem 5: data-field values are untyped text
or_data_field_values.valueis text for every field type. No write path checks the value against the field type.Production holds 949,524 values. "No value" has four representations:
Representation Rows No row Not countable NULL2,576 Empty string 463,560 The string None432 644 non-empty values do not match their field type: 574 dates, 49 numbers and 21 users. That count is small. One such value is enough to fail an endpoint that casts it.
Dropdown and multi-select values store the option label, not the option key. Renaming an option rewrites every value row under a lock (
data_fields_services.py:1873).Readers compensate with a 179-line SQL resolver (
data_field_value_sql.py) and a 329-line defensive reader (mappers.py).Seven fix PRs between June and September 2026 address malformed values: #9680, #9683, #10158, #10381, #10637, #11686, #13600.
Problem 6: legacy generations stay alive next to their replacements
or_project_activity,or_playbook_activityandor_task_activitystopped receiving writes on 2026-01-22, 2026-02-18 and 2024-01-29. Together they hold 1.47 million rows and about 1.0 GB. The activity feed still merges them throughlegacy_providers.py(710 lines).- The dashboard Activities tile (
app/api/dashboard/services.py:107-128) counts onlyor_project_activityfor the last week. That table has received no row since 2026-01-22, so the tile has shown zero since then (inferred from the query window). or_lane_activityholds 4.7 million rows and 9.2 GB. It grows by about 205,000 rows every 30 days. No code in this repository reads it. The replica shows 2,222 scans of it, so a reader outside this repository may exist.- Production holds 11 backup tables from past data fixes, about 24 MB in total. Two of them (
backup_onramp_6537_user,backup_onramp_6537_account) hold copies of user and account rows outside any model. - Lanes and Ramps, v1 and v2 integrations, and three generations of saved filters are all live. These pairs are product decisions and are out of scope (see "Deferred decisions").
Problem 7: type and index hygiene
- 35 indexes duplicate another index exactly, for 59 MB in total. Most are a plain index on
uuidnext to a unique constraint onuuid. 17 migrations create both. - 416 non-unique indexes (2.75 GB) show zero scans on the replica. Primary statistics are needed before any of them can be called unused. The largest are trigram indexes on
or_tasks(1.15 GB). - 88 columns use
json. 40 usejsonb. Fivejsoncolumns are cast tojsonbat query time:or_projects.project_health,or_projects.project_engagement,or_ramp_workflows.trigger, the ruleactionscolumns, andor_activity_history.data. - 148 timestamp columns in 74 tables have no time zone. 19 tables mix both kinds. The code carries about 10 helpers that convert between the two.
user.user_uuidandaccount.account_uuidhave no unique constraint. Production holds no duplicates in either column.account.account_uuidis the identifier in URLs and has no index.user.email_addresshas no unique constraint at any scope. Production holds 843 groups of duplicate addresses within one vendor. 762 of those groups pair one internal user with one customer user.- 50 status or type text columns have no
CHECKconstraint.
Proposal
Each workstream lists its changes, its benefit, its cost and its reversibility. Each numbered change is one PR unless stated otherwise. Migrations follow docs/runbooks/migrations-expand-and-contract.md and the lock_safe_ddl helpers.
W1: guardrails
Changes:
- Build the test template database from
devtools/db/schema-baseline.sqlplusflask db upgrade heads, instead ofcreate_all. Remove theid-default workarounds from the test files. - Add a CI job that runs
compare_metadataagainst the migrated schema. The job fails on any difference that is not in an allowlist file. The allowlist starts with the 389 current differences. A later PR may only shrink the allowlist. - Add migration-lint rules:
- A new foreign key to
userfrom an actor column must not useCASCADE. - A new foreign key to a lookup table must use
RESTRICT. - A migration must not create a plain index on columns that already carry a unique constraint.
- A new timestamp column must use
timestamptz. - A new tenant-keyed table must declare its tenant column
NOT NULL.
- A new foreign key to
Benefit: tests exercise RLS policies, unique constraints and triggers. New drift fails CI instead of reaching production.
Cost:
- 3 PRs. No schema change.
- The template build gains about 18 seconds (0.6 s baseline load plus 17.7 s migration replay, measured locally). The suite builds the template once per session.
Reversibility: fully reversible. Each change is CI or test configuration.
W2: correctness fixes without schema changes
Changes:
Rename the ORM attributes of the two task flags. The database columns keep their names:
hidden_from_portal = mapped_column("task_is_internal", ...)is_internal_task = mapped_column("is_internal", ...)
Route every portal filter through one visibility predicate.
PortalService.assert_task_portal_visibleis the starting point. The rename touches 63 Python files and 34 frontend files. DTO field names stay unchanged, so the API contract does not change.Point the dashboard Activities tile at
or_activity_history.Base the
ORStandardModelhybridsis_deletedandis_archivedon the timestamp alone. Production has no row that the current hybrids misread, so this change closes a latent defect only.Replace the 26
default=datetime.now()call sites that run once at import (for exampleapp/api/ramps/dtos/ramp_dto.py:17) withdefault_factory. Remove the hardcoded-4offset inapp/api/utils/datetime.py:13.
Benefit: closes the bug class behind #10773, #12704 and #12735. Restores a dashboard metric.
Cost: 4 PRs. No migration. Change 1 is the largest, by file count.
Reversibility: fully reversible.
W3: tenant keys
Changes:
- Investigate the 1,022 rows with a cross-vendor parent (310 task steps and 712 data-field values) and the 64 tasks that reference another vendor's status row. Find the code path that wrote them. Fix that path. Correct or delete the rows. Report whether any row was visible to another vendor.
- Set
vendoron the 235 modules that lack one. AddCHECK (vendor IS NOT NULL) NOT VALIDtoor_tasks,or_modulesandor_task_steps. Validate the checks. RunSET NOT NULL, which skips the table scan on PostgreSQL 12 and later because a validated check exists.VALIDATE CONSTRAINTdoes not block reads or writes. - Fix the application writers that can create these rows without a vendor. 27 constructor call sites exist. Only 5 of the 9
ORTaskStep(sites passvendor=near the constructor (inferred; some may set it later). - Add
vendor_idtoor_data_field_valuesand backfill it in batches (949,524 rows, 221 MB). Add a unique constraint onor_data_fields(id, vendor_id). Add a composite foreign key from(field_id, vendor_id)to that constraint. Makefield_idandvendor_idNOT NULL. Delete the 212 values whose project no longer exists. - Add a composite tenant foreign key from
or_task_steps(task_id, vendor)toor_tasks(id, vendor). Add the same pattern foror_comment,or_note,or_time_entriesandor_portal_favoritetoor_projects. The SMS and journeys tables already use this pattern (fk_or_sms_notifications_project_owner). - Correct template C in
rls-tenant-isolation.mdto usetask_id.
The four spellings of the tenant column (vendor_id, vendor, or_vendor_id, org_id) stay. A rename touches 886 references in four models alone (ORProject, User, ORTask, ORPlaybook). The ORM-scoping RFC can add a SQLAlchemy synonym per class instead.
Benefit: the database rejects rows without a tenant and rows whose tenant disagrees with the parent's tenant. The RLS RFC can ship without breaking writes.
Cost:
- About 8 PRs.
- Change 1 is an investigation of unknown size.
- The composite key on
or_task_stepsneeds a unique index onor_tasks(id, vendor)(2.98 million rows), built withCREATE UNIQUE INDEX CONCURRENTLY. Validating the foreign key scansor_task_steps(6.56 million rows, 22 GB) without blocking writes.
Reversibility: reversible. Dropping a NOT NULL or a foreign key is instant. Deleted orphan rows cannot be restored without a backup.
W4: one source of truth for lifecycle state
Changes:
- Add
CHECK (NOT (is_draft AND is_published))onor_playbooks. Production has no violating row. - Add a partial unique index that allows one live published playbook per series. Production has no violating series.
- Add
CHECK (num_nonnulls(project_id, playbook_id) <= 1)onor_tasks. Production has no violating row. - Add a unique constraint on
or_task_mappings.or_task(2.97 million rows, no duplicates). - Reconcile the 373 projects whose status and
project_archived_dtsdisagree. Pick the status code as the authority. Deriveproject_archived_dtsfrom status changes in one service function. - Stop writing
or_projects.healthandor_projects.engagement. Move the project export and the other legacy readers toproject_healthandproject_engagement. Drop the legacy pair one release later. - Declare one authoritative archive column per core table in
.claude/rules/backend-models.md. Remove the unused columns through expand and contract:archived_at,archived_by,deleted_atanddeleted_byonor_projects,or_tasksanduser, andarchived_dateandarchived_atonor_playbooks. No production row sets any of them. - Drop the
DEFAULT 1onor_tasks.task_status.
Benefit: four invariants move from code into the database without any data cleanup. One duplicated JSON pair disappears. Exports and the application report the same health.
Cost: about 8 PRs. Each constraint uses NOT VALID followed by VALIDATE.
Reversibility: reversible, except the column drop in change 6.
W5: delete semantics
Rules:
| Foreign key kind | Delete action | Foreign keys that differ today |
|---|---|---|
To or_vendors | CASCADE | 26 |
Actor column to user | SET NULL | about 45 |
| To a lookup table | RESTRICT | 26 |
| Owned child to its parent | CASCADE | unchanged |
Changes:
- Rewrite the organization-deletion helper so that it deletes the vendor row and relies on the vendor cascade. This rewrite comes first, because the helper depends on the actor cascades today.
- Remove lookup-row deletes from migration
downgrade()functions. - Change the foreign keys in batches, one table per migration. Each migration drops the constraint and re-adds it as
NOT VALID. A follow-up migration runsVALIDATE CONSTRAINT.
Benefit: a user deletion can no longer remove projects, playbooks or other users. A lookup deletion fails loudly. Vendor offboarding becomes one statement.
Cost:
- About 97 foreign-key changes, grouped into roughly 15 to 20 migrations by table (estimate).
- Re-adding a foreign key takes a brief
SHARE ROW EXCLUSIVElock on both tables.user,or_projectsandor_tasksare busy tables, so these migrations run one table at a time.
Reversibility: reversible. Each migration can restore the previous action.
W6: data-field write codec
Changes:
- Add one write function per field type. The function normalizes the value or rejects it. Example rules:
- Any field: an empty string,
NULLor the stringNonemeans "no value". The codec deletes the row instead of storing a placeholder. - A number field accepts a numeric string and stores its canonical form.
- A date field accepts an ISO date and stores
YYYY-MM-DD. - A user field accepts a user UUID from the same vendor.
- Any field: an empty string,
- Route every writer through the codec: UI, public API, CRM sync, imports, automations and agents.
- Clean up the existing rows in one batched migration: delete the 466,568 placeholder rows (empty string,
NULLorNone). Fix or report the 644 malformed values.
Benefit: new malformed values stop entering the table. "No value" gets one representation. The table shrinks by about half. The defensive readers stop growing, and the filters stop checking four kinds of emptiness.
Cost: about 4 PRs. One data migration.
Reversibility: changes 1 and 2 are fully reversible. Change 3 deletes rows. Mitigation: a pg_dump of the affected rows before the delete.
W7: remove dead schema
Changes:
- Drop the tables in Appendix B whose production check shows them empty or frozen.
- Drop the 11 production-only backup tables after a retention decision.
- Decide the fate of the legacy activity tables: backfill them into
or_activity_history, or freeze their history and accept its loss from the feed. Then drop the tables andlegacy_providers.py. - Stop writing
or_lane_activityif no reader exists outside this repository. Archive or drop its 9.2 GB.
Each drop follows the two-release contract that the squawk rule ban-drop-table requires. Before each drop, the PR checks three consumers:
- Sequelize models in
crm-integrations/onramp-database-js. - Models in
customer-api. - The data warehouse, which replicates every table missing from
DEFAULT_EXCLUDE_TABLES(infra/datawarehouse/config.ts:10-66).
Benefit: fewer tables to read, search and reason about. Less code in the feed path. About 10 GB less storage if change 4 lands. Customer data stops living in unmodeled backup tables.
Cost: one PR per cluster. About 6 to 8 PRs in total.
Reversibility: irreversible for the data. Mitigation: the two-release contract, and a pg_dump of each table before its drop.
W8: type and index hygiene
Changes:
- Drop the 35 duplicate indexes with
DROP INDEX CONCURRENTLY. - Collect index statistics from the primary. Drop the indexes that both the primary and the replica show as unused.
- Add unique indexes on
user.user_uuidandaccount.account_uuidwithCREATE UNIQUE INDEX CONCURRENTLY. Production holds no duplicates. - Validate the 11
NOT VALIDconstraints, or drop them if their rule no longer holds. - Convert the five queried
jsoncolumns tojsonbthrough expand and contract: add ajsonbcolumn, write both columns, backfill, switch the readers, drop the old column. A directALTER TYPErewrites the table under an exclusive lock, so this RFC does not use it.or_activity_history.datasits in a 23 GB table, so its conversion is the last one. - Add
CHECKconstraints to unconstrained status columns when another workstream already touches the table.
Benefit: lower write cost on the affected tables. Identifier lookups use an index. JSON queries stop casting at query time.
Cost: about 8 PRs.
Reversibility: reversible, except the column drops in change 5.
Sequencing
| Order | Workstream | Depends on | PRs (estimate) |
|---|---|---|---|
| 1 | W1 guardrails | none | 3 |
| 2 | W2 correctness fixes | none | 4 |
| 3 | W3 tenant keys | W1 | 8, plus the investigation |
| 4 | W4 lifecycle state | W1 | 8 |
| 5 | W6 data-field codec | none | 4 |
| 6 | W5 delete semantics | W1 | 15 to 20 |
| 7 | W7 dead schema | W1 | 6 to 8 |
| 8 | W8 hygiene | W1 | 8 |
W1 and W2 can run in parallel. W3, W4 and W6 can run in parallel after W1. W5 comes after W3 and W4 because no current code path is likely to trigger the harmful cascades (inferred). The PR counts are estimates from the size of each change, not measurements.
Deferred decisions
Each item needs its own decision and its own document. This RFC makes each item smaller but does not start it.
- Retire Lanes in favor of Ramps. Lanes spans about 13,500 lines, 6 tables and two Lambdas in
crm-integrations. In the last 30 days, 5 vendors received lane inbound items, and 44 vendors ran ramps. - Retire the v1 integration pipeline.
subscribed_integration_actionsholds 50 rows from 2 vendors, and its newest row is from 2023-10-09.integration_authorizationsis empty. - Store each project's CRM link in one place. Six places hold it today.
- Point status foreign keys at the global
or_object_status_coderows, and drop the per-vendor copies inor_object_status(2,368 rows: 8 per vendor for 296 vendors). - Keep one representation of a task's parent, either the columns or the mapping chain.
- Add typed shadow columns to
or_data_field_values(value_numeric,value_date,value_user_id) for filtering and sorting. W6 makes the backfill safe. - Merge the six stores of automation rules (library, module, playbook, project, lane, ramp).
- Retire
filtersandfilter_mappingsafter the Projects v2 general release, and renameor_projects_user_filter. - Convert the 148 timestamp columns without a time zone.
- Rebaseline the migration history, and set a policy for merge migrations. 84 merge migrations exist, and 35 of them landed in September 2026.
- Remove the
ORStandardModelaudit columns from the tables that never use them. 84 tables carry the columns. 26 tables hold any soft-deleted or archived row.
Alternatives considered
- Guardrails only (W1). This alternative stops new drift. It leaves the tenant disagreements, the visibility-flag bug class and the lifecycle conflicts in place. Rejected because W2, W3 and W4 each have an observed defect behind them.
- One redesign program that covers the deferred decisions. This alternative has the largest payoff on paper. It needs product decisions, long dual-write periods and several irreversible steps. Rejected as a first step. The deferred items remain open.
- Rename every tenant column to
vendor_id. This alternative removes one naming inconsistency. It touches 886 references in four models alone and changes no behavior. Rejected. - No change. The defensive code keeps growing, and the defect classes in Problems 2 to 5 stay open.
Risks
- The cross-vendor rows in W3 change 1 reflect a defect that is still writing new rows.
- Mitigation: the investigation finds the writer before the composite foreign keys land. The foreign keys then block new rows.
- Lock contention on busy tables during W3 and W5.
- Mitigation:
NOT VALIDfollowed byVALIDATE, one table per migration, deploys outside peak hours, and the existinglock_safe_ddlhelpers.
- Mitigation:
- A dropped table still has a reader outside this repository.
- Mitigation: the three-consumer check in W7 and the two-release contract.
- The organization-deletion rewrite in W5 misses a table.
- Mitigation: a test that creates a vendor with data in every vendor-keyed table, deletes the vendor, and asserts that no row of that vendor remains.
- Deleting placeholder data-field rows in W6 changes a reader that treats a missing row differently from an empty string.
- Mitigation: the codec lands and runs in production before the cleanup. The cleanup PR lists every reader of
or_data_field_values.value.
- Mitigation: the codec lands and runs in production before the cleanup. The cleanup PR lists every reader of
- Test runs slow down after W1.
- Mitigation: the template database is built once per session. The measured cost is about 18 seconds.
- The W1 allowlist becomes permanent.
- Mitigation: the CI job reports the allowlist size. Each workstream PR removes the entries it fixes.
Open questions
- Legacy activity history: backfill it into
or_activity_history, or freeze it? The answer decides the cost of W7 change 3. - Data-field name uniqueness: per vendor, or per vendor and object type? Two application checks disagree today (
data_fields_services.py:167and:172). Production holds 41 groups of live fields with duplicate names within a vendor. user.email_addressuniqueness: should one vendor allow an internal user and a customer user with the same address? Production holds 762 such pairs. Production also holds 81 duplicate groups within one vendor and one user type.- Retention of the 11 production backup tables: who decides, and is a copy kept?
- Who reads
or_lane_activityoutside this repository? - Which team owns each workstream?
Appendix A: production measurements
Read replica, 2026-10-06 03:56 UTC. Aggregates only.
Sizes of the tables this RFC changes
| Table | Rows | Total size |
|---|---|---|
or_activity_history | 9.77 M | 23 GB |
or_task_steps | 6.56 M | 22 GB |
or_lane_activity | 4.72 M | 9.2 GB |
or_tasks | 2.98 M | 3.7 GB |
or_projects | 108,806 | 3.0 GB |
or_project_activity | 1.12 M | 806 MB |
or_task_mappings | 2.97 M | 542 MB |
or_modules | 652,661 | 276 MB |
or_data_field_values | 949,524 | 221 MB |
or_playbook_activity | 346,577 | 198 MB |
user | 105,650 | 190 MB |
The public schema holds 280 tables and 116 GB.
Hard deletes
The replica records no delete counts. The gap between the highest id and the row count gives an upper bound.
| Table | Rows | Highest id | Gap |
|---|---|---|---|
user | 105,650 | 108,469 | 2,819 |
or_projects | 108,806 | 111,713 | 2,907 |
or_playbooks | 11,432 | 12,558 | 1,126 |
or_vendors | 458 | 529 | 71 |
or_object_status | 2,368 | 3,920 | 1,552 |
No row in or_projects, or_tasks, or_task_steps or user has deleted_at set. These tables use hard deletes only.
Data-field values by field type
| Type | Values | Empty string | Malformed (non-empty) |
|---|---|---|---|
| dropdown | 474,281 | 203,842 | not checked |
| text | 298,082 | 150,090 | not applicable |
| date | 125,506 | 79,491 | 574 |
| user | 17,703 | 14,738 | 21 |
| number | 14,338 | 10,354 | 49 |
| link | 12,555 | 72 | not checked |
| monetary | 3,711 | 2,348 | 0 |
| multi_select | 3,346 | 2,625 | 0 |
Lanes and Ramps activity
| Measure | Value |
|---|---|
| Vendors with lane inbound items, last 30 days | 5 |
| Vendors with lane inbound items, last 90 days | 7 |
| Lanes not archived | 243, across 48 vendors |
| Vendors with ramp executions, last 30 days | 44 |
| Ramps live and unpaused | 440 |
Appendix B: dead schema candidates
| Object | Evidence in code | Evidence in production |
|---|---|---|
or_ai_conversations, or_ai_messages, or_ai_creation_sessions | No model. Code removed on 2026-04-03. | 0 rows |
or_ai_task | No model. Code removed on 2026-09-10. A Sequelize model in crm-integrations references it. | 139 rows, newest 2024-04-30 |
vendor_exports | No model. Code removed on 2026-09-17. | 1,418 rows, newest 2024-10-10 |
migration_notifications | No model. Created by a manual migration outside Alembic. | 6 rows, newest 2026-05-22 |
or_action_mapping | No writer. Its one route, GET /action, has no frontend caller. | 0 rows |
vendor_email_configuration, vendor_email_configuration_user | One route, with no frontend caller. | 0 rows |
project_states | One reader, with no caller. | 0 rows |
or_lane_project_creation_queue | No writer. Its one reader has no caller. | 0 rows |
or_authorization_oauth2 | Referenced only by a foreign key and a relationship. A Sequelize model exists. | 0 rows |
or_project_activity, or_playbook_activity, or_task_activity | No writer. Read by legacy_providers.py and the dashboard tile. | Frozen since 2026-01-22, 2026-02-18 and 2024-01-29 |
or_lane_activity | Written, never read in this repository. | 4.72 M rows, 9.2 GB, about 205,000 new rows every 30 days |
11 backup tables (backup_onramp_6537_*, oei_4872_*, *_html_entities_backup, or_projects_name_onramp_4596_backup) | Not in any migration or model. | About 24 MB in total |
or_task_steps.old_step_configuration, old_action_response | No writer in the app. Two manual repair scripts in devtools/manual-migrations/ write old_step_configuration. | Not measured |
task_types.old_configuration | Model and seed migration only. | Not measured |
or_lane_rule.old_action_configuration | Read, never written. | Not measured |
or_projects.health, or_projects.engagement | Written in parallel with project_health and project_engagement. Covered by W4. | In a 20% sample, every value is a JSON string that contains encoded JSON |
or_project_user_mappings.user_role | No writer. | 0 of 257,071 rows set (docs/research/rbac-current-state.md:577) |