`feat` PRD: table-level CHECK constraints (multi-column), auto-migrated

[0x054]

Requested by: Pinch • Blocks: pinch-backend#234 (linked-activity Transfer shape, pinch-backend#230)

Grilled 2026-08-18. ADRs 0012–0016. Glossary: table check, check predicate, column check.

Problem Statement

A Ferro model can say “this column is unique” and “this closed-domain column is IN (...)”. It cannot say “this boolean over several columns of one row must hold.” Auto-migrate therefore never emits a named table-level CHECK, and a later ALTER never adds one either.

Pinch’s Transfer is the concrete hole: four nullable unique FKs (outflow/inflow transaction, outflow/inflow activity). Illegal shapes — both ends of one side set, activity↔activity, empty row — are facts about a single row, not a query filter. Pinch’s domain data lives on Ferro models; raw SQL ALTER TABLE … ADD CHECK is refused. __ferro_checks__ today is an unknown ClassVar: a silent no-op.

Solution

Declare table checks on the model as Check(suffix, predicate) in __ferro_checks__. The predicate is a ferro lambda over that table’s columns (a check predicate). Ferro emits a named CHECK on CREATE TABLE (both dialects) and, under migrate_updates, ALTER TABLE … ADD CONSTRAINT / constraint rebuild on PostgreSQL. Removing a table check drops only under migrate_destructive; migrate_updates warns with the live name and leaves it. Violating inserts/updates raise the existing CheckViolationError. Column checks (Field(db_check=True)) keep their Field API and ride the same ck_* reconcile pass.

class Transfer(Model):
    __ferro_checks__: ClassVar[tuple[Check, ...]] = (
        Check("at_most_one_outflow", lambda t: (t.outflow_transaction == None) | (t.outflow_activity == None)),
        Check("at_most_one_inflow", lambda t: (t.inflow_transaction == None) | (t.inflow_activity == None)),
        Check("at_least_one_transaction", lambda t: (t.outflow_transaction != None) | (t.inflow_transaction != None)),
        Check("at_least_one_side", lambda t: (t.outflow_transaction != None) | (t.inflow_transaction != None) | (t.outflow_activity != None) | (t.inflow_activity != None)),
    )

Live names: ck_transfer_at_most_one_outflow, etc. Shadow *_id spellings of the same null tests are also valid.

User Stories

