Migrations¶

"Migration" means several different things in the Dataland stack, and this page covers all of them:

Kind	Owner	Lives in	Run by
DB schema (Postgres DDL)	`dataland-agent`	`migrations/versions/` (Alembic)	explicit operator step
Data backfills (one-shot SQL)	`dataland-agent`	`migrations/data/`	by hand, with approval
Model config (Gemini id)	agent + rag + infra	per-repo `.env` + code defaults	push to `main`
Env consolidation	`dataland-infrastructure`	per-repo `.env` + `env-inventory.md`	guarded by `check-env-drift.sh`
Vector re-ingest (Qdrant)	knowledge → rag	`images` / `knowledge` collections	replace-by-slug sync

Everything runs on the single Spark host, so a "migration" here is a change to on-disk schema, data, or vectors — not a service relocation.

1. Database schema migrations (Alembic — `dataland-agent`)¶

The agent owns the only relational schema that evolves over time (the agent tables in the on-host dataland Postgres database). Schema changes are managed with Alembic. RAG vectors live in Qdrant and museum/RDC state lives in Redis — neither uses Alembic. The auth server's auth_server_users table also lives in the same on-host dataland database, but it is created idempotently by auth_server.py, not by the agent's Alembic chain.

Two flavours of Postgres change live in dataland-agent/migrations/:

migrations/versions/ — Alembic-managed schema migrations. Each file is a Python module with upgrade() and downgrade().
migrations/data/ — one-shot SQL data backfills run by hand against production (see §2).

Configuration¶

alembic.ini (repo root) wires the migration runner:

[alembic]
script_location = migrations
file_template = %%(rev)s_%%(slug)s     # (1)!
truncate_slug_length = 60
timezone = UTC
sqlalchemy.url = postgresql://dataland:dataland@localhost:5432/dataland  # (2)!

The %% is a literal % escaped for ConfigParser. This template yields the strictly-linear four-digit-prefix filenames like 0010_user_profile_fields.py, so ls migrations/versions sorts in deploy order.
Placeholder only — never the real DB. The actual DB URL comes from $DATABASE_URL at runtime (see below); this static value just keeps the runner importable when no env is set.

The real DB URL comes from $DATABASE_URL at runtime; migrations/env.py strips the async-driver suffix so the same URL the application uses works for migrations too:

Application URL	Rewritten for Alembic (sync)
`postgresql+asyncpg://…`	`postgresql+psycopg2://…`
`sqlite+aiosqlite:///…`	`sqlite:///…`
bare `postgresql://…`	`postgresql+psycopg2://…`

env.py also wires target_metadata = Base.metadata (from app.models) so alembic revision --autogenerate diffs against the same models the app uses at runtime, with compare_type=True and compare_server_default=True.

Idempotent, dialect-aware migrations

Every revision is written to be idempotent and dual-dialect (Postgres and SQLite). Production is Postgres; the test fixture builds a fresh SQLite schema from Base.metadata.create_all. Migrations therefore inspect the live schema (sa.inspect(bind)) and skip ops that are already satisfied — e.g. 0005 only drops messages.event if the column still exists; 0006 skips the varchar → timestamptz cast if the column is already a DateTime. This is why the round-trip test (upgrade → downgrade → upgrade) passes on a freshly-create_all'd DB even though production took a different path to the same shape.

Migration chain (current head: `0010`)¶

graph LR
  B["0001<br/>baseline"] --> U["0002<br/>utm active uniq"]
  U --> M["0003<br/>messages conv_seq uniq"]
  M --> E["0004<br/>users LOWER(email) uniq"]
  E --> D["0005<br/>drop messages.event"]
  D --> T1["0006<br/>messages.created_at → tz"]
  T1 --> T2["0007<br/>lift timestamps → tz"]
  T2 --> TK["0008<br/>tickets table"]
  TK --> R["0009<br/>runs table"]
  R --> P["0010<br/>user profile fields"]

Rev	File	Purpose
0001	`0001_baseline.py`	No-op. Establishes the Alembic head; the pre-Alembic schema (built by `create_all`) is stamped to this.
0002	`0002_utm_active_uniq.py`	Partial unique index `ix_utm_ticket_active` on `user_ticket_mappings(ticket_id) WHERE active`. Guarded — no-op on the simplified schema that no longer has the table.
0003	`0003_messages_conv_seq_uniq.py`	Composite unique `ix_messages_conv_seq` on `messages(conversation_id, seq)`; also a covering index for the canonical read pattern.
0004	`0004_users_email_lower_uniq.py`	Case-insensitive partial unique `ix_users_email_lower` on `users(LOWER(email)) WHERE email IS NOT NULL`. Raw SQL (cross-dialect functional index).
0005	`0005_drop_messages_event.py`	Drop the dead `messages.event` column (NULL on 100% of 5949 rows).
0006	`0006_messages_created_at_tz.py`	`messages.created_at` `varchar → timestamptz` (all 5949 rows parse as ISO-8601).
0007	`0007_lift_timestamps_to_tz.py`	Lift the remaining 5 timestamp columns (`users`, `conversations`, `user_ticket_mappings`) to `timestamptz`, interpreting existing values as UTC.
0008	`0008_tickets_table.py`	Create `tickets` (per-ticket state) + backfill `first_seen`/`last_seen`/`visit_count` from `user_ticket_mappings`. Additive.
0009	`0009_runs_table.py`	Create `runs` (first-class run entity: model, tokens, cost, status) + best-effort backfill from `messages` (2918 distinct `run_id`). No hard FK yet.
0010	`0010_user_profile_fields.py`	Add nullable `users` profile columns from the museum-wide JWT: `full_name`, `location`, `profile_photo_url`, `joined_date`, `access_permissions`, `stripe_customer_id`.

