angora-schema

name	angora-schema
description	Design or evolve SQLite schema (`src/data/schema/tables/*.ts`) using Drizzle ORM — new tables, relational modeling, junction tables for many-to-many, foreign keys, CHECK constraints, SEO fields (slug, meta_title, meta_description), draft/published status columns, and media references. Trigger whenever the user wants to model a new content type (events, team members, case studies, pricing tiers, blog posts with tags), rethink an existing table's structure, or design relationships between tables. Phrases like 'new table for events', 'model a pricing_tiers table', 'schema for a job board', 'junction table for posts and tags', 'how should I model FAQs by category' all trigger this. For single-column additions or quick queries use angora-data.
argument-hint	<what to model>

Schema: $ARGUMENTS

Before you start

1. Read src/system.md — understand the site's intent and scope.
2. Read schema — glob src/data/schema/tables/*.ts to discover tables, then read the relevant files. One file per table.

Analysis phase (always happens)

Whether the user gives you structured data or a vague description, always analyze first. Ask yourself:

• "Six months from now, what will they wish they had modeled?"
• Arrays of objects → child tables
• Repeated file references → media references (FK to media)
• Many-to-many relationships → junction tables
• Enum-like values → CHECK constraints or lookup tables
• Is this content that drives pages? → needs SEO + status fields

Ask the user probing questions. Don't just accept the first description — refine it.

Schema conventions

Every table gets:

• id INTEGER PRIMARY KEY AUTOINCREMENT
• created_at TEXT NOT NULL DEFAULT (datetime('now'))
• Foreign keys where appropriate

Page-driving tables

When records will get their own pages (blog posts, case studies, team members, cities), add:

• slug TEXT NOT NULL UNIQUE — URL-safe identifier
• meta_title TEXT — for <title> tag
• meta_description TEXT — for <meta name="description"> and OG tags
• status TEXT NOT NULL DEFAULT 'draft' with CHECK (status IN ('draft', 'published')) — enables content staging, only published records render

Explain to the user why these fields matter (SEO, content staging, clean URLs).

Media references

Use media_id INTEGER REFERENCES media(id) for a primary image/asset. For multiple images, consider a junction table.

Presentation before execution

Before changing any files:

1. Show the Drizzle table definition — the TypeScript that will go in its own file under schema/
2. Show example data — 2-3 realistic rows per table
3. Show example queries — Drizzle queries page templates will need (e.g., db.select().from(testimonials).where(eq(testimonials.status, 'published')))
4. Wait for user approval — all schema changes require explicit approval

Applying schema changes

Schema changes follow a three-step process:

1. Create a new file in src/data/schema/tables/

One file per table. Example — src/data/schema/tables/testimonials.ts:

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';
import { media } from './media';

export const testimonials = sqliteTable('testimonials', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  quote: text('quote').notNull(),
  mediaId: integer('media_id').references(() => media.id),
  status: text('status').notNull().default('draft'),
  createdAt: text('created_at').notNull().default(sql`(datetime('now'))`),
});

Then add the re-export to src/data/schema/index.ts:

export * from './tables/testimonials';

2. Generate migration

pnpm db:generate

This creates a readable .sql file in src/data/migrations/. Show it to the user.

3. Apply migration

pnpm db:migrate

Each step requires user approval before executing.

After applying, mention: "You can browse the data with pnpm db:studio whenever you're ready to seed some content."

Rules

• All schema changes require user approval before executing.
• Foreign keys are ON — respect referential integrity.
• Use text() for dates (ISO 8601 format via datetime('now')).
• Use integer() for booleans (0/1).
• Media paths are relative to public/media/.
• The media table is the base — other tables reference it via media_id.
• Never modify data — use /angora-data for seeding or /angora-import for bulk inserts.
• Never delete tables or columns without explicit permission.