1. As an application author, I want to declare a named boolean invariant over several columns of one model, so that the database rejects illegal rows even if Python validation is bypassed.
2. As an application author, I want that invariant written as a lambda like where(), so that I do not embed SQL dialect in the model.
3. As an application author, I want a Check helper in __ferro_checks__, so that the name and the predicate are distinct slots and not a raw 2-tuple.
4. As an application author, I want Check imported from ferro, so that declaration matches Field / BackRef.
5. As an application author, I want the live constraint named ck_<table>_<suffix>, so that I can recognize it in pg_constraint and in error attributes.
6. As an application author, I want a 63-character truncation of that name matching column checks, so that Postgres does not silently clip it into a collision.
7. As Pinch, I want Transfer to reject both a transaction and an activity on the outflow side, so that a side is never two things at once.
8. As Pinch, I want Transfer to reject both a transaction and an activity on the inflow side, so that a side is never two things at once.
9. As Pinch, I want Transfer to reject activity↔activity (no transaction on either side), so that a Transfer always involves at least one transaction.
10. As Pinch, I want Transfer to reject a fully empty row, so that a Transfer always has at least one side.
11. As Pinch, I want a Transfer with one transaction only (untracked other side) to succeed, so that the current untracked-leg shape still works.
12. As Pinch, I want a Transfer with one transaction and the opposite-side activity to succeed, so that the mixed-leg shape works.
13. As Pinch, I want those four table checks present in pg_constraint (contype = 'c') after auto_migrate=True, migrate_updates=True, so that the invariant is actually in the database.
14. As Pinch’s API layer, I want a violating insert to raise CheckViolationError (an IntegrityError) with .constraint set to the live name, so that I can translate it to HTTP 422 without string-matching driver text.
15. As an application author, I want a violating update() to raise the same CheckViolationError, so that bulk paths cannot bypass the invariant.
16. As an application author, I want t.outflow_transaction == None to mean the shadow FK is null, so that I can write the check the same way I write where().
17. As an application author, I want t.outflow_transaction_id == None to produce the same CHECK body, so that thinking in columns still works.
18. As an application author, I want t.outflow_transaction.amount in a check predicate to fail at build time, so that I cannot declare a CHECK that other tables cannot satisfy.
19. As an application author, I want .exists() in a check predicate to fail at build time, so that I cannot declare a CHECK with a subquery.
20. As an application author, I want aggregates in a check predicate to fail at build time, so that I cannot declare a CHECK that is not row-local.
21. As an application author, I want lambda t: t.amount > min_amount (a closed-over variable) to fail at class definition, so that schema does not snapshot import-time state.
22. As an application author, I want lambda t: t.amount >= 0 with a literal to work, so that simple numeric bounds are table checks.
23. As an application author, I want lambda t: t.end > t.start to work, so that column-to-column comparisons are table checks.
24. As an application author, I want .in_((...)) and .like(...) with literals to work, so that the root-column where() dialect is not hollowed out.
25. As an application author, I want enum members as literals (t.status == Status.OPEN) inlined into the CHECK, so that I do not hand-write label strings.
26. As an application author, I want &, |, and ~ to compose check predicates, so that the Transfer ORs and future NANDs are spellable.
27. As an application author, I want an unknown column in a check predicate to fail at class definition (or resolved registration for shadow FKs), so that typos do not become silent no-ops.
28. As an application author, I want two table checks with the same suffix on one model to fail at class definition, so that I cannot emit two constraints with one name.
29. As an application author, I want a table-check suffix that collides with a column check’s full name (ck_<table>_<col>) to fail at class definition, so that ownership by prefix stays unambiguous.
30. As an application author, I want an empty or non-identifier suffix to fail at class definition, so that I cannot emit an un-ownable or un-droppable name.
31. As an application author, I want an empty __ferro_checks__ to be a no-op, so that the ClassVar can exist as a default on Model.
32. As an application author, I want several table checks on one model, so that Transfer’s four invariants are separate named constraints, not one opaque AND.
33. As an application author, I want CREATE TABLE for a new model to include every table check, so that a fresh database matches the model without a second pass.
34. As an application author on SQLite, I want a fresh CREATE TABLE to include those CHECKs, so that tests against SQLite actually enforce the Transfer invariants.
35. As an application author on SQLite, I want adding a table check to an already-live table to warn with the constraint name and skip, so that Ferro does not attempt a silent table rebuild.
36. As an application author on SQLite, I want dropping or rebuilding a table check on an already-live table to warn and skip the same way, so that reconcile never half-applies.
37. As an application author, I want column db_check to remain elided on SQLite (unchanged), so that this ticket does not silently change that emission path.
38. As an application author, I want connect(auto_migrate=True) without migrate_updates to leave an existing table’s CHECKs untouched, so that ADR-0010 still holds: the create pass never ALTERs live tables.
39. As an application author, I want migrate_updates=True to ADD CONSTRAINT a table check that is on the model but missing live, so that a second process can widen Transfer without a manual migration.
40. As an application author, I want that add to fail the connect if existing rows violate the new CHECK, so that I never boot against a schema that cannot represent the data.
41. As an application author, I want changing a check predicate while keeping the suffix to rebuild the constraint (DROP + ADD) under migrate_updates, so that definition drift is not silent.
42. As an application author, I want that rebuild to fail the connect if existing rows violate the new body, so that a tightened invariant cannot be ignored.
43. As an application author, I want a second migrate_updates boot with no model change to be a no-op (no phantom drop+add), so that connect stays cheap and locks stay off.
44. As an application author, I want removing a Check(...) from the model to leave the live ck_* in place under migrate_updates, and to emit a warning that names it, so that leftover enforcement is visible and the destructive ladder is kept.
45. As an application author, I want migrate_destructive=True to DROP CONSTRAINT that orphaned ck_*, so that I can actually remove the invariant in development.
46. As an application author, I want a CHECK I created by hand (name not ck_*) to survive auto-migrate, so that user-owned schema is never altered or dropped.
47. As an application author, I want toggling Field(db_check=True) on an existing closed-domain column to add ck_<table>_<col> under migrate_updates, so that column checks are not stuck on create/add-column only.
48. As an application author, I want changing the allowed labels of a column check to rebuild that ck_* under migrate_updates, so that enum IN (...) drift is not silent.
49. As an application author, I want removing db_check=True from a field to warn under migrate_updates and drop under migrate_destructive, so that column checks and table checks share one ownership rule.
50. As an application author, I want the db_check Field API itself unchanged, so that existing models do not migrate their declaration style.
51. As an application author using Alembic, I want autogenerate to propose ADD CONSTRAINT when the model gained a table check, so that production gets a reviewable revision.
52. As an application author using Alembic, I want autogenerate to propose DROP CONSTRAINT when I removed a table check, even though auto-migrate would not drop it yet, so that the generated revision matches what the model means.
53. As an application author using Alembic, I want autogenerate to propose drop+add when the check predicate drifted, so that body changes are reviewable.
54. As an application author using Alembic, I want autogenerate against a database bootstrapped by connect(auto_migrate=True) to be empty when the model is in sync, so that I do not get phantom CHECK diffs.
55. As an application author using Alembic, I want the autogenerate SQL to be byte-identical to the runtime renderer for the same table check, so that the two doors cannot disagree.
56. As a Ferro maintainer, I want one function to render a check predicate into a CHECK body, so that Alembic and auto-migrate cannot drift (I-1).
57. As a Ferro maintainer, I want live Postgres definitions compared through one normalizer against that canonical body, so that extra parens from pg_get_constraintdef do not cause phantom rebuilds.
58. As a Ferro maintainer, I want tests that pin real pg_get_constraintdef output for IS NULL / OR shapes, so that a Postgres quoting change fails the suite instead of rewriting production constraints.
59. As a Ferro maintainer, I want SchemaIR to carry table checks as structured facts (name + check predicate), not opaque user SQL, so that both emitters render from one artifact.
60. As an application author, I want check predicates compiled at resolved registration, so that shadow *_id columns from ForeignKey exist when the lambda is checked.
61. As an application author, I want __ferro_composite_indexes__ and __ferro_composite_uniques__ unchanged (string tuples), so that this ticket does not rewrite composite declaration.
62. As a documentation reader, I want table-check examples to use lambda predicates with a lowercase-singular parameter, so that they match the official query style.
63. As a documentation reader, I want assignment vs Annotated field tabs unchanged for models that also declare __ferro_checks__, so that the ClassVar appears identically in both tabs.
64. As an application author, I want a misspelled column in a check predicate to name the closest match when possible, so that the error is as useful as where().
65. As an application author, I want create_tables() on a missing table to include table checks, so that the create pass stays “table + columns + indexes + constraints, together.”
66. As an application author, I want adding columns and a table check that references them in one migrate_updates run to order column adds first, so that the CHECK does not run against missing columns.
67. As an application author, I want a failed CHECK add on Postgres to roll back that table’s plan, so that I never observe a half-applied reconcile.
68. As an application author catching IntegrityError, I want CheckViolationError to remain a subclass, so that existing broad handlers still work.

