Schema code reference
The complete schema code syntax, the modeling style and the reference schemas, exactly as the assistant and connected AI apps get them.
This is the reference the in-app assistant has in its instructions when you work on an ER diagram, and connected AI apps get from lookup_guidance with the topic schema-code. For a walk-through, see Schema code.
Schema code syntax (DBML; what write_diagram takes and get_diagram returns)
Project <name> { the diagram's name, database and note (optional)
database_type: 'PostgreSQL' PostgreSQL, MySQL or SQL Server: sets the types you write
Note: 'What this schema is for'
}
Table <name> [headercolor: #3498DB] { a table; schema.name outside the default schema; "as <alias>" before the settings
<column> <type> [<settings>] one column per line
Note: 'What a row is'
indexes {
(a, b) [pk] a composite primary key
email [unique] a unique index; (a, b) [unique] across columns
created_at a plain index; [name: 'ix_name', type: btree]
}
}
Column settings, comma separated: pk, increment, unique, not null, default: <value>, note: '...', ref: > other.id
default: 0 / true / 'draft' / `now()` a value, or an expression in backticks
ref: > users.id this column points at users.id: many to one
ref: - users.id one to one; ref: <> tags.id many to many (better a junction table)
Ref: orders.customer_id > customers.id [delete: cascade, update: no action]
a relationship on a line of its own; name it with Ref name: ...
Ref: order_items.(order_id, line) > orders.(id, line)
composite keys
Enum <name> { a type with fixed values; columns use it by name
pending
paid [note: 'Money received']
}
TableGroup <name> { a frame round related tables
orders
order_items
}
// <text> a comment on a line of its own
Names with spaces or symbols go in double quotes: "order items". Text goes in single quotes.
Columns are nullable unless marked not null; primary key columns are never null.
get_diagram prints every element's id. Keep those ids when you rewrite a diagram, so the user's comments stay on their elements.Example
The reference ends with this example.
Project shop {
database_type: 'PostgreSQL'
}
Enum order_status {
pending
paid
shipped
}
Table customers {
id integer [pk, increment]
email varchar(255) [unique, not null]
name varchar(120) [not null]
created_at timestamp [not null, default: `now()`]
}
Table orders {
id integer [pk, increment]
customer_id integer [not null, ref: > customers.id]
status order_status [not null, default: 'pending']
total decimal(10,2) [not null]
created_at timestamp [not null, default: `now()`]
}Modeling style
The topic schema-style is the style the assistant follows when it designs a schema.
ER diagram style
- One schema per diagram, named for the system it serves ("Online store", "Team workspace").
- Plural snake_case table names, snake_case columns, <thing>_id for foreign keys.
- A primary key on every table; composite keys for junction tables.
- Foreign keys with the type of the key they point at; not null when the row can't exist without its parent.
- Enums for fixed states, lookup tables for lists that change.
- Constraints that protect the data: unique emails and slugs, not null on required fields, sensible defaults.
- Money as decimal, times as timestamp, flags as boolean with a default.
- Indexes on columns searched or joined often.
- Table groups for areas of the system once the schema passes about eight tables.Reference schemas
When a request covers a common kind of system, the assistant can look up a reference schema and adapt it to the tables and names you give. Each one says which decisions matter, gives a compact draft in schema code, and lists what is often missed. These are the texts lookup_guidance returns, with each draft drawn.
Online store schema reference
Topic store-schema.
Customers, a catalog with variants, carts, orders and payments.
Decisions that matter:
- An order copies what it needs at the time: unit_price on order_items and the shipping address id, so later price or address edits don't rewrite history.
- Sellable things are product_variants (size, colour) with their own sku and price; products group them.
- Payments are their own table: one order can have a failed attempt, then a captured one, then a refund.
- Money as decimal(10,2), or integer cents named *_cents. Never float.
Enum order_status {
pending
paid
shipped
cancelled
}
Table customers {
id integer [pk, increment]
email varchar(255) [unique, not null]
name varchar(120) [not null]
created_at timestamp [not null, default: `now()`]
}
Table products {
id integer [pk, increment]
name varchar(255) [not null]
slug varchar(255) [unique, not null]
}
Table product_variants {
id integer [pk, increment]
product_id integer [not null, ref: > products.id]
sku varchar(64) [unique, not null]
price decimal(10,2) [not null]
stock integer [not null, default: 0]
}
Table orders {
id integer [pk, increment]
customer_id integer [not null, ref: > customers.id]
status order_status [not null, default: 'pending']
total decimal(10,2) [not null]
placed_at timestamp [not null, default: `now()`]
}
Table order_items {
id integer [pk, increment]
order_id integer [not null, ref: > orders.id]
variant_id integer [not null, ref: > product_variants.id]
quantity integer [not null]
unit_price decimal(10,2) [not null]
}
Table payments {
id integer [pk, increment]
order_id integer [not null, ref: > orders.id]
provider_ref varchar(255) [unique, not null]
amount decimal(10,2) [not null]
status varchar(20) [not null]
}Often missed: addresses as their own table (customers have several), categories as a self-reference (parent_id) or a junction product_categories, carts and cart_items for a basket that outlives a session, shipments when one order ships in parts, an index on orders.customer_id.
Multi-tenant SaaS schema reference
Topic saas-schema.
Accounts (workspaces, organisations or teams) that own everything, people who belong to several, roles and invites.
Decisions that matter:
- Every tenant-owned table carries the tenant's id (team_id, org_id), not null, even when it could be reached through a parent: queries and access checks filter on it directly.
- People and tenants are many-to-many: a memberships junction with the role, and a composite primary key (user_id, team_id).
- Invites are their own table: an email that isn't a user yet, who invited them, and when it expires.
- Plans and billing hang off the tenant, not the user.
Enum member_role {
owner
admin
member
}
Table users {
id integer [pk, increment]
email varchar(255) [unique, not null]
name varchar(120)
created_at timestamp [not null, default: `now()`]
}
Table teams {
id integer [pk, increment]
name varchar(120) [not null]
slug varchar(60) [unique, not null]
created_at timestamp [not null, default: `now()`]
}
Table memberships {
user_id integer [not null, ref: > users.id]
team_id integer [not null, ref: > teams.id]
role member_role [not null, default: 'member']
indexes {
(user_id, team_id) [pk]
}
}
Table invites {
id integer [pk, increment]
team_id integer [not null, ref: > teams.id]
email varchar(255) [not null]
role member_role [not null, default: 'member']
invited_by integer [not null, ref: > users.id]
expires_at timestamp [not null]
}
Table projects {
id integer [pk, increment]
team_id integer [not null, ref: > teams.id]
name varchar(255) [not null]
created_by integer [not null, ref: > users.id]
}Often missed: a unique index on (team_id, email) for invites, subscriptions with plan and status on the tenant, an api_keys table owned by the tenant, audit events (see "history-schema").
Booking and scheduling schema reference
Topic booking-schema.
Clients booking a time slot for a service with a provider (staff, room or resource).
Decisions that matter:
- A booking stores starts_at and ends_at (or a duration copied from the service at booking time), so changing a service doesn't move past bookings.
- Double booking is prevented by a unique index on (staff_id, starts_at), or an exclusion constraint where the database has one.
- Availability is its own table: weekly opening hours per provider, with exceptions for days off.
- Cancellation is a status with cancelled_at, not a deleted row.
Enum booking_status {
booked
completed
cancelled
no_show
}
Table clients {
id integer [pk, increment]
name varchar(120) [not null]
email varchar(255) [unique, not null]
}
Table staff {
id integer [pk, increment]
name varchar(120) [not null]
active boolean [not null, default: true]
}
Table services {
id integer [pk, increment]
name varchar(120) [not null]
minutes integer [not null]
price decimal(10,2) [not null]
}
Table bookings {
id integer [pk, increment]
client_id integer [not null, ref: > clients.id]
staff_id integer [not null, ref: > staff.id]
service_id integer [not null, ref: > services.id]
starts_at timestamp [not null]
ends_at timestamp [not null]
status booking_status [not null, default: 'booked']
cancelled_at timestamp
indexes {
(staff_id, starts_at) [unique]
}
}
Table availability {
id integer [pk, increment]
staff_id integer [not null, ref: > staff.id]
weekday smallint [not null]
opens time [not null]
closes time [not null]
}Often missed: which services each provider offers (a junction staff_services), time zones (store timestamps in UTC, the location's zone on the location), payments or deposits per booking, reminders sent.
Invoicing and billing schema reference
Topic billing-schema.
Customers billed with invoices made of line items, paid by one or more payments.
Decisions that matter:
- An invoice has a human number, unique, separate from its id, and a status (draft, sent, paid, void).
- Line items copy description, quantity and unit price; the invoice's totals are stored once it is sent, so they never change.
- Payments and invoices can be many-to-many when one payment settles several invoices: a payment_allocations junction with the amount applied.
- Taxes are per line (tax_rate), and amounts are decimal(12,2) with a currency char(3).
Enum invoice_status {
draft
sent
paid
void
}
Table customers {
id integer [pk, increment]
name varchar(255) [not null]
billing_email varchar(255) [not null]
}
Table invoices {
id integer [pk, increment]
customer_id integer [not null, ref: > customers.id]
number varchar(30) [unique, not null]
status invoice_status [not null, default: 'draft']
currency char(3) [not null]
issued_at date
due_at date
total decimal(12,2) [not null, default: 0]
}
Table invoice_lines {
id integer [pk, increment]
invoice_id integer [not null, ref: > invoices.id]
description varchar(255) [not null]
quantity decimal(10,2) [not null]
unit_price decimal(12,2) [not null]
tax_rate decimal(5,2) [not null, default: 0]
}
Table payments {
id integer [pk, increment]
customer_id integer [not null, ref: > customers.id]
amount decimal(12,2) [not null]
received_at timestamp [not null]
method varchar(30) [not null]
}
Table payment_allocations {
payment_id integer [not null, ref: > payments.id]
invoice_id integer [not null, ref: > invoices.id]
amount decimal(12,2) [not null]
indexes {
(payment_id, invoice_id) [pk]
}
}Often missed: credit notes as negative invoices or their own table, products or price lists the lines come from, subscriptions that generate invoices, an index on invoices.due_at for overdue reports.
Content and publishing schema reference
Topic content-schema.
Authors writing posts or articles, with comments, tags and categories.
Decisions that matter:
- A post has a unique slug for its URL, a status (draft, published, archived) and published_at, which stays empty until it is published.
- Tags are many-to-many: a post_tags junction with a composite primary key. Categories are usually one per post (category_id) or a tree (parent_id).
- Comments belong to a post and, for threads, to a parent comment; guests' comments carry a name and email instead of a user.
Enum post_status {
draft
published
archived
}
Table authors {
id integer [pk, increment]
email varchar(255) [unique, not null]
name varchar(120) [not null]
}
Table posts {
id integer [pk, increment]
author_id integer [not null, ref: > authors.id]
title varchar(255) [not null]
slug varchar(255) [unique, not null]
body text [not null]
status post_status [not null, default: 'draft']
published_at timestamp
}
Table comments {
id integer [pk, increment]
post_id integer [not null, ref: > posts.id]
parent_id integer [ref: > comments.id]
name varchar(120) [not null]
body text [not null]
created_at timestamp [not null, default: `now()`]
}
Table tags {
id integer [pk, increment]
name varchar(60) [unique, not null]
}
Table post_tags {
post_id integer [not null, ref: > posts.id]
tag_id integer [not null, ref: > tags.id]
indexes {
(post_id, tag_id) [pk]
}
}Often missed: media or attachments as their own table, revisions when edits need a history (see "history-schema"), an index on (status, published_at) for the listing page.
History, soft delete and audit reference
Topic history-schema.
Keeping what changed and who changed it, without losing the current row.
Choose by what the user needs:
- Soft delete: deleted_at timestamp (nullable) on the table; queries skip rows that have one. Unique indexes then need the database's partial index, so mention it in a Note.
- Who and when: created_at, updated_at and, when it matters, created_by and updated_by referencing users. Add them where the user asked, not everywhere.
- Full history of one table: a <table>_versions table with the same columns plus version integer, changed_at and changed_by, and a composite key (<table>_id, version).
- An audit log across tables: one audit_events table with actor, action, table name, row id and a jsonb of the change. It points at rows by value, not by foreign key, so it survives deletes.
This draft is part of a schema, so it is shown as code only.
Table audit_events {
id bigint [pk, increment]
actor_id integer [ref: > users.id]
action varchar(20) [not null]
table_name varchar(60) [not null]
row_id varchar(60) [not null]
changes jsonb
at timestamp [not null, default: `now()`]
indexes {
(table_name, row_id)
}
}Don't add history the user didn't ask for: it doubles the tables to read.