Database

Capstan's database layer (@zauso-ai/capstan-db) provides a model definition system built on top of Drizzle ORM. Define your models once with defineModel() and get typed schemas, migration generation, and automatic CRUD route scaffolding.

defineModel()

defineModel() declares a data model with fields, relations, and indexes:

import { defineModel, field, relation } from "@zauso-ai/capstan-db";

export const Ticket = defineModel("ticket", {
  fields: {
    id: field.id(),
    title: field.string({ required: true, min: 1, max: 200 }),
    description: field.text(),
    status: field.enum(["open", "in_progress", "closed"], { default: "open" }),
    priority: field.enum(["low", "medium", "high"], { default: "medium" }),
    assigneeId: field.string({ references: "user" }),
    createdAt: field.datetime({ default: "now" }),
    updatedAt: field.datetime({ updatedAt: true }),
  },
  relations: {
    assignee: relation.belongsTo("user", { foreignKey: "assigneeId" }),
    comments: relation.hasMany("comment"),
  },
  indexes: [
    { fields: ["status"], unique: false },
    { fields: ["assigneeId", "status"], unique: false },
  ],
});

Field Types

The field helper provides builders for all supported scalar types:

Field Options

Every field builder accepts an optional options object:

interface FieldOptions {
  required?: boolean;    // NOT NULL constraint
  unique?: boolean;      // UNIQUE constraint
  default?: unknown;     // Default value ("now" for datetime auto-fill)
  min?: number;          // Minimum length (string) or value (number)
  max?: number;          // Maximum length (string) or value (number)
  updatedAt?: boolean;   // Auto-set to current time on update
  autoId?: boolean;      // Auto-generate UUID (set automatically by field.id())
  references?: string;   // Foreign key reference to another model name
}

Examples:

// Required string with length constraints
field.string({ required: true, min: 1, max: 200 })

// Integer with range validation
field.integer({ required: true, min: 0, max: 100 })

// Boolean with default value
field.boolean({ default: false })

// Datetime that auto-fills on create
field.datetime({ default: "now" })

// Datetime that auto-updates
field.datetime({ updatedAt: true })

// Enum with default
field.enum(["low", "medium", "high"], { default: "medium" })

// Foreign key reference
field.string({ references: "user" })

// Vector embedding (1536 dimensions for OpenAI ada-002)
field.vector(1536)

Relations

The relation helper defines how models connect to each other:

import { relation } from "@zauso-ai/capstan-db";

relation.belongsTo("user", { foreignKey: "assigneeId" })
relation.hasMany("ticket")
relation.hasOne("profile")
relation.manyToMany("tag", { through: "ticket_tags" })

Database Providers

Capstan supports four database providers, each using Drizzle ORM with a provider-specific driver:

Install the driver for your chosen provider:

# SQLite
npm install better-sqlite3 drizzle-orm

# libSQL / Turso
npm install @libsql/client drizzle-orm

# PostgreSQL
npm install pg drizzle-orm

# MySQL
npm install mysql2 drizzle-orm

Configuration

Configure the database in capstan.config.ts:

import { defineConfig, env } from "@zauso-ai/capstan-core";

export default defineConfig({
  app: { name: "my-app" },
  database: {
    provider: "sqlite",
    url: env("DATABASE_URL") || "./data.db",
  },
});

For edge-deployed apps, use the libsql provider with Turso:

database: {
  provider: "libsql",
  url: env("TURSO_DATABASE_URL"),
  authToken: env("TURSO_AUTH_TOKEN"),
},

Migrations

Capstan provides a migration system that generates SQL from model definition diffs and tracks applied migrations in a _capstan_migrations table.

The migration generator produces forward-only diffs:

• New models produce CREATE TABLE statements
• New fields produce ALTER TABLE ADD COLUMN statements
• New indexes produce CREATE INDEX statements
• Removed models produce DROP TABLE statements

Auto-Generated CRUD

generateCrudRoutes() creates API route files from a model definition:

import { generateCrudRoutes } from "@zauso-ai/capstan-db";
import { Ticket } from "./models/ticket.model.js";

const files = generateCrudRoutes(Ticket);
// Returns:
// [
//   { path: "tickets/index.api.ts", content: "..." },   // GET (list) + POST (create)
//   { path: "tickets/[id].api.ts", content: "..." },     // GET (by id) + PUT (update) + DELETE
// ]

The generated files include:

• Zod input/output schemas derived from the model fields
• defineAPI() handlers with proper capability and resource metadata
• policy: "requireAuth" on all write endpoints
• TODO comments where you plug in actual database queries

Or use the CLI scaffolder:

npx capstan add api tickets    # Generates CRUD routes for tickets

RAG Primitives

Capstan provides built-in support for vector embeddings and similarity search, enabling retrieval-augmented generation (RAG) workflows.

defineEmbedding()

Configure an embedding model to generate vectors from text:

import { defineEmbedding, openaiEmbeddings } from "@zauso-ai/capstan-db";

export const embeddings = defineEmbedding("text-embedding-3-small", {
  dimensions: 1536,
  adapter: openaiEmbeddings({ apiKey: env("OPENAI_API_KEY") }),
});

Vector Fields

Add a field.vector() column to store embeddings alongside your data:

export const Document = defineModel("document", {
  fields: {
    id: field.id(),
    content: field.text({ required: true }),
    embedding: field.vector(1536),
  },
});

Vector Search

Query by similarity using cosine distance:

import { vectorSearch } from "@zauso-ai/capstan-db";

const results = await vectorSearch(db, {
  table: "documents",
  column: "embedding",
  query: await embeddings.embed("How do I reset my password?"),
  limit: 5,
});
// Returns rows ordered by cosine similarity

Table Naming

Model names are automatically pluralized for table names: