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

Execute SQL migration

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

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()
);

Response:

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

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://run402.com/admin/v1/projects/{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