fix(hub): the migration chain runs, and a test says so

`add_email_verification` wrote PostgreSQL's `(now() at time zone 'utc')` as a
literal server default, where every other migration in the chain uses
`sa.func.now()` and lets the dialect render it. On SQLite that is
`sqlite3.OperationalError: near "at": syntax error` — so `meshbay-hub migrate`
could not reach head on the database the suite and the documented local-hub
workflow both use.

Which is how it survived: the only test that ran alembic at all stopped at
`c3d4e5f6a7b8`, the revision immediately before it. The two newest migrations —
email verification, and the mail quota committed two days ago — had been run by
exactly one thing, a production deploy, and the newest by nothing at all.

`test_migrations_reach_head.py` upgrades to head and compares what that built
against `Base.metadata`, both directions: a column in the models and in no
migration never reaches production, and one in the migrations and in no model is
a rename abandoned halfway. Both tests fail on the unfixed migration with the
error above; the drift half was checked by adding a model column on purpose and
watching it be named. The schemas agree today.

It still does not check PostgreSQL-only behaviour — a default, an index type or
a constraint one dialect accepts and the other refuses. Running the chain
somewhere beats running it nowhere, and is not the same as running it where it
ships.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UMxEQadpzPkYLFf5CYKhpW

2 files changed, 93 insertions, 1 deletions

diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d4e5f6a7b8c9_add_email_verification.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d4e5f6a7b8c9_add_email_verification.py
index 20ba29d..6741eb8 100644
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d4e5f6a7b8c9_add_email_verification.py
+++ b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d4e5f6a7b8c9_add_email_verification.py
@@ -34,8 +34,14 @@ def upgrade() -> None:
         sa.Column('purpose', sa.String(16), nullable=False),
         sa.Column('user_id', sa.String(36), sa.ForeignKey('users.id'), nullable=True),
         sa.Column('group_id', sa.String(36), sa.ForeignKey('groups.id'), nullable=True),
+        # `sa.func.now()`, as every other migration in this chain uses: the
+        # dialect running it renders it. Written out as PostgreSQL's
+        # `(now() at time zone 'utc')`, this was a syntax error on SQLite — so
+        # the chain could not reach head on the database the suite and the
+        # local-hub workflow both use, and the two newest migrations had been
+        # run by exactly one thing: a production deploy.
         sa.Column('created_at', sa.DateTime(timezone=True),
-                  server_default=sa.text("(now() at time zone 'utc')")),
+                  server_default=sa.func.now()),
         sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
         sa.Column('verified_at', sa.DateTime(timezone=True), nullable=True),
         sa.Column('attempts', sa.Integer, server_default='0'),
diff --git a/packages/meshbay-hub/tests/test_migrations_reach_head.py b/packages/meshbay-hub/tests/test_migrations_reach_head.py
new file mode 100644
index 0000000..3631438
--- /dev/null
+++ b/packages/meshbay-hub/tests/test_migrations_reach_head.py
@@ -0,0 +1,86 @@
+"""
+The migration chain runs, and it builds the schema the models expect.
+
+`create_all()` is not a migration: it creates missing *tables* and never a
+missing *column*, so the schema the suite runs on is not the schema a deployed
+hub has. The symptom is one endpoint answering 500 with an HTML body while
+everything else works, and an `UndefinedColumn` in the journal.
+
+Two things went unnoticed because nothing ran the chain to the end. The only
+test that ran alembic at all stopped at `c3d4e5f6a7b8`, the revision before
+`add_email_verification` — and that one wrote PostgreSQL's
+`(now() at time zone 'utc')` as a literal server default, which is a syntax
+error on SQLite. So the chain could not reach head on the database the suite
+and the local-hub workflow both use, and the two newest migrations had been run
+by exactly one thing: a production deploy.
+
+Hence the two tests here, and they are cheap enough to stay cheap: **upgrade to
+head**, and **compare what that built against `Base.metadata`**. A column added
+to a model and to no migration fails the second one; a migration that cannot run
+fails the first.
+
+What this does *not* check is PostgreSQL-only behaviour — a default, an index
+type or a constraint SQLite accepts and PostgreSQL does not, or the reverse.
+Running the chain somewhere is enormously better than running it nowhere, and it
+is not the same thing as running it where it ships.
+"""
+
+from pathlib import Path
+
+import pytest
+from alembic import command
+from alembic.config import Config
+from sqlalchemy import create_engine, inspect
+
+from meshbay_hub.db.models import Base
+
+HUB = Path(__file__).resolve().parents[1]
+
+
+@pytest.fixture
+def migrated(tmp_path, monkeypatch):
+    """A database built by the migrations alone, at head."""
+    db = tmp_path / "hub.db"
+    monkeypatch.setenv("MESHBAY_DATABASE_URL", f"sqlite+aiosqlite:///{db}")
+    command.upgrade(Config(str(HUB / "alembic.ini")), "head")
+    return db
+
+
+def test_the_chain_reaches_head(migrated):
+    """It ran at all — which is the half that was false."""
+    insp = inspect(create_engine(f"sqlite:///{migrated}"))
+    assert "alembic_version" in insp.get_table_names()
+    # A table from the newest revision, so "head" means head and not "as far as
+    # the last revision anybody happened to run".
+    assert "mail_quota" in insp.get_table_names()
+
+
+def test_the_migrated_schema_is_the_one_the_models_expect(migrated):
+    """Every table and column, both directions.
+
+    Both directions on purpose: a column in the models and in no migration
+    never reaches production, and a column in the migrations and in no model is
+    a write nothing performs and a rename somebody abandoned halfway.
+    """
+    insp = inspect(create_engine(f"sqlite:///{migrated}"))
+    migrated_tables = {
+        name: {c["name"] for c in insp.get_columns(name)}
+        for name in insp.get_table_names() if name != "alembic_version"
+    }
+
+    problems: list[str] = []
+    for name, table in Base.metadata.tables.items():
+        if name not in migrated_tables:
+            problems.append(f"{name}: in the models, no migration creates it")
+            continue
+        expected = {c.name for c in table.columns}
+        for missing in sorted(expected - migrated_tables[name]):
+            problems.append(f"{name}.{missing}: in the models, in no migration")
+        for extra in sorted(migrated_tables[name] - expected):
+            problems.append(f"{name}.{extra}: in the migrations, in no model")
+    for name in sorted(set(migrated_tables) - set(Base.metadata.tables)):
+        problems.append(f"{name}: a migration creates it, no model uses it")
+
+    assert not problems, (
+        "the deployed schema and the tested one have drifted:\n  "
+        + "\n  ".join(problems))