Implementation Decisions

• Public declaration: Check(suffix, predicate) collected as __ferro_checks__: ClassVar[tuple[Check, ...]]. Not raw (name, lambda) tuples, not a dict. Check is a public export next to Field.
• Suffix is an identifier [a-z][a-z0-9_]*, unique per model. Live name is ck_<table>_<suffix> with the same 63-character truncation as column checks (…_ck). Passing a full ck_… name is rejected. Collision with a column check’s full name is a class-definition error. (ADR-0012)
• Check predicate: where() dialect restricted to this table — comparisons, IS NULL / IS NOT NULL, AND/OR/NOT, .in_(), .like(), column-to-column. Values are literals (including enum members), inlined; CHECKs cannot carry bind parameters. Closed-over variables and calls fail at class definition. Traversal, existence tests, and aggregates fail at build time. SQL functions wait for the predicate dialect to grow. (ADR-0012, ADR-0016)
• Forward-FK null tests: both t.rel == None (join-free shadow-FK leaf) and t.rel_id == None. Compiled at resolved registration so shadow columns exist. (ADR-0016)
• SchemaIR: table checks are structured (canonical name + check-predicate tree or equivalent structured expression), not user SQL strings. One renderer in the shared lowering layer produces the CHECK body; both emitters consume it. Same seam as column-check render_check_body. (I-1, ADR-0015)
• Create pass: table checks are inline in CREATE TABLE on both dialects. Existing tables are not touched without migrate_updates (ADR-0010).
• Reconciliation (every ferro-owned ck_*, table checks and column checks): (ADR-0013)
  • Model has it, live doesn’t → ADD CONSTRAINT on migrate_updates. Existing rows must pass or connect fails.
  • Same name, body drifted → constraint rebuild on migrate_updates. Existing rows must pass the new body or connect fails.
  • Live ferro ck_*, model doesn’t → DROP CONSTRAINT only on migrate_destructive. On migrate_updates: warn with the live name, do not drop.
  • Non-ck_* CHECKs: never touched.
