Docs/ Spec/ substrate

substrateshippedp0

Substrate migrations workflow

substrate-migrations-workflow · updated 2026-05-12T19:30:00Z · owner rritz

Header

Use the pencil to edit title, status, priority, and owner. Changing status auto-prepends a changelog entry.

Why

Schema is the source of truth: one Alembic migration per logical change,
living inside the installed josh_substrate package and addressed via
the package:resource syntax (script_location = josh_substrate:migrations).
Production runs use a Kamal pre-deploy hook so alembic upgrade head fires
against the new image *before* the container swap — failed migrations
abort the deploy, never half-apply against the running substrate.
Local development and tests use the same mechanism via a session-scoped
template DB in conftest.py.

User stories

As a substrate contributor, I want one place to write a schema change so that every service picks it up without duplicated DDL.

As an operator running `kamal deploy` for josh-core, I want pending migrations to apply automatically before the container swaps in so that the new image never sees a stale schema.

As a test author, I want a fresh schema-loaded SQLite without re-running Alembic per test so that tests don't pay a 3-5 s alembic cost each.

Acceptance criteria (EARS)

  1. When `alembic upgrade head` is run with `SUBSTRATE_DB_PATH` pointed at any path, the system shall apply every migration under `shared/josh_substrate/src/josh_substrate/migrations/versions/` in order and end with the latest revision marked current.
  2. When `kamal deploy` runs from the project root, the `.kamal/hooks/pre-deploy` shell hook shall execute `kamal app exec --roles web --version $KAMAL_VERSION 'alembic upgrade head'` against the new image's web role before the container swap; failure shall abort the deploy.
  3. When the pytest session starts, the `migrated_db_template` session-scoped fixture in repo-root `conftest.py` shall run `alembic upgrade head` exactly once into a temp template; per-test fixtures shall `shutil.copy` the template rather than re-running Alembic.

Success determiner

kindbash

Command

set -e
# 1. Migrations apply cleanly to a fresh DB.
tmp=$(mktemp -d)
SUBSTRATE_DB_PATH="$tmp/probe.db" \
  uv run --directory shared/josh_substrate alembic upgrade head
SUBSTRATE_DB_PATH="$tmp/probe.db" \
  uv run --directory shared/josh_substrate alembic current | grep -E "0005.*\(head\)"
# 2. The pre-deploy hook script exists at the canonical Kamal location.
test -x .kamal/hooks/pre-deploy
# 3. The hook runs alembic against the web role (post-consolidation
#    pattern; pre-consolidation it gated on basename "$PWD" != "josh-core").
grep -q "alembic upgrade head" .kamal/hooks/pre-deploy
grep -q -- "--roles web" .kamal/hooks/pre-deploy
# 4. Root deploy.yml exists (single Kamal config since consolidation).
test -f config/deploy.yml
rm -rf "$tmp"
echo OK

Expect

Last line of stdout is `OK`. `alembic current` shows the head revision (`0005…(head)`); the pre-deploy hook script exists at `.kamal/hooks/pre-deploy` and targets `--roles web`; the root `config/deploy.yml` is present.

Doesn't require a deployed server — proves the workflow is runnable locally and that the deploy-time pieces are wired. Re-run after any change to `alembic.ini` or the hook script.

Clarifications needed

None.

Out of scope

  • Online schema migrations / dual-writes. SQLite + Kamal pre-deploy keeps things simple — the container swap is the cutover.
  • Rollback policy. `alembic downgrade` exists but isn't part of the deploy automation; rolls happen by re-deploying an earlier image.
  • Multi-tenant per-customer schemas. Single substrate, single schema.

Dependencies

Plan

Three load-bearing pieces:

1. shared/josh_substrate/alembic.ini — uses
script_location = josh_substrate:migrations so alembic finds the
versions dir after a pip install. The DB URL is set programmatically
in migrations/env.py from SUBSTRATE_DB_PATH.
2. .kamal/hooks/pre-deploy — project-root hook. Targets
--roles web so alembic runs once against the web role-container
(the conceptual schema owner). Branches on KAMAL_DESTINATION to
avoid the empty--d Kamal crash documented in commit 026d7d1.
(Pre-consolidation revision gated on basename "$PWD" != "josh-core"
because each service had its own Kamal app; consolidation to a single
image with multiple roles made that gate unnecessary.)
3. conftest.py (repo root) — migrated_db_template session fixture
calls uv run alembic upgrade head once per pytest session into a
temp file. copy_template(src, dst) is the helper per-test fixtures
use to get a fresh schema-loaded DB without re-running alembic.

See https://docs.usejosh.com/operations/migrations/ for the operator-facing runbook,
including the macOS LANG workaround for production runs and manual
invocation patterns (kamal app exec --reuse 'alembic …').

Tasks

4 of 4 done.

  • t1 Alembic configured with package:resource script_location
  • t2 Pre-deploy Kamal hook wired and gated on service_dir
  • t3 conftest session fixture caches alembic upgrade across tests
  • t4 https://docs.usejosh.com/operations/migrations/ captures the runbook

Changelog

  • 2026-05-12T19:30:00Z shipped→shipped Reframed acceptance criterion 2 + 3 and the determiner for the consolidation to single-image multi-role Kamal deploy (`substrate-single-image-deploy`). Old wording assumed each service had its own `config/deploy.yml` and the hook gated on `basename "$PWD" != "josh-core"`. New wording: one root `config/deploy.yml`, hook targets `--roles web`, no per-service `.kamal` symlink. Workflow behavior is unchanged at the Alembic level — only the deploy-orchestration shape moved.
  • 2026-05-10T18:30:00Z (new)→shipped Spec content backfilled retrospectively. Migration workflow has been live since the SQLite swap; the pre-deploy hook fix landed in commit 026d7d1 and the session-scoped conftest cache landed in commit a0c2b0e. This commit replaces the [STUB] placeholders with grounded acceptance criteria and a mechanical bash determiner.

docs/spec/substrate-migrations-workflow.html · generated by bin/build-spec.py