The baseline + _ensure_schema_migrations retirement

0001_baseline.py is intentionally empty. The schema that existed at adoption-time predates Alembic — it was built by Base.metadata.create_all in app/db/session.py::init_db(). The ad-hoc _ensure_schema_migrations DDL that used to run alongside create_all was then retired (every operation it performed had long since landed on every deploy). Today, init_db() still calls create_all so a freshly-spun environment boots with a working schema, but schema evolution from here lives in migrations/versions/ exclusively.

Running migrations¶

The application does not run alembic upgrade head on startup. Migrations are an explicit operator step so deploys stay predictable and rollbacks stay simple.

Environment	Command
Local mirror	`DATABASE_URL=postgresql://dataland:dataland@localhost:15432/dataland uv run alembic upgrade head`
Production	`ssh ege@<host> 'scripts/dl agent uv run alembic upgrade head'`

First deploy of Alembic (one-time only). The version table must be created and stamped to baseline before any subsequent migration applies:

ssh ege@<host> 'scripts/dl agent uv run alembic stamp head'  # (1)!

stamp writes the version row without running any migration SQL. It tells Alembic the existing (pre-Alembic, create_all-built) schema already matches the head revision. Run this exactly once on first adoption; running upgrade instead here would try to re-apply migrations against an already-current schema.

After that, every future migration deploy is just alembic upgrade head.

Authoring a new migration¶

# From the dataland-agent repo, DATABASE_URL pointed at the local mirror
DATABASE_URL=postgresql://dataland:dataland@localhost:15432/dataland \
  uv run alembic revision --autogenerate -m "short_description"  # (1)!

--autogenerate diffs Base.metadata against the live mirror schema to draft the migration. It is a starting point, not the final artifact — autogen misses functional/partial indexes, data backfills, and dialect quirks, so the next step (hand-edit + round-trip) is mandatory.

Then always hand-edit before committing, and verify the round-trip:

DATABASE_URL=... uv run alembic upgrade head
DATABASE_URL=... uv run alembic downgrade -1
DATABASE_URL=... uv run alembic upgrade head   # (1)!

The second upgrade must converge to the same schema as the first. This proves downgrade() is a true inverse and the revision is idempotent — a revision that fails this round-trip is not safe to ship.

Two open PRs, one revision line

Revision ids are strictly linear four-digit prefixes (%%(rev)s_%%(slug)s) so ls migrations/versions sorts in deploy order. Two PRs each adding a migration will collide on the next id. Coordinate the next number before branching, and never edit a migration that has already been applied to any environment — write a follow-up instead.

2. Data backfills (`migrations/data/`)¶

One-shot SQL backfills live outside Alembic because backfill SQL is per-row, slow on large tables, and benefits from decision-log review before commit. Every file is idempotent, wrapped in BEGIN; … COMMIT;, ends with a post-check SELECT COUNT(*), and includes a DO $$ … RAISE EXCEPTION $$ guard that aborts the transaction if the post-state is out of bounds.

