Constraints and indices

codegen_database dimensions are defined with schema_items — a list of SQLAlchemy Column objects mixed with codegen_database constraint and index definitions. All three use {column_name} markers to reference columns, and all three are validated against the actual table columns at factory time.

Class	Purpose	Import
CodegenDatabaseCheck	CHECK constraints	from codegen_database.check import CodegenDatabaseCheck
CodegenDatabaseIndex	Indices (btree, GIN, unique, functional, …)	from codegen_database.index import CodegenDatabaseIndex
CodegenDatabaseForeignKey	Foreign key constraints (inline, single-column)	from codegen_database.fk import CodegenDatabaseForeignKey

Column markers

Check and index classes reference columns with {column_name} markers. At factory time, codegen_database validates that every referenced column exists on the target table and substitutes the markers with real column references.

# Check: the expression is a SQL predicate
CodegenDatabaseCheck("{price} > 0", name="positive_price")

# Index: each argument is an expression
CodegenDatabaseIndex("idx_name", "{name}")
CodegenDatabaseIndex("idx_lower", "lower({name})")

# FK: pass inline to Column, reference is "dimension.column"
Column("customer_id", Integer, CodegenDatabaseForeignKey("customers.id"))

If a marker names a column that does not exist on the table, codegen_database raises CodegenDatabaseValidationError at factory time — not at migration time or at runtime.

Check constraints

CodegenDatabaseCheck defines a SQL CHECK constraint. It takes an expression and a name.

CodegenDatabaseCheck("{price} > 0", name="positive_price")
CodegenDatabaseCheck(
    "{end_date} > {start_date}",
    name="valid_date_range",
)

For simple and append-only dimensions, this becomes a real CHECK constraint on the table. For EAV dimensions, it becomes a trigger function that validates NEW.price > 0 before the main EAV triggers process the row. The same CodegenDatabaseCheck definition works on all dimension types — codegen_database picks the right enforcement strategy automatically.

Indices

CodegenDatabaseIndex mirrors the sqlalchemy.Index constructor: name first, then column expressions, then keyword arguments passed through to the underlying index.

# Simple index
CodegenDatabaseIndex("idx_products_sku", "{sku}")

# Unique index
CodegenDatabaseIndex("uq_products_name", "{name}", unique=True)

# Functional index with dialect kwargs
CodegenDatabaseIndex(
    "idx_lower_name", "lower({name})",
    postgresql_using="btree",
)

# Multi-column index
CodegenDatabaseIndex("idx_name_price", "{name}", "{price}")

CodegenDatabaseIndex supports the same keyword arguments as sqlalchemy.Index:

Keyword	Effect
unique=True	Creates a UNIQUE index
postgresql_using="gin"	Uses the GIN index method
postgresql_where=text("active")	Partial index (WHERE active)
postgresql_ops={"data": "jsonb_path_ops"}	Operator class for a column

Foreign keys

CodegenDatabaseForeignKey defines a single-column foreign key inline on the Column constructor — analogous to SQLAlchemy’s ForeignKey.

The reference string accepts two formats:

Format	Example	When to use
"dimension.column"	"customers.id"	Target is a codegen_database dimension. Resolved via the dimension registry at factory time.
"schema.table.column"	"public.orgs.id"	Target is outside codegen_database, or you want full control. Passed through to SQLAlchemy as-is.

# Resolved — codegen_database finds the physical table
Column(
    "customer_id", Integer,
    CodegenDatabaseForeignKey("customers.id", ondelete="CASCADE"),
)

# Raw — passed through directly
Column(
    "org_id", Integer,
    CodegenDatabaseForeignKey("tenant.orgs.id"),
)

Cascade options

Both ondelete and onupdate accept any PostgreSQL action: CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION (the default).

Column(
    "customer_id", Integer,
    CodegenDatabaseForeignKey(
        "customers.id",
        ondelete="CASCADE",
        onupdate="SET NULL",
    ),
)

Simple dimension example

customers = CodegenDatabaseSimple(
    tablename="customers",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("email", String, nullable=False),
        CodegenDatabaseIndex("uq_customers_email", "{email}", unique=True),
    ],
)

orders = CodegenDatabaseSimple(
    tablename="orders",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column(
            "customer_id",
            Integer,
            CodegenDatabaseForeignKey("customers.id", ondelete="CASCADE"),
            nullable=False,
        ),
        Column("total", Numeric(10, 2), nullable=False),
        Column("status", String, nullable=False),
        CodegenDatabaseCheck("{total} > 0", name="positive_total"),
        CodegenDatabaseCheck(
            "{status} IN ('pending', 'paid', 'cancelled')",
            name="valid_status",
        ),
        CodegenDatabaseIndex("idx_orders_customer_id", "{customer_id}"),
        CodegenDatabaseIndex("idx_orders_status", "{status}"),
    ],
)

Append-only dimension example

Constraints and indices on append-only dimensions are placed on the attributes table. Foreign keys that target an append-only dimension resolve to the root table — the stable primary key.

departments = CodegenDatabaseSimple(
    tablename="departments",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        CodegenDatabaseIndex("uq_departments_name", "{name}", unique=True),
    ],
)

employees = CodegenDatabaseAppendOnly(
    tablename="employees",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("salary", Integer, nullable=False),
        Column(
            "department_id",
            Integer,
            CodegenDatabaseForeignKey("departments.id"),
            nullable=False,
        ),
        CodegenDatabaseCheck("{salary} > 0", name="positive_salary"),
        CodegenDatabaseIndex(
            "idx_employees_department_id",
            "{department_id}",
        ),
    ],
)

EAV dimension example

EAV dimensions store attributes as rows, not columns. Table-level CHECK constraints cannot reference virtual columns, so codegen_database enforces checks via INSTEAD OF trigger functions instead. The same CodegenDatabaseCheck syntax works — the enforcement mechanism is chosen automatically.

products = CodegenDatabaseEAV(
    tablename="products",
    schemaname="private",
    metadata=metadata,
    schema_items=[
        Column("color", String),
        Column("weight", Float),
        Column("price", Integer),
        CodegenDatabaseCheck("{price} > 0", name="positive_price"),
        CodegenDatabaseCheck("{weight} > 0", name="positive_weight"),
    ],
)