Database Migrations

Apso treats .apsorc as the source of truth for schema changes. When the schema changes, use the migration sandbox to inspect and test the SQL before it touches a shared database.

This page is for developers evolving an existing Apso service. It is especially important when you are adding relationships, changing required fields, or working with AI-generated schema edits.

Why the Sandbox Exists

Backend generators can produce SQL that looks plausible but fails against real data. Apso reduces that risk by testing schema changes in a local PGlite database first.

The migration sandbox helps you answer three questions:

What SQL does this schema change require?
Does the SQL run successfully?
Is the change safe enough to apply to a shared environment?

Migration Workflow

1. Change `.apsorc`

Add one schema change at a time. For example, add priority to Project:

.apsorc


{
  "version": 2,
  "rootFolder": "src",
  "entities": [
    {
      "name": "Project",
      "fields": [
        { "name": "name", "type": "text" },
        { "name": "priority", "type": "integer", "default": 0 }
      ]
    }
  ],
  "relationships": []
}

2. Regenerate Code


apso generate

Generated entity metadata must match the schema before the migration sandbox can produce a useful diff.

3. Preview and Test the Migration


apso migrate

The command builds the previous schema snapshot in an in-process Postgres database, compares it to the current generated entities, generates SQL, and verifies that SQL locally.

For a change like adding priority, expect SQL similar to:


ALTER TABLE "projects"
ADD "priority" integer NOT NULL DEFAULT 0;

4. Print SQL for Review


apso migrate --sql

Use this when you want to paste the SQL into a pull request, review it with another engineer, or ask an AI assistant to explain the risk.

5. Accept the New Baseline


apso migrate --apply

Run --apply only after you have reviewed the output. It updates .apso/sandbox/schema-snapshot.json so future migration checks compare against the new schema.

Command Reference

Command	What it does
`apso migrate`	Tests schema changes in the local migration sandbox
`apso migrate --sql`	Prints migration SQL only
`apso migrate --apply`	Saves the current schema as the new sandbox baseline
`apso deploy`	Runs migration validation before deployment unless skipped

Effective Migration Habits

Make Small Changes

Add one entity, field, or relationship group at a time. Smaller diffs are easier to review and safer to deploy.

Review Destructive SQL

Treat these operations as high risk:

DROP TABLE
DROP COLUMN
Changing a nullable field to required
Renaming columns without a data migration plan
Changing enum values that existing rows may use

Plan Tenant Scope Early

If an entity belongs to a workspace, organization, account, or customer, add the scope field before production data exists. Retrofitting tenant isolation later is harder than starting with it.

Keep Generated Files Disposable

Do not patch src/autogen/ to make a migration pass. Fix .apsorc, run apso generate, then run apso migrate again.

AI-Assisted Review

When working with an AI assistant, ask it to review the migration output before applying it:


Review this Apso migration SQL.
Identify destructive operations, nullable-to-required changes,
relationship changes, and any data migration I need before deployment.

Do not let an assistant apply migrations to staging or production without a human review of the SQL.

Troubleshooting

No Changes Detected

Run apso generate first. The sandbox compares generated entity metadata, so stale generated files can hide schema changes.

SQL Fails Locally

Fix the schema or generated project until the migration passes locally. Do not skip the check to force a deployment.

Snapshot Is Wrong

If the sandbox baseline no longer represents your intended current schema, inspect .apso/sandbox/schema-snapshot.json. Run apso migrate --apply only after confirming the current schema is the baseline you want.