Skip to content

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 ​

BenefitWhat changes for the team or the customerDelivered by
Tenant isolation enforced by the databaseThe 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 bugsThe 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 productionTests 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 questionEach fact (archived, completed, published) has one authoritative column. Today 373 projects disagree between their status and their archive timestamp.W4
Less defensive codeOne 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 deleteDeleting 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 understandDead 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 cost35 duplicate indexes go away. Each removed index removes work from every insert and update on its table.W8
Future flexibilityTyped 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 with create_all.
  • A grep of this repository, crm-integrations and customer-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 id sequences, 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.

#RuleMechanismViolating rows in production
1Every row in or_tasks, or_modules, or_task_steps and or_data_field_values has a tenant.NOT NULL on the tenant column235 (or_modules only)
2A 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
3Deleting a vendor deletes all of that vendor's data.ON DELETE CASCADE on every foreign key to or_vendorsNot applicable
4Deleting a user never deletes another row.ON DELETE SET NULL on every actor columnNot applicable
5A lookup row that is in use cannot be deleted.ON DELETE RESTRICT on every foreign key to a lookup tableNot applicable
6A playbook series has at most one live published version.Partial unique index0
7A playbook version is never both draft and published.CHECK constraint0
8A task belongs to a project or to a playbook, never to both.CHECK (num_nonnulls(project_id, playbook_id) <= 1)0
9A task has at most one task mapping.Unique constraint on or_task_mappings.or_task0
10The 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:

    Objectcreate_allMigrated
    RLS policies059
    Unique constraints99134
    Partial indexes6592
    id columns without a default145
  • At least 12 test files contain workarounds for missing id defaults.

  • Alembic's compare_metadata reports 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_id as NOT NULL. The database allows NULL.
    • 19 column types differ.
  • app/api/base/schema_drift.py detects 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 VALID that nobody validated.

Problem 2: tenant keys have gaps and disagreements ​

  • or_tasks.vendor, or_modules.vendor and or_task_steps.vendor allow NULL. In production, 235 modules have no vendor. No task and no step lacks a vendor.

  • or_data_field_values has no tenant column. Its field_id allows NULL, but no production row uses NULL.

  • Tenant disagreements in production:

    RelationshipRows whose vendor differs from the parent's vendor
    Task step to task310
    Project data-field value to its field712
    Task to the status row it references64
    Task to project2
  • 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 a NULL tenant and rejects inserts without one.

  • The RLS RFC's template C references or_task_steps.or_task_id. The real column is task_id.

Problem 3: one fact, several columns ​

  • Task visibility and task kind use nearly identical names:
    • or_tasks.task_is_internal means "hidden from the portal".
    • or_tasks.is_internal means "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 have project_archived_dts without the archived status.
  • Playbook archive state lives in three places: is_archived, archived_date and archived_at. In production, all 3,880 archived playbooks use only is_archived. archived_date and archived_at are never set.
  • 13 tables carry both a boolean flag and a timestamp for the same state.
  • Playbook version state uses two booleans, is_draft and is_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_id and or_tasks.playbook_id, and in the or_task_mappings to or_module_mappings chain. 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.health and or_projects.engagement duplicate project_health and project_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_status defaults to 1. No or_object_status row has id 1 in production. An insert that relies on the default fails.

Problem 4: foreign keys delete data that should survive ​

  • 80 foreign keys to user use ON DELETE CASCADE. About 45 of them are actor columns. Examples confirmed in production:
    • or_projects.created_by, project_owner_user, project_completed_by and project_archived_by.
    • or_tasks.completed_by.
    • user.created_by_user_id, which points at user itself. 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_status and or_tasks.task_status reference or_object_status.
    • or_task_steps.step_type references task_types.
  • The downgrade() of migration 2025_12_10_5d3f32d30eac deletes a task_types row. Running that downgrade in production would delete every List-form step of every customer.
  • Today the application hard-deletes user rows 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).
  • 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_vendors do 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 on or_comment.created_by_user_id.

