Step 11: Create Database Tables

Phase: implement

Context

You have project_id, service_key, and required_services with table definitions. Time to create the database schema.

What to do

Two paths — pick whichever fits the flow:

Preferred: registered migration via unified deploy

Put SQL under database.migrations[] in p.apply(). The gateway tracks each migration by (id, checksum): same id + same SQL is a no-op; same id + different SQL is a hard MIGRATION_CHECKSUM_MISMATCH error. Use a fresh id for each new SQL change.

const p = await r.project(env.PROJECT_ID);
await p.apply({
  database: {
    migrations: [{
      id: "001_init",
      sql: `CREATE TABLE todos (
        id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        task text NOT NULL,
        done boolean DEFAULT false,
        user_id uuid,
        created_at timestamptz DEFAULT now()
      );
      CREATE TABLE categories (
        id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        name text NOT NULL UNIQUE,
        created_at timestamptz DEFAULT now()
      );`
    }]
  }
});

You can combine this with database.expose (Step 12), site.replace (Step 15), and functions.replace in a single deploy. They all activate atomically.

Imperative escape hatch: admin SQL endpoint

For ad-hoc one-shot SQL outside a deploy, the admin SQL endpoint still works:

POST https://api.run402.com/projects/v1/admin/{project_id}/sql
Content-Type: application/json
Authorization: Bearer {service_key}

{
  "sql": "CREATE TABLE todos (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), task text NOT NULL, done boolean DEFAULT false, user_id uuid, created_at timestamptz DEFAULT now());"
}

Response:

{
  "status": "ok",
  "schema": "p0001",
  "rows": [],
  "row_count": 0
}

SDK equivalent (2.0+): (await r.project(projectId)).sql(sqlString). MCP equivalent: run_sql. Use this only for exploration or ad-hoc fixes — registered migrations (above) are the durable path because the gateway tracks them.

SQL rules

  • Allowed: CREATE TABLE, ALTER TABLE, CREATE INDEX, INSERT (for seed data), UPDATE, DELETE, SELECT
  • Blocked (will return 403): CREATE EXTENSION, COPY PROGRAM, ALTER SYSTEM, SET search_path, SET role, CREATE/DROP SCHEMA, GRANT/REVOKE, CREATE/DROP ROLE
  • Use gen_random_uuid() for UUID primary keys (built-in, no extension needed)
  • Use timestamptz for timestamps (timezone-aware)
  • Use text for strings (no need for varchar length limits)
  • Use jsonb for flexible/nested data

Schema reload delay

Important: After creating tables, the API needs 100-500ms to recognize the new schema. If the next API call returns a 404 for a table that was just created, wait 500ms and retry. This is normal.

Seed data (if needed)

If the build plan requires initial data (e.g., default categories, sample content), insert it in the same SQL migration or a follow-up call:

INSERT INTO categories (name) VALUES
  ('General'),
  ('Urgent'),
  ('Personal');

Verify tables

After creation, verify the schema:

GET https://api.run402.com/projects/v1/admin/{project_id}/schema
Authorization: Bearer {service_key}

What to tell the user

"I've set up the storage for your app. Everything your app needs to remember will go here."

Expected output

  • tables_created — List of table names successfully created, with their columns: 
    [
  {"name": "todos", "columns": ["id", "task", "done", "user_id", "created_at"]},
  {"name": "categories", "columns": ["id", "name", "created_at"]}
]

Memory directive