• Body-drift comparison: canonical renderer vs pg_get_constraintdef, both through one normalizer (whitespace, wrapping parens, ident quotes). Differ → rebuild; equal → no-op. No COMMENT ON CONSTRAINT hashes. No rebuild-every-connect. Alembic autogenerate consumes the same comparison over FFI. (ADR-0015)
• SQLite: emit on create; reconcile add/rebuild/drop warns with the constraint name and skips. Not a postgres-only schema object. Column db_check SQLite elision is unchanged. (ADR-0014)
• Alembic autogenerate always reports add, body-rebuild, and drop. Auto-migrate gates are connect-time safety; a revision is not applied until reviewed. Parity is the decision and the SQL, not the flag. (ADR-0011, ADR-0013)
• Column db_check Field API does not change. Missing / drifted / removed column checks use the same ck_* pass.
• Runtime errors: existing CheckViolationError / IntegrityError; Postgres SQLSTATE 23514; .constraint is the live name. No new exception type.
• Composites stay string tuples of column names. Selector-lambda sugar for __ferro_composite_indexes__ / __ferro_composite_uniques__ is a separate ticket.
• I-1: add table-check names (ck_<table>_<suffix>) to the cross-emitter naming table next to column checks. Both emitters in the same change. Do not edit CHANGELOG.md by hand (I-10).
• Docs: lambda predicates, lowercase-singular parameter; __ferro_checks__ is identical in assignment and Annotated field tabs (I-7, I-8).

Testing Decisions

A good test asserts what the user can observe: the model declares, connect migrates (or warns), the live catalog has (or lacks) the named CHECK, a violating write raises CheckViolationError, a legal write succeeds, autogenerate is empty or proposes the expected op. Do not assert internal IR field layouts except where I-1 already pins renderer output.

Three existing seams, no new ones:

1. Live DB (primary). Pytest against Postgres and SQLite: Transfer-shaped model; connect + flag ladder; pg_constraint / SQLite CREATE TABLE; violating insert/update → CheckViolationError; leftover warning; SQLite reconcile skip. Build-time errors (bad suffix, traversal, closures, collisions, unknown columns) at this seam as class-definition failures. Prior art: auto-migrate index reconcile, db_check integration, exception mapping, label addition, composite unique unknown-column.
2. Cross-emitter parity (I-1). Alembic metadata vs Rust DDL: names and bodies match; autogenerate against a Rust-bootstrapped in-sync DB is empty; autogenerate reports add / rebuild / drop when the model drifted. Prior art: cross-emitter parity sentinels, db_type check-name parity, Alembic autogenerate.
3. Canonical renderer + normalizer (I-1 decision table). Cargo tests in the shared lowering layer: ck_<table>_<suffix> (including truncation), canonical body SQL, pg_get_constraintdef pins for boolean / IS NULL / OR shapes so quoting changes fail the suite instead of phantom drop+add. Prior art: render_db_check, db_check_constraint_name, missing_enum_labels.

Acceptance from Pinch’s workload (Transfer-shaped, four nullable unique FKs, auto_migrate=True, migrate_updates=True):

• Four CHECKs exist in pg_constraint (contype = 'c') on table transfer.
• Insert with both transaction and activity set on one side → CheckViolationError.
• Insert with activity on both sides and no transaction → CheckViolationError.
• Insert with every side NULL → CheckViolationError.
• Insert with one transaction only (untracked) → succeeds.
• Insert with one transaction + opposite-side activity → succeeds.
• A second process that adds a CHECK to an already-migrated table emits ALTER TABLE … ADD CONSTRAINT; removing it is not silent (migrate_updates warns; migrate_destructive drops).

Out of Scope

• Changing the Field(db_check=True) declaration API (column checks stay; they only join the ck_* reconcile pass).
• Composite uniques/indexes, and selector lambdas for those ClassVars (parked).
• Raw SQL check bodies, or shipping both SQL and lambdas.
• SQL functions in check predicates (char_length, …) until the predicate dialect grows.
• Relation traversal, existence tests, or aggregates inside check predicates.
• NOT VALID / separate validate-later adds; deferrable CHECKs (Postgres CHECKs are not deferrable).
• SQLite table-rebuild on reconcile (Alembic batch mode is the reviewed-rebuild door).
• Pinch-side raw SQL, Alembic-only CHECKs, or a second table to avoid the invariant.
• Renames of tables/columns; those remain Alembic territory.
• Erroring on unknown __ferro_* ClassVars in general (only __ferro_checks__ is consumed).

Further Notes

• Pinch will not work around this with ferro.raw. Ferro-orm#339 is the schema capability; pinch-backend#234 is blocked on it.
• Uniqueness on each activity side is already ForeignKey(unique=True) — out of this spec.
• Table checks are not a postgres-only schema object: SQLite can represent them at create time (unlike materialized views or native enums).
• CheckViolationError already exists; this spec does not introduce a parallel violation type.