Problem 5: data-field values are untyped text ​

  • or_data_field_values.value is 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:

    RepresentationRows
    No rowNot countable
    NULL2,576
    Empty string463,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_activity and or_task_activity stopped 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 through legacy_providers.py (710 lines).
  • The dashboard Activities tile (app/api/dashboard/services.py:107-128) counts only or_project_activity for 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_activity holds 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 uuid next to a unique constraint on uuid. 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 use jsonb. Five json columns are cast to jsonb at query time: or_projects.project_health, or_projects.project_engagement, or_ramp_workflows.trigger, the rule actions columns, and or_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_uuid and account.account_uuid have no unique constraint. Production holds no duplicates in either column. account.account_uuid is the identifier in URLs and has no index.
  • user.email_address has 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 CHECK constraint.

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:

  1. Build the test template database from devtools/db/schema-baseline.sql plus flask db upgrade heads, instead of create_all. Remove the id-default workarounds from the test files.
  2. Add a CI job that runs compare_metadata against 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.
  3. Add migration-lint rules:
    • A new foreign key to user from an actor column must not use CASCADE.
    • 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.

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:

  1. 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_visible is 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.

  2. Point the dashboard Activities tile at or_activity_history.

  3. Base the ORStandardModel hybrids is_deleted and is_archived on the timestamp alone. Production has no row that the current hybrids misread, so this change closes a latent defect only.

  4. Replace the 26 default=datetime.now() call sites that run once at import (for example app/api/ramps/dtos/ramp_dto.py:17) with default_factory. Remove the hardcoded -4 offset in app/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:

  1. 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.
  2. Set vendor on the 235 modules that lack one. Add CHECK (vendor IS NOT NULL) NOT VALID to or_tasks, or_modules and or_task_steps. Validate the checks. Run SET NOT NULL, which skips the table scan on PostgreSQL 12 and later because a validated check exists. VALIDATE CONSTRAINT does not block reads or writes.
  3. Fix the application writers that can create these rows without a vendor. 27 constructor call sites exist. Only 5 of the 9 ORTaskStep( sites pass vendor= near the constructor (inferred; some may set it later).
  4. Add vendor_id to or_data_field_values and backfill it in batches (949,524 rows, 221 MB). Add a unique constraint on or_data_fields(id, vendor_id). Add a composite foreign key from (field_id, vendor_id) to that constraint. Make field_id and vendor_id NOT NULL. Delete the 212 values whose project no longer exists.
  5. Add a composite tenant foreign key from or_task_steps(task_id, vendor) to or_tasks(id, vendor). Add the same pattern for or_comment, or_note, or_time_entries and or_portal_favorite to or_projects. The SMS and journeys tables already use this pattern (fk_or_sms_notifications_project_owner).
  6. Correct template C in rls-tenant-isolation.md to use task_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_steps needs a unique index on or_tasks(id, vendor) (2.98 million rows), built with CREATE UNIQUE INDEX CONCURRENTLY. Validating the foreign key scans or_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:

  1. Add CHECK (NOT (is_draft AND is_published)) on or_playbooks. Production has no violating row.
  2. Add a partial unique index that allows one live published playbook per series. Production has no violating series.
  3. Add CHECK (num_nonnulls(project_id, playbook_id) <= 1) on or_tasks. Production has no violating row.
  4. Add a unique constraint on or_task_mappings.or_task (2.97 million rows, no duplicates).
  5. Reconcile the 373 projects whose status and project_archived_dts disagree. Pick the status code as the authority. Derive project_archived_dts from status changes in one service function.
  6. Stop writing or_projects.health and or_projects.engagement. Move the project export and the other legacy readers to project_health and project_engagement. Drop the legacy pair one release later.
  7. 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_at and deleted_by on or_projects, or_tasks and user, and archived_date and archived_at on or_playbooks. No production row sets any of them.
  8. Drop the DEFAULT 1 on or_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 kindDelete actionForeign keys that differ today
To or_vendorsCASCADE26
Actor column to userSET NULLabout 45
To a lookup tableRESTRICT26
Owned child to its parentCASCADEunchanged

Changes:

  1. 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.
  2. Remove lookup-row deletes from migration downgrade() functions.
  3. 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 runs VALIDATE 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 EXCLUSIVE lock on both tables. user, or_projects and or_tasks are 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:

  1. Add one write function per field type. The function normalizes the value or rejects it. Example rules:
    • Any field: an empty string, NULL or the string None means "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.
  2. Route every writer through the codec: UI, public API, CRM sync, imports, automations and agents.
  3. Clean up the existing rows in one batched migration: delete the 466,568 placeholder rows (empty string, NULL or None). 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:

  1. Drop the tables in Appendix B whose production check shows them empty or frozen.
  2. Drop the 11 production-only backup tables after a retention decision.
  3. 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 and legacy_providers.py.
  4. Stop writing or_lane_activity if 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:

  1. Drop the 35 duplicate indexes with DROP INDEX CONCURRENTLY.
  2. Collect index statistics from the primary. Drop the indexes that both the primary and the replica show as unused.
  3. Add unique indexes on user.user_uuid and account.account_uuid with CREATE UNIQUE INDEX CONCURRENTLY. Production holds no duplicates.
  4. Validate the 11 NOT VALID constraints, or drop them if their rule no longer holds.
  5. Convert the five queried json columns to jsonb through expand and contract: add a jsonb column, write both columns, backfill, switch the readers, drop the old column. A direct ALTER TYPE rewrites the table under an exclusive lock, so this RFC does not use it. or_activity_history.data sits in a 23 GB table, so its conversion is the last one.
  6. Add CHECK constraints 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 ​

OrderWorkstreamDepends onPRs (estimate)
1W1 guardrailsnone3
2W2 correctness fixesnone4
3W3 tenant keysW18, plus the investigation
4W4 lifecycle stateW18
5W6 data-field codecnone4
6W5 delete semanticsW115 to 20
7W7 dead schemaW16 to 8
8W8 hygieneW18

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.

  1. 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.
  2. Retire the v1 integration pipeline. subscribed_integration_actions holds 50 rows from 2 vendors, and its newest row is from 2023-10-09. integration_authorizations is empty.
  3. Store each project's CRM link in one place. Six places hold it today.
  4. Point status foreign keys at the global or_object_status_code rows, and drop the per-vendor copies in or_object_status (2,368 rows: 8 per vendor for 296 vendors).
  5. Keep one representation of a task's parent, either the columns or the mapping chain.
  6. 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.
  7. Merge the six stores of automation rules (library, module, playbook, project, lane, ramp).
  8. Retire filters and filter_mappings after the Projects v2 general release, and rename or_projects_user_filter.
  9. Convert the 148 timestamp columns without a time zone.
  10. Rebaseline the migration history, and set a policy for merge migrations. 84 merge migrations exist, and 35 of them landed in September 2026.
  11. Remove the ORStandardModel audit columns from the tables that never use them. 84 tables carry the columns. 26 tables hold any soft-deleted or archived row.

Alternatives considered ​

  1. 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.
  2. 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.
  3. 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.
  4. No change. The defensive code keeps growing, and the defect classes in Problems 2 to 5 stay open.

Risks ​

  1. 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.
  2. Lock contention on busy tables during W3 and W5.
    • Mitigation: NOT VALID followed by VALIDATE, one table per migration, deploys outside peak hours, and the existing lock_safe_ddl helpers.
  3. A dropped table still has a reader outside this repository.
    • Mitigation: the three-consumer check in W7 and the two-release contract.
  4. 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.
  5. 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.
  6. Test runs slow down after W1.
    • Mitigation: the template database is built once per session. The measured cost is about 18 seconds.
  7. The W1 allowlist becomes permanent.
    • Mitigation: the CI job reports the allowlist size. Each workstream PR removes the entries it fixes.

Open questions ​

  1. Legacy activity history: backfill it into or_activity_history, or freeze it? The answer decides the cost of W7 change 3.
  2. Data-field name uniqueness: per vendor, or per vendor and object type? Two application checks disagree today (data_fields_services.py:167 and :172). Production holds 41 groups of live fields with duplicate names within a vendor.
  3. user.email_address uniqueness: 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.
  4. Retention of the 11 production backup tables: who decides, and is a copy kept?
  5. Who reads or_lane_activity outside this repository?
  6. 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 ​

TableRowsTotal size
or_activity_history9.77 M23 GB
or_task_steps6.56 M22 GB
or_lane_activity4.72 M9.2 GB
or_tasks2.98 M3.7 GB
or_projects108,8063.0 GB
or_project_activity1.12 M806 MB
or_task_mappings2.97 M542 MB
or_modules652,661276 MB
or_data_field_values949,524221 MB
or_playbook_activity346,577198 MB
user105,650190 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.

TableRowsHighest idGap
user105,650108,4692,819
or_projects108,806111,7132,907
or_playbooks11,43212,5581,126
or_vendors45852971
or_object_status2,3683,9201,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 ​

TypeValuesEmpty stringMalformed (non-empty)
dropdown474,281203,842not checked
text298,082150,090not applicable
date125,50679,491574
user17,70314,73821
number14,33810,35449
link12,55572not checked
monetary3,7112,3480
multi_select3,3462,6250

Lanes and Ramps activity ​

MeasureValue
Vendors with lane inbound items, last 30 days5
Vendors with lane inbound items, last 90 days7
Lanes not archived243, across 48 vendors
Vendors with ramp executions, last 30 days44
Ramps live and unpaused440

Appendix B: dead schema candidates ​

ObjectEvidence in codeEvidence in production
or_ai_conversations, or_ai_messages, or_ai_creation_sessionsNo model. Code removed on 2026-04-03.0 rows
or_ai_taskNo model. Code removed on 2026-09-10. A Sequelize model in crm-integrations references it.139 rows, newest 2024-04-30
vendor_exportsNo model. Code removed on 2026-09-17.1,418 rows, newest 2024-10-10
migration_notificationsNo model. Created by a manual migration outside Alembic.6 rows, newest 2026-05-22
or_action_mappingNo writer. Its one route, GET /action, has no frontend caller.0 rows
vendor_email_configuration, vendor_email_configuration_userOne route, with no frontend caller.0 rows
project_statesOne reader, with no caller.0 rows
or_lane_project_creation_queueNo writer. Its one reader has no caller.0 rows
or_authorization_oauth2Referenced only by a foreign key and a relationship. A Sequelize model exists.0 rows
or_project_activity, or_playbook_activity, or_task_activityNo writer. Read by legacy_providers.py and the dashboard tile.Frozen since 2026-01-22, 2026-02-18 and 2024-01-29
or_lane_activityWritten, 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_responseNo writer in the app. Two manual repair scripts in devtools/manual-migrations/ write old_step_configuration.Not measured
task_types.old_configurationModel and seed migration only.Not measured
or_lane_rule.old_action_configurationRead, never written.Not measured
or_projects.health, or_projects.engagementWritten 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_roleNo writer.0 of 257,071 rows set (docs/research/rbac-current-state.md:577)

Internal documentation — gated behind Cloudflare Access.