#	File	Purpose	Result on mirror
0001	`0001_backfill_null_conversation_ids.sql`	Fill `user_ticket_mappings.conversation_id` on 62/70 legacy NULL rows (newest in-window conversation, 7-day grace).	62 updated; 0 NULL after; guard asserts < 5% NULL.
0002	`0002_backfill_null_conversation_mode.sql`	Fill `conversations.mode` on 290/2638 NULL rows (owner's most-used mode, else `museum`).	290 updated (184 owner-pref, 106 default); guard asserts 0 NULL after.

The runbook for each backfill (restore the local mirror, run there first, take a fresh production pg_dump parachute, run on production with explicit user approval, re-run the audit queries) is in dataland-agent/migrations/README.md.

Backfills are not reversible by script

Data migrations keep no undo log. The rollback path is the point-in-time pg_dump taken immediately before the run. Always take it; always run on the local mirror first.

3. Gemini model configuration¶

The stack is standardized on gemini-3.5-flash for all generative work (chat + Gemini captioning + RAG reranking). Vector embeddings use a separate model. Model ids are config, read from each repo's .env at boot:

Repo	Setting	Value	Where
`dataland-agent`	`agent_model`	`google-gla:gemini-3.5-flash`	`app/config.py`, `AGENT_MODEL`
`dataland-agent`	`gemini_model`	`gemini-3.5-flash`	`app/config.py`, `GEMINI_MODEL`
`dataland-rag`	`gemini_model`	`gemini-3.5-flash`	`config.py`, `GEMINI_MODEL` (captioning + rerank fallback + kreuzberg VLM)
`dataland-infrastructure`	`AGENT_MODEL` / `GEMINI_MODEL`	`gemini-3.5-flash`	`.env.example`, propagated by compose

Changing the id is a config-only change — no schema, no data. Set the value and redeploy the agent + rag images. The agent's boot guard (app/runtime.py) refuses to start if AGENT_MODEL uses the google-gla: provider but GEMINI_API_KEY is empty.

Embeddings are separate

EMBEDDING_MODEL (gemini-embedding family) is the vector model for the Qdrant collections. Changing it would require re-embedding every point — a far larger operation than an id swap. See RAG.

4. Environment configuration (per-repo `.env`)¶

Each service loads its own dataland-<repo>/.env via the compose env_file: directive. The root /home/cobanov/DATALAND/.env is not injected into the services — it exists only as the compose --env-file, used for ${…} interpolation of things like image tags, host ports, and *_PUBLIC_BIND values inside compose.yml.

Source-of-truth hierarchy¶

1. dataland-<service>/.env             ← actual per-service deploy values (gitignored)
2. /home/cobanov/DATALAND/.env         ← compose --env-file (interpolation only)
3. dataland-infrastructure/.env.example ← canonical template (git). New vars start here.
4. dataland-<service>/.env.example     ← per-repo local-dev template (a subset)

Operational rule: every variable a deployed service reads must appear in the infra template. A service-repo var that is absent from both the infra template and the explicit service-local-only allowlist is treated as drift.

Adding a new variable (the workflow)¶

Add it to dataland-infrastructure/.env.example (with an ownership comment).
Wire it into the relevant service's env_file / environment: block in compose.yml.
Mirror it in the owning service repo's .env.example for local-dev parity.
Run bash scripts/check-env-drift.sh — exit 0 means done.

flowchart LR
  A["new var in service<br/>.env.example"] --> C{"in infra<br/>.env.example?"}
  C -->|yes| OK["check-env-drift.sh → exit 0"]
  C -->|no| D{"in SERVICE_LOCAL<br/>allowlist?"}
  D -->|yes| OK
  D -->|no| FAIL["exit 1 → smoke/CI fails<br/>follow-up PR required"]

The drift guardrail (scripts/check-env-drift.sh) extracts ^[A-Z][A-Z0-9_]*= keys from each service .env.example, subtracts the infra template, and fails on anything left that isn't in the SERVICE_LOCAL allowlist (per-developer tuning knobs like CHUNK_SIZE, RERANKER_MODEL, the notification EXPLORER_* vars). The full ownership map is documented in docs/env-inventory.md.

Deploy-time boot guard¶

deploy.sh fails fast before rebuilding if the agent's production env still holds placeholder/default secrets. It runs the real agent boot guard (assert_boot_required_env) from the current dataland/agent:latest image, so the check can never drift from the boot-time contract:

docker run --rm --env-file dataland-agent/.env dataland/agent:latest \
  /app/.venv/bin/python -c "from app.runtime import assert_boot_required_env; assert_boot_required_env()"  # (1)!

Runs the real boot guard against the agent's env, so the deploy-time check can never drift from the boot-time contract. A non-zero exit aborts deploy.sh before the rebuild, preventing the crash-loop outage where a freshly-built container fails the guard and takes chat offline.

The guard is a no-op outside APP_ENV=production, and is skipped on the very first deploy when no image exists yet. See Deploy.

5. Vector store re-ingest (Qdrant backfills)¶

Qdrant has no Alembic equivalent — content "migrations" are re-ingests. Schema/payload changes or content edits are applied by re-running ingestion, which is safe because of two properties enforced by dataland-rag and the knowledge service's app/rag_sync.py:

Deterministic point ids — UUIDv5 derived from the source slug/path, so a re-ingest upserts in place instead of duplicating.
Replace-by-slug — every sync first issues a DELETE (e.g. DELETE /ingest/by-project-slug/<slug>, or by the namespaced museum slugs museum-section-<slug> / museum-scene-<slug>) to wipe stale points across both collections, then re-ingests.

Museum re-ingest¶

The 20 museum sections + their scenes + the museum overview are re-ingested into the Qdrant knowledge collection (currently ~4969 points). Text flows to /ingest/file (knowledge); images flow to /ingest/image (Gemini-captioned, images collection). Entity types on the payloads are section / scene / museum (plus section_image / scene_image).

Re-ingest is idempotent by design

Because ids are UUIDv5 and each sync deletes-then-reingests by slug, running the museum re-ingest twice converges on the same point set. See Knowledge and RAG.

Runbooks¶

For schema + backfills the authoritative runbook is dataland-agent/migrations/README.md. For env ownership it is docs/env-inventory.md. For host-level state, volumes, and backups see Host state & backups.