Emitting DDL from Django without migrations — a table per form

Every form builder starts with a JSONB column. It is the right first decision: one table, submissions, with data jsonb, and the shape of the form lives in the form definition rather than in the schema. You ship in a week.

The bill arrives later, and it arrives as a list:

No type at rest. A date is a string until someone parses it, and the day one respondent’s phone sends 03/04/2026, nobody can tell you which April it was.
No constraints. NOT NULL, UNIQUE, foreign keys — none of it exists, so the invariant lives in application code and holds until the second writer.
Indexes are possible and unpleasant. A GIN index on the whole document, or an expression index per key that you have to create by hand for each of forty fields on each of two hundred forms.
No spatial types. A GPS point stored as {"lat": …, "lng": …} cannot be handed to PostGIS, which means no radius query, no polygon containment, no index.
Nobody can plug in a BI tool. “Point Metabase at it” turns into a view full of data->>'field_7' that breaks when a field is renamed.

So PaperPoint grew a second mode: a published form becomes a real PostgreSQL table, with real types, real indexes and real history. The registry that describes those tables is ordinary Django. The tables themselves never touch a migration file.

This is how that is built, and where the sharp edges are.

Two levels, and only one of them is Django’s

The whole design is in the module docstring:

"""SchemaManager — materializes DynamicTable rows as real PostgreSQL tables.

This is the only code allowed to emit DDL for user-level dynamic tables. The
Django ORM is used for the `dynforms` registry (DynamicTable, DynamicColumn,
DynamicTableVersion) but NOT for the tables it describes: those are created
and altered via `connection.schema_editor()` + raw SQL so we bypass Django's
migration pipeline entirely (H7 — versioning is additive, controlled by
`DynamicTableVersion`, not `django_migrations`).
"""

Read the second sentence twice. There are two schemas in this database and they are governed differently:

The registry — DynamicTable, DynamicColumn, DynamicTableVersion — is normal Django. It has migrations, it is in INSTALLED_APPS, it ships with the code.
The tables the registry describes are created at runtime, by customers, from a browser. Django has never heard of them and must never hear of them.

That separation is not stylistic. makemigrations inspects INSTALLED_APPS and compares model state to migration state. If a customer’s table were an ORM model, then a customer publishing a form at 3pm on a Tuesday would change the application’s model graph — and makemigrations on a developer’s laptop would try to generate a migration for a table that exists only in one customer’s database. There is no version of that which ends well.

So the rule is written as an enforcement, not a convention: one module is allowed to emit DDL. Everything else goes through it. That sentence is worth more than the code under it, because the failure mode of dynamic DDL is not a bug — it is a second place that also emits DDL, six months later, which does not know about the history trigger.

Every table gets the same eleven columns

SYSTEM_COLUMNS_DDL: list[str] = [
    "id uuid PRIMARY KEY DEFAULT gen_random_uuid()",
    "_created_at timestamptz NOT NULL DEFAULT now()",
    "_updated_at timestamptz NOT NULL DEFAULT now()",
    '_created_by_id bigint REFERENCES "user"(id) ON DELETE SET NULL',
    '_updated_by_id bigint REFERENCES "user"(id) ON DELETE SET NULL',
    "_is_active boolean NOT NULL DEFAULT true",
    "_schema_version integer NOT NULL",
    "_langue_saisie varchar(5) NOT NULL DEFAULT ''",
    "_embedding halfvec(1024)",
    "_embedding_updated timestamptz",
    "_source_submission_id bigint",
]

The underscore prefix is doing real work: it partitions the namespace. A customer naming a field created_at collides with nothing, because the system column is _created_at. Without the convention, every new system column is a potential collision with a field somebody already created, and you find out at CREATE TABLE time on their data.

Four of these deserve their own note.

_schema_version integer NOT NULL — on the row, not just the table. Rows written under version 3 keep saying 3 after the table reaches version 7. When a reader hits a null in a column added at version 5, that row’s version says whether it means the user left it blank or this column did not exist yet. Those are different facts and no other mechanism can tell them apart.

_is_active boolean — soft delete, with the index below to make it free.

_embedding halfvec(1024) — every materialised table is semantically searchable from day one, because retrofitting a vector column onto two hundred customer tables afterwards is two hundred ALTERs and a backfill. halfvec is pgvector’s 16-bit float variant: half the storage and half the index memory of vector, with a precision loss that does not move recall at 1024 dimensions.

_source_submission_id bigint — and the comment next to it is the most honest thing in the file:

# `_source_submission_id` stores the legacy FormSubmission.id for the
# coexistence phase (H10). FormSubmission.pk is BigAutoField, not UUID —
# the v3 doc spec said `uuid` but the live data says otherwise, so we
# follow the code.

The design document said uuid. The database said bigint. The code follows the database and says so in a comment. That is the correct resolution, and writing down that there was a disagreement is what stops someone from “fixing” it back to uuid next year on the authority of a document.

The same discipline appears above:

# `_created_by_id` / `_updated_by_id` are bigint because `users.CustomUser`
# still uses Django's default BigAutoField PK — the user model predates the
# UUID default on BaseModel. When CustomUser migrates to UUID these two
# columns must follow.

