Factories

codegen_database factories generate SQLAlchemy tables, views, triggers, functions, and Alembic migrations from declarative configuration. Every factory sits on the same plugin-driven pipeline (Plugin architecture), so ordering rules, singleton conflicts, and extension hooks behave uniformly regardless of which factory you pick.

Four factories ship out of the box:

• Simple – one table, direct CRUD. Best for reference data.

• Append-only (SCD Type 2) – full change history via an append-only attributes log. Best for slowly changing dimensions.

• EAV – sparse attributes stored as rows and pivoted back to columns. Best for highly dynamic or optional fields.

• Ledger – an append-only event stream with running-balance, rollup, and chart helpers. See Ledger tables for the full guide.

All factories support declarative CodegenDatabaseCheck, CodegenDatabaseIndex, and inline CodegenDatabaseForeignKey on columns. See Constraints and indices for a walkthrough with generated SQL.

Simple

A single backing table. Suitable for reference data and simple lookups that don’t need change history.

Example configuration:

users = CodegenDatabaseSimple(
    tablename="users",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("email", String),
    ],
)

Usage:

INSERT INTO public.users (name, email)
VALUES ('Alice', 'alice@example.com');

INSERT INTO public.users (name, email)
VALUES ('Bob', 'bob@example.com');

UPDATE public.users
SET email = 'alice@newdomain.com'
WHERE id = 1;

DELETE FROM public.users
WHERE id = 2;

Append-only (SCD Type 2)

Tracks full change history using an append-only attributes log. Every update creates a new row in the attributes table; the root table points to the latest version. A join view presents the current state. Ideal for slowly changing dimensions where audit trails matter.

Example configuration:

employees = CodegenDatabaseAppendOnly(
    tablename="employees",
    schemaname="private",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("department", String),
    ],
)

Usage – inserts and updates go through the factory view; the triggers manage the internal tables:

-- Operations go through the private.employees view.
-- The triggers manage the root and attributes tables for you.

INSERT INTO private.employees (name, department)
VALUES ('Alice', 'Engineering');

INSERT INTO private.employees (name, department)
VALUES ('Bob', 'Marketing');

-- Alice moves to Management.  This appends a new row to the
-- attributes table rather than updating in place.
UPDATE private.employees
SET department = 'Management'
WHERE id = 1;

EAV (Entity-Attribute-Value)

Stores attributes as rows rather than columns, using typed value columns (string_value, integer_value, etc.) with a check constraint enforcing exactly one non-null value per row. A pivot view reconstructs the familiar columnar layout. Ideal for sparse or highly dynamic attributes where most entities only have a subset of possible fields.

Example configuration:

products = CodegenDatabaseEAV(
    tablename="products",
    schemaname="private",
    metadata=metadata,
    schema_items=[
        Column("color", String),
        Column("weight", Float),
        Column("is_active", Boolean),
        Column("price", Integer),
    ],
)

Usage – the factory view looks like a normal table; the triggers decompose columns into EAV rows behind the scenes:

-- Operations go through the private.products view.
-- The triggers decompose each column into attribute rows
-- in the underlying EAV tables.

INSERT INTO private.products (color, weight, is_active, price)
VALUES ('red', 2.5, TRUE, 999);

INSERT INTO private.products (color, weight, is_active, price)
VALUES ('blue', 1.0, TRUE, 499);

-- The pivot view reconstructs columns, so SELECTs look normal:
SELECT * FROM private.products;

Ledger

CodegenDatabaseLedger generates an append-only event stream designed for inventory, double-entry accounting, audit trails, and any other append-only delta model. On top of the event stream codegen_database ships a toolbox of query and function builders: per-period rollups, running-balance windows, gap-filled period axes, rollup charts, and a double-entry specialization that normalizes deltas by each account’s normal_side.

The chart helpers depend on Chart extension for the codegen_database_date_bin polyfill.

See Ledger tables for the full walkthrough with worked examples, and Ledger events for attaching named PostgreSQL functions to a ledger.