Expand Only

Every deploy runs this against the production Neon database before a single new container takes traffic. It is step two of scripts/box-swap.sh, ahead of the container swap:

docker run --rm -e DATABASE_URL="$DB_URL" "${ECR_REGISTRY}/commscrew/api:${API_TAG}" python -m app_new.migrate

It is a gate. If that command exits nonzero the swap never happens, and the old version keeps serving from the address Caddy is already pointed at.

Alembic sits in backend/requirements.txt and is never used: no alembic.ini, no versions directory, no import anywhere in the backend. The models are the schema, and backend/app_new/migrate.py is the only code that ever changes production's structure. 179 lines, 26 tables.

That is either reasonable or reckless, depending on one property: whether the script is incapable of making a change the currently running code cannot survive.

The deploy is an in-place swap, so the schema has to be backward compatible for a few seconds

One box. One ARM spot instance. New containers come up on alternate ports, get health checked, and only then does Caddy flip. For a short window, the old container and the new container are both alive and both talking to the same database.

That window is the whole reason the migration is expand-only. The old code has to keep working against the new schema, because for a moment it is still the code answering requests. Adding a nullable column satisfies that. Dropping a column does not. Renaming one does not. Changing a type does not.

migrate.py says so in its docstring and then enforces it. create_all handles missing tables; missing columns need a second pass, because create_all will not touch a table that already exists:

    for table in Base.metadata.sorted_tables:
        if table.name not in existing_tables:
            continue  # create_all just made it, so it is already current
        db_columns = {c["name"] for c in inspector.get_columns(table.name)}
        for column in table.columns:
            if column.name in db_columns:
                continue

Anything that survives those guards gets exactly one statement:

            ddl_type = column.type.compile(dialect=dialect)
            stmt = (
                f"ALTER TABLE {preparer.format_table(table)} "
                f"ADD COLUMN {preparer.quote(column.name)} {ddl_type}"
            )
            sync_conn.execute(text(stmt))
            added.append(f"{table.name}.{column.name}")

The entire thing runs inside async with engine.begin() as conn, so create table, add column, convert type, and create index are one transaction. Postgres does transactional DDL, which means a migration that dies halfway leaves the schema exactly as it was. That is what lets the gate be a simple exit code.

A column the migration refuses to add is more useful than one it adds wrongly

There is a guard before the ALTER, and it is the load-bearing part of the design:

            # Only safe, additive changes: an existing row must be able to take
            # the new column without a value.
            if not column.nullable and column.server_default is None:

A NOT NULL column with no default cannot be added to a table that already has rows, because those rows have nothing to put in it. The migration prints a SKIP line naming the table and column and moves on.

The model side of that contract is written at the other end, in backend/app_new/models/content.py, where the campaign strategy stage got a new status column:

    # server_default matters: migrate.py refuses to ALTER-ADD a NOT NULL column
    # without one, because existing rows would have nothing to put in it.
    strategy_status: Mapped[str] = mapped_column(
        String(20), default="none", server_default="none", nullable=False
    )  # none, draft, locked

default="none" is a Python default and only applies to rows this application inserts. server_default="none" is DDL, and it is the half that makes the column addable to a populated table. Two arguments that look redundant, one of which is talking to the migration.

The failure mode this guards against is an ALTER that cannot succeed. Postgres rejects a NOT NULL column with no default on a populated table, and since the whole sync is one transaction, that rejection would take every other change down with it.

Foreign keys are the deletion policy, written once, in the schema

Every child row in this system has an answer to one question: what happens when the thing it points at is deleted. The answer is in the column definition, not in an endpoint.

The rule is a split. Owned children cascade. Optional references null out. From backend/app_new/models/content.py:

    campaign_id: Mapped[uuid.UUID | None] = mapped_column(Uuid(as_uuid=True), ForeignKey("campaigns.id", ondelete="SET NULL"))

    content_item_id: Mapped[uuid.UUID] = mapped_column(Uuid(as_uuid=True), ForeignKey("content_items.id", ondelete="CASCADE"), nullable=False, index=True)

A version snapshot belongs to its content item and is meaningless without it, so it goes. A campaign reference on a content item is context, not ownership, so deleting a campaign should orphan the piece rather than shred it. Eleven foreign keys in the models carry an explicit ondelete today, split between those two intentions.

None of them had one until commit 0bd5fa2. Before that, DELETE /content/{id} raised IntegrityError on Postgres for any item that had ever been published or edited, because PublishedPost, EditSignal, approvals, comments, variants, and versions all pointed at it with no policy at all.

That bug sat there through every local test run, and the reason is the part worth stealing.

Dev has to fail the way production fails, or dev is not a test

SQLite ignores foreign keys. Not "checks them loosely". Ignores them, unless a pragma is set on every connection. So every delete that would raise IntegrityError against Postgres succeeded quietly in development and in the whole test suite.

backend/app_new/database.py sets it on connect:

def _enable_sqlite_foreign_keys(engine: AsyncEngine) -> None:
    """Turn on FK enforcement for SQLite.
    ...
    """
    from sqlalchemy import event

    @event.listens_for(engine.sync_engine, "connect")
    def _set_pragma(dbapi_connection, _connection_record):  # pragma: no cover - driver hook
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON")
        cursor.close()

backend/tests/conftest.py imports that same private function and calls it on the test engine, so the suite runs under the same rules the app does.

Turning it on immediately failed two existing tests that had been writing orphaned rows against fabricated organization IDs, one of them inserting a child before its parent. Those tests had been green for the life of the suite because they were being graded by a database that had opted out of grading.

Then there is a test whose only job is to make sure the grading stays on, in backend/tests/test_delete_cascades.py:

    async def test_sqlite_enforces_foreign_keys(self, client):
        """If this pragma is off, every cascade test below is vacuous."""
        async with TestSessionLocal() as db:
            enabled = (await db.execute(text("PRAGMA foreign_keys"))).scalar()
        assert enabled == 1, "FK enforcement must be on, or dev hides Postgres 500s"

The failure mode is a suite of cascade tests that all pass because nothing is being enforced. A test that cannot fail is indistinguishable from a test that does not exist, and the difference is invisible in a green CI run.

create_all only creates missing tables, which is a sentence with a production incident hiding in it

The retrieval corpus lives in one table, and its embedding column has two storage types depending on dialect. From backend/app_new/models/memory.py:

    embedding: Mapped[list | None] = mapped_column(
        JSON(none_as_null=True).with_variant(Vector(active_dimension()), "postgresql")
    )

On Postgres that is a vector(N) with an HNSW index and similarity computed in SQL. On SQLite it is a JSON float array scored in Python. One attribute, so every read and write site is identical and only the query branches.

The first production deploy of pgvector failed at the migrate gate. CREATE INDEX ... USING hnsw died with a datatype mismatch, because production's org_memory_embeddings table already existed from the old schema with embedding as JSON. create_all only creates missing tables, so the new vector(384) declaration on the model never touched the existing column. The index build then asked for vector_cosine_ops on a JSON column and got told no.

Every environment where that migration had been verified built the table from scratch and got the right type for free. Dev, CI, a fresh Neon branch. The only environment with history was the one that mattered.

A fresh database is a flattering test double for a production one.

The fix is the one place in this migration that is not purely additive, and it is deliberately narrow. It reads the column's real type out of information_schema and returns early if the table does not exist yet or the column is already vector, which is what keeps a second run a no-op. Otherwise it converts in place:

    dim = active_dimension()
    await conn.execute(
        text(
            f"ALTER TABLE org_memory_embeddings "
            f"ALTER COLUMN embedding TYPE vector({dim}) USING NULL::vector({dim})"
        )
    )

USING NULL discards the legacy vectors rather than casting them. That is a decision, not a shortcut. Vectors written before the provider columns existed carry no embedding_provider label, and retrieval filters on that label, so those rows were already invisible to the agents. The durable data is text_content, which is untouched, and the backfill re-embeds from it. The count of discarded values is measured and printed, so the day the choice is not free the log says what it cost. Production held zero rows when the conversion ran, checked before the code was written.

The HNSW index is created here, in the migration, rather than declared as an Index on the model. The model is shared with SQLite, which would choke on USING hnsw.

The test is the environment with history

The guard that came out of that incident does not assert a fact about the migration. It reconstructs the starting conditions the migration failed against. backend/tests/test_migrate_legacy_schema.py opens with the legacy DDL, column for column:

