Database development: .schema.iwc

Purpose: one database table: its fields, their validation, relations, indexes, who may read and write it, and development seed rows. When: for every kind of record the app stores (users, products, orders, messages).

IWC creates and migrates the tables for you: SQLite in development (.iwc/dev.db), and SQLite or Postgres in production. You never write SQL.

Structure

{
  "name": "product",
  "description": "Things we sell.",
  "fields": {
    "name": { "type": "string", "required": true, "maxLength": 120 },
    "slug": { "type": "string", "required": true, "unique": true, "pattern": "^[a-z0-9-]+$" },
    "description": { "type": "text", "maxLength": 4000 },
    "price": { "type": "number", "required": true, "min": 0 },
    "stock": { "type": "integer", "required": true, "min": 0, "default": 0 },
    "category": { "type": "relation", "to": "category.schema.iwc", "onDelete": "set null" },
    "status": { "type": "enum", "values": ["draft", "active", "retired"], "default": "draft", "index": true },
    "featured": { "type": "boolean", "default": false },
    "specs": { "type": "json" },
    "launchDate": { "type": "date" },
    "costPrice": { "type": "number", "sensitive": true, "description": "never sent to browsers" }
  },
  "indexes": [["status", "featured"]],
  "unique": [["category", "name"]],
  "access": { "read": "public", "create": ["admin"], "update": ["admin"], "delete": ["admin"] },
  "seed": [
    { "id": "p-lamp", "name": "Desk Lamp", "slug": "desk-lamp", "price": 59, "stock": 8, "status": "active" }
  ]
}

Every table also gets id (a UUID unless a seed row gives one), and createdAt/updatedAt (ISO timestamps). You can't declare these yourself.

Field types

Field options for every type: required, unique, index, default (a literal, or "now" for datetimes), sensitive, description.

These rules are enforced on every write: through APIs, and also from actions, so an action can't store an invalid row either.

Access rules

Defaults, when access is omitted: read is public, except on the user schema, where each user can read only themselves. create needs sign-in. update and delete are owner-only when the schema has an owner, and otherwise not allowed at all.

Rules apply to db.* APIs and to DATABASE.* data sources. Actions are trusted server code: they check their own auth/roles, and their reads and writes ignore row-level rules (tenancy still applies).

{
  "name": "note",
  "fields": {
    "author": { "type": "relation", "to": "user.schema.iwc", "required": true, "onDelete": "cascade" },
    "title": { "type": "string", "required": true, "maxLength": 120 },
    "body": { "type": "text" }
  },
  "owner": "author",
  "access": { "read": "owner", "create": "auth", "update": "owner", "delete": "owner" }
}

With this schema, GET /notes style data sources show each user their own notes only, and POST /api/notes stores the caller as author whatever the body says (an author in the payload is rejected unless the API declares it, and is overwritten if it does).

Sensitive fields

password fields and fields with "sensitive": true are never selected for pages, never returned by APIs, and can't be filtered, sorted or searched from a page. Each attempt is a compile error. Use them for cost prices, internal notes and tokens.

Encrypted fields

"encrypted": true stores a string, text, email, url, json, date or datetime field as AES-256-GCM ciphertext (key: IWC_ENCRYPTION_KEY). Reads through IWC return the plain value; the database and its backups hold only ciphertext. Encrypted fields can't be filtered, sorted, searched, indexed or unique. Combine with sensitive when the value must also never reach a browser. Keys, rotation and backups: security.

Relations

A relation stores the related row's id. Read it in a page as a plain value ("bind": "category" shows the id; "filter": {"category": "cat-lighting"} filters by it), or attach the related record with an include to show its name.

Writes are checked: through db.create/db.update APIs, a relation must point at a record the caller could read. In an action, it must point at a record in the caller's tenant. Otherwise the write is refused with a 422 naming the field, and "doesn't exist" and "not yours" give the same answer. This keeps a member of one tenant from linking their rows to another tenant's records, which would let that tenant's cascade deletes, or its restrict rules, reach across. Jobs and event handlers (no visitor) aren't checked.

Unique among some rows

"At most one active delivery per order" is a unique constraint over the rows in some states only:

{
  "name": "assignment",
  "fields": {
    "order": { "type": "relation", "to": "order.schema.iwc", "required": true },
    "status": { "type": "enum", "values": ["offered", "accepted", "delivered", "rejected"], "default": "offered", "required": true }
  },
  "partialUnique": [{ "fields": ["order"], "where": { "status": { "in": ["offered", "accepted"] } } }]
}

The database enforces it (CREATE UNIQUE INDEX … WHERE … on PostgreSQL and SQLite), so two concurrent writes can't both succeed: the second gets 409 conflict. Conditions are literal values: a value, {"in": [...]}, {"ne": value}, or null. Changing a condition creates a new index; the old one is left until you drop it.

Tables that point at each other

An order points at its current delivery assignment, and the assignment at its order. Such a cycle can't be created in one pass, so IWC creates the tables first and then adds the foreign key that closes the cycle (ALTER TABLE … ADD CONSTRAINT on PostgreSQL). SQLite can't add a foreign key to an existing table, so there that one field is checked by IWC's relation checks only. Seed rows are inserted in the order their relations need, whatever order the tables were created in.

Seeds

{
  "name": "user",
  "fields": {
    "name": { "type": "string", "required": true },
    "email": { "type": "email", "required": true, "unique": true },
    "password": { "type": "password", "required": true },
    "role": { "type": "enum", "values": ["customer", "admin"], "default": "customer", "required": true }
  },
  "seed": [
    { "id": "u-admin", "name": "Avery Admin", "email": "admin@example.com", "password": "admin-pass-123", "role": "admin" }
  ]
}

Seed rows are inserted when a table is empty, at dev start and in every test run. Give them stable ids so other seeds can relate to them ("category": "cat-lighting"). To re-seed, delete .iwc/dev.db.

Seeds are development fixtures: production never receives them. When the rows are part of the app itself — categories, a catalogue, the examples on the default website — add "seedInProduction": true. iwc start (and a deploy host) then inserts them into the table when it is empty, once; rows already there are never touched. Never mark test users or passwords this way.

{
  "name": "category",
  "table": "categories",
  "fields": { "name": { "type": "string", "required": true }, "slug": { "type": "string", "required": true, "unique": true } },
  "access": { "read": "public" },
  "seedInProduction": true,
  "seed": [{ "id": "cat-lighting", "name": "Lighting", "slug": "lighting" }]
}

Migrations

Whenever the project compiles (in iwc dev, iwc start, iwc migrate), IWC compares the schemas with the database:

iwc migrate --dry-run prints the SQL. iwc build writes it per database to dist/migrations/. IWC refuses to migrate a database (or Postgres schema) holding tables it didn't create, so pointing at the wrong database fails safely.

Production database

{ "name": "Shop", "database": { "url": { "env": "SHOP_DATABASE_URL" } } }

With this, iwc start reads the Postgres URL (postgres://user:pass@host/db) from SHOP_DATABASE_URL and refuses to start without it. Without database, production uses SQLite in the data folder. Never put a database URL in a file: the compiler rejects files that contain credentials.

Common mistakes