Schema Extension

Extensions add database tables via the schemas() and migrations() methods.

Schema Definition

fn schemas(&self) -> Vec<SchemaDefinition> {
    vec![
        SchemaDefinition::new("users", include_str!("../schema/001_users.sql")),
        SchemaDefinition::new("sessions", include_str!("../schema/002_sessions.sql")),
    ]
}

SchemaDefinition::new(table, sql) takes the table name and its SQL. Embed the SQL at compile time with include_str! or pass a string literal directly.

Required Columns

Validate that columns exist after schema creation:

SchemaDefinition::new("users", include_str!("../schema/users.sql"))
    .with_required_columns(vec!["id".into(), "email".into(), "created_at".into()])

Non-Default Schema

Tables land in public by default. Target another Postgres schema with:

SchemaDefinition::new("events", include_str!("../schema/events.sql"))
    .with_schema("audit")

Execution Order

Extensions run in topological order based on dependencies(). Declare the extensions whose tables must exist before yours:

fn dependencies(&self) -> Vec<&'static str> {
    vec!["users"]  // Runs after the users extension
}

Ties are broken by priority() (lower runs first). Missing dependencies are warned and ignored; a dependency cycle is a load error.

fn priority(&self) -> u32 {
    50
}

Versioned Migrations

For schema evolution after initial deployment:

fn migrations(&self) -> Vec<Migration> {
    vec![
        Migration::new(1, "add_email_column",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS email TEXT"),
        Migration::new(2, "add_email_index",
            "CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)"),
    ]
}

Each migration runs once, tracked by version number.

SQL Patterns

Use idempotent patterns:

-- Tables
CREATE TABLE IF NOT EXISTS my_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_my_items_name ON my_items(name);

-- Columns (in migrations)
ALTER TABLE my_items ADD COLUMN IF NOT EXISTS description TEXT;

Typed Extension

For compile-time type safety:

use systemprompt::extension::prelude::SchemaExtensionTyped;

impl SchemaExtensionTyped for MyExtension {
    fn schemas(&self) -> Vec<SchemaDefinitionTyped> {
        vec![
            SchemaDefinitionTyped::new("users", include_str!("../schema/users.sql")),
        ]
    }
}