CREATE TABLE org_memory_embeddings (
    id UUID PRIMARY KEY,
    organization_id UUID NOT NULL REFERENCES organizations(id),
    content_type VARCHAR(50) NOT NULL,
    source_id UUID,
    text_content TEXT NOT NULL,
    embedding JSON,
    metadata JSON,
    created_at TIMESTAMP NOT NULL
)

Then it seeds a row the way the old writer wrote them, runs the real migrate.main(), and asserts the column is now vector, the HNSW index exists, the text survived, and the legacy vector is gone. Then it runs migrate.main() again, because an expand-only migration that cannot run twice is not expand-only.

Two details in that DDL are there because a rehearsal caught them. Before shipping the fix, the conversion was run against the actual production database inside a transaction that was rolled back. The rehearsal failed twice, both times in its own seed row: created_at is NOT NULL in production, and organization_id carries a real foreign key. The first draft of the test had gotten both wrong in the same way. The test double was drifting from the thing it doubles, inside the file written to stop test doubles drifting.

The test runs only under Postgres, wired into a CI job that boots pgvector/pgvector:pg16 as a service, runs python -m app_new.migrate against it, then runs four test files with TEST_DATABASE_URL pointed at it. conftest.py picks the dialect off that one variable, defaulting to sqlite+aiosqlite:// when it is unset, so the same suite runs on both backends with no branching in the tests.

That job has caught four dual-environment bugs the SQLite suite could not see.

The timestamps are aware in Python and naive in the database, and only one of those is enforced by a type

Commit e52db8d replaced 41 naive datetime.utcnow() calls and five ad-hoc helpers with three different meanings. The policy in backend/app_new/core/time.py is six numbered rules that reduce to three: store naive UTC, compare aware to aware, convert at the boundary. The write boundary is one function:

def to_db_utc(dt: datetime | None) -> datetime | None:
    """Normalize a datetime for storage in a naive `DateTime` column.
    ...
    """
    if dt is None or dt.tzinfo is None:
        return dt
    return dt.astimezone(UTC).replace(tzinfo=None)

Conversion happens on write because the two backends disagree in the worst possible direction. asyncpg refuses an aware datetime bound to a naive column and raises. SQLite accepts it and stores the wall clock reading, so 23:30+05:30 is written as 23:30. One backend fails loudly in production, the other succeeds wrongly in dev. Three real bugs fell out, including campaign dates parsed from client offsets that stored the wrong instant and passed it to every scheduled piece.

Now the counted part.

There are 47 DateTime columns in these models. Zero of them are DateTime(timezone=True). The word "aware" in this layer describes Python objects and nothing else. Moving those columns to timestamptz is an ALTER COLUMN ... TYPE, which this migration is built to refuse, and an ALTER without USING x AT TIME ZONE 'UTC' would shift every historical row by the session offset. SQLite ignores the flag anyway, so the change would be risky and would not remove the read side normalization.

Which means the guarantee rests on discipline plus one grep test that fails the suite if datetime.utcnow() reappears anywhere in app_new/ outside the module explaining the ban. Sixteen tests hold the policy up, run under TZ=Asia/Kolkata because a half hour offset catches bugs that whole hour offsets hide. It works. It is also a policy enforced by a string search rather than by a column type, and I would rather say that out loud than let "timezone-aware timestamps" imply the database is doing the work.

Where this layer would break

Expand-only means the schema can only grow. Nothing is dropped or renamed, and the only retype is the hand-written JSON to vector special case above, so dead columns accumulate and a bad column name is permanent.

The SKIP path is honest but blunt. The day someone needs a NOT NULL column without a default on a populated table, the migration will log a skip, exit zero, and let the deploy proceed with the column missing. The gate holds against errors, not against refusals.

And the two-dialect split under all of this stays real. Development and CI mostly exercise SQLite while production is Postgres. The pg16 job narrows the gap for the four files it runs. It does not close it.

The one-liner: a migration that can only add is the only kind you can run unattended, and the only way to know it can only add is to make it fail against the schema you already have.

Next in this series: the deploy path this migration is a gate inside, and what it costs to make an in-place swap on a single spot instance safe enough to run from a push.

The views expressed here are my own and are not related to or reflective of my work or any organization I am affiliated with.