Schema & Migrations

Aerostack uses a numbered migration file system. Migrations run in order and are tracked to prevent re-runs.

Create a migration

Use the CLI:

aerostack migration create add_posts_table
# Creates: migrations/0001_add_posts_table.sql

Or use the /new-migration slash command in Claude Code:

/new-migration

Write the migration

-- migrations/0001_add_posts_table.sql
CREATE TABLE IF NOT EXISTS posts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  title TEXT NOT NULL,
  body TEXT,
  published INTEGER DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
 
CREATE INDEX IF NOT EXISTS idx_posts_user_id ON posts(user_id);
CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at);

Apply migrations

# Local development
wrangler d1 migrations apply aerocall-core --local
 
# Production
wrangler d1 migrations apply aerocall-core --remote

⚠️

The npm run db:migrate script is currently broken (applies only file 0011 hardcoded). Use the wrangler command directly.

Common column patterns

-- Auto-ID with UUID (Workers-compatible)
id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
 
-- Timestamps (Unix ms)
created_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
updated_at INTEGER,
 
-- Soft delete
deleted_at INTEGER,
 
-- JSON metadata
metadata TEXT DEFAULT '{}',
 
-- Boolean (SQLite has no boolean type)
is_active INTEGER DEFAULT 1,

Check migration status

wrangler d1 migrations list aerocall-core

Run schema queries from SDK

// Get all table names
const { results } = await sdk.db.query(
  "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
)
 
// Column info
const { results: cols } = await sdk.db.query(
  `PRAGMA table_info(posts)`
)

Querying Batch Operations