A known inconsistency, its cause, and the exact work its resolution implies. This is what a comment is for. A codebase where every type is uniform because someone enforced uniformity is usually a codebase where a migration was skipped quietly.

Types come from metadata, not from a table alone

Most field types map straight through:

FIELD_TYPE_TO_PG_TYPE: dict[str, str] = {
    "char": "text",
    "date": "date",
    "datetime": "timestamptz",
    "boolean": "boolean",
    "multiple_choice": "text[]",
    "file": "uuid",
    "files": "uuid[]",
    "gps_coordinates": "geometry(Point, 4326)",
    "geo_polygon": "geometry(Polygon, 4326)",
    ...
}

Three choices in that table are worth arguing about.

timestamptz, never timestamp. A form filled in Bamako and read in Geneva has one correct instant and two correct wall-clock readings. timestamp without time zone stores the reading and loses the instant. There is no case in an application with users in more than one place where the naive type is right.

multiple_choice → text[], not a join table. A normalised model would give each selection a row. For a checkbox group whose options are defined by the same customer who defined the field, an array is the honest shape: the values have no identity outside this field, they are never referenced, and @> with a GIN index answers “who ticked X” perfectly well. Normalise things that have an identity; this does not.

gps_coordinates → geometry(Point, 4326). The whole reason for the exercise. This column can be indexed with GiST, filtered with ST_DWithin, and joined against a polygon. Two float columns cannot do any of that, and a JSON object can do less.

And two types cannot be decided from the field type alone:

def resolve_pg_type(field_type: str, *, metadata: dict | None = None) -> str:
    metadata = metadata or {}

    if field_type == "char":
        n = metadata.get("max_length")
        return f"varchar({n})" if n else "text"

    if field_type == "number":
        decimals = metadata.get("decimals")
        if decimals:
            precision = metadata.get("precision", 18)
            return f"numeric({precision},{decimals})"
        return "integer"

    try:
        return FIELD_TYPE_TO_PG_TYPE[field_type]
    except KeyError as exc:
        raise SchemaManagerError(f"Unknown field_type: {field_type!r}") from exc

numeric(p,s) and not float for a decimal number, because someone will put money in it. integer when no decimals are configured — narrower than bigint, and narrowing later is impossible while widening is one ALTER.

And the KeyError becomes a SchemaManagerError naming the type. An unknown field type must stop the publication, not silently drop a column. A form published with a missing column is a form collecting data into nowhere, and nobody notices until the export.

Additive only, and the code refuses to be anything else

This is the constraint that makes runtime DDL survivable.

@transaction.atomic
def apply_change(dynamic_table, change: AddColumnChange, *, applied_by=None) -> None:
    """Apply an additive schema change; bumps `schema_version`."""
    if not isinstance(change, AddColumnChange):
        raise SchemaManagerError(
            f"Unsupported change type: {type(change).__name__}. "
            "Only AddColumnChange is implemented in the P2 MVP."
        )

There is exactly one change type, and it adds a column. There is no DropColumnChange, no AlterTypeChange, no RenameChange — and the type check is at the top of the function so that adding one is a deliberate act with a name, not an accident of passing a different object.

The reason is not caution for its own sake. It is that the destructive operations are the ones you cannot undo from a customer’s browser:

Dropping a column destroys data with no confirmation that a person capable of understanding the consequence has seen it. The customer thinks they are tidying up a form.
Changing a type rewrites the whole table under an ACCESS EXCLUSIVE lock. On a table with four million inspection rows, that is minutes of a hard lock on a live system, triggered by a dropdown.
Renaming breaks every saved report, export and integration that referenced the old name.

So a removed field stops being written, its column stays, and the physical schema only ever grows. Storage is cheap. A customer who cannot recover last year’s answers is not.

The @transaction.atomic on both entry points matters more here than usual: PostgreSQL has transactional DDL. CREATE TABLE, ALTER TABLE, CREATE INDEX and CREATE TRIGGER all roll back. So the failure mode that plagues MySQL setups — a table created, an index missing, a trigger not attached, and a registry row that says everything is fine — cannot happen. The DDL and the registry row commit together or not at all.

The migration history is data

DynamicTableVersion.objects.create(
    table=dynamic_table,
    version=dynamic_table.schema_version,
    applied_by=applied_by,
    diff_json={
        "op": "add_column",
        "name": change.physical_name,
        "pg_type": change.pg_type,
        "nullable": change.is_nullable,
        "default": change.default_expr,
    },
)

Django keeps its schema history in files, applied in order, recorded in django_migrations. This subsystem keeps its history in rows: what changed, in which version, by whom (applied_by), and when.

applied_by is the column that files cannot have. A schema change made by a customer at 15:42 has an author, and the question “who added this column and when” is a support question that gets asked. In a migration file the answer is in git, which the support team does not have.

diff_json rather than the DDL string is a deliberate choice too. The statement is a rendering of the intent; the intent is what you want when you replay a table’s construction, compare two customers’ tables, or generate a change log a human reads. Storing the SQL means storing one execution of one code version.

Indexes, and the one that is easy to forget

def _system_index_ddl(table: str) -> list[str]:
    return [
        f'CREATE INDEX {_ident(f"{table}__created_at_idx")} ON {_ident(table)} (_created_at);',
        f'CREATE INDEX {_ident(f"{table}__is_active_idx")} ON {_ident(table)} (_is_active) '
        f"WHERE _is_active = true;",
        f'CREATE INDEX {_ident(f"{table}__embedding_hnsw")} ON {_ident(table)} '
        f"USING hnsw (_embedding halfvec_cosine_ops) WITH (m=16, ef_construction=64);",
    ]

The partial index on _is_active. A plain index on a boolean is close to useless — two distinct values, so the planner reads the table anyway. WHERE _is_active = true builds an index containing only the live rows, which is both the rows every query wants and a fraction of the table once soft deletes accumulate. Partial indexes are the single most underused feature in PostgreSQL and this is their canonical case.

HNSW with m=16, ef_construction=64 — pgvector’s defaults, kept because they are a reasonable point on the build-time/recall curve and because a customer publishing a form should not wait on index tuning. The parameter to raise later, if recall disappoints, is ef_search at query time; it needs no rebuild.

And then the case that is genuinely easy to get wrong:

with connection.cursor() as cur:
    cur.execute(ddl)
    # A geometry column added after table creation also needs its GiST
    # index, otherwise spatial queries on it seq-scan (E4).
    if _is_geometry_type(change.pg_type):
        cur.execute(_gist_index_ddl(dynamic_table.physical_name, change.physical_name))

At CREATE TABLE, geometry columns get their GiST index from _user_index_ddl. A geometry column added later, via apply_change, goes down a different code path — and if that path does not repeat the rule, the column works, the queries return correct results, and they sequentially scan forever. Nothing is broken. It is just slow, and it gets slower proportionally to success.

This is the general shape of the bug worth watching for: a rule applied in the create path and forgotten in the alter path. The two paths are written months apart, and only one of them is exercised by the tests you wrote on day one.

Note also that geometry columns are indexed unconditionally, while ordinary columns are indexed only when col.is_indexed:

if _is_geometry_type(col.pg_type):
    out.append(_gist_index_ddl(table, col.physical_name))
elif col.is_indexed:
    out.append(...)

A btree on a geometry column is not merely suboptimal, it is meaningless — it orders geometries by their internal representation. There is no configuration in which a customer wants that. When only one answer is correct, the setting should not be offered.

The identifier problem

Runtime DDL means building SQL by string concatenation, which means the question has to be answered explicitly rather than left to the driver — parameter binding does not apply to identifiers.

def _ident(name: str) -> str:
    """Double-quote a PG identifier. Input is trusted (slug-validated upstream)."""
    if '"' in name or "\x00" in name:
        raise SchemaManagerError(f"Refusing identifier with suspicious chars: {name!r}")
    return f'"{name}"'

Two layers, and both are needed.

Upstream, physical_name is a SlugField(max_length=63) on both DynamicTable and DynamicColumn — letters, digits, hyphens, underscores. 63 is PostgreSQL’s NAMEDATALEN - 1, and it is a hard cap rather than a suggestion: over it, PostgreSQL truncates silently, so two forms whose slugs differ at character 70 would materialise into the same table.

Here, the value is double-quoted and rejected if it contains a quote or a NUL. Quoting alone is what makes a hyphenated slug legal as an identifier at all; the rejection is the second lock, for the day someone writes a code path that sets physical_name without going through a form — and SlugField’s validator, like every Django field validator, only runs in full_clean(), not in save().

That is the honest description of the trust chain: a whitelist that is enforced by convention, plus a guard that is enforced by code. If you build this, the guard is the one you cannot skip.

One residual worth naming, since the file does not: _column_ddl interpolates col.default_expr directly.

default_clause = f" DEFAULT {col.default_expr}" if col.default_expr else ""

That is arbitrary SQL by design — a column default has to be able to be now() or gen_random_uuid(), and there is no way to express that as a bound parameter. Today nothing in the codebase writes default_expr from a request path; it exists on the model and is only ever set from code. That property is the security boundary, and it is invisible from the file that depends on it. If a “default value” box ever appears in the form builder’s UI, it must not be wired to this column — it needs an allow-list of expressions, or a literal that is quoted as a literal.

What to take away

JSONB first is right; a table per form is what you graduate to. Types, constraints, indexes, PostGIS and BI tools are the list of things you buy back.
Keep runtime DDL out of INSTALLED_APPS. A customer must not be able to change your application’s model graph.
Give every generated table the same underscore-prefixed system columns, and put _schema_version on the row so a null can be dated.
Make the change vocabulary additive and typed. One AddColumnChange, and a type check that makes adding a destructive operation a deliberate act.
Store the schema history as rows with an author. Files cannot tell you who.
Repeat every index rule in the alter path. The forgotten GiST index does not break anything — it just seq-scans forever.
Quote identifiers and validate them, in two independent places. Field validators do not run on save().

Materialising a form as a real PostgreSQL table