05. Database

Scrivener is the model layer. It never learns which database it is talking to.

The query IR

The requirement is that nobody thinks about PostgREST, or about SQL dialects, or about which driver is configured. That means the model layer cannot be built on a driver's query builder, because every leaked quirk becomes application-visible.

The model and query builder produce a serializable QueryIR. The driver compiles it.

Revised after M0: the spike proved result parity on every expressible operation and failed the assumption on underdefinition. Everything a compiler would otherwise have to guess is now explicit (D25).

export type CompareOp = '=' | '!=' | '<' | '<=' | '>' | '>=' | 'like' | 'ilike'

export interface OrderTerm {
  column: string
  direction: 'asc' | 'desc'
  nulls?: 'first' | 'last'
}

export interface RelationLoad {
  /** Relation name as declared on the model. Keys the loaded rows in the result. */
  relation: string
  kind: 'belongsTo' | 'hasOne' | 'hasMany'
  /** Resolved by the model layer so drivers never introspect models. */
  table: string
  /** Column on the parent row. */
  localKey: string
  /** Column on the related table. */
  foreignKey: string
  select: string[] | '*'
  where: Predicate | null
  order: OrderTerm[]
  limit?: number
  /** Nested loads. Total depth is checked against the driver's maxRelationDepth. */
  relations: RelationLoad[]
}

export interface QueryIR {
  table: string
  mode: 'select' | 'count' | 'insert' | 'update' | 'delete' | 'upsert'
  select: string[] | '*'
  /** Entries compose with AND. */
  where: Predicate[]
  relations: RelationLoad[]
  order: OrderTerm[]
  limit?: number
  offset?: number
  values?: Record<string, unknown> | Record<string, unknown>[]
  returning?: string[] | '*'
  /**
   * Required when mode is 'upsert'. `update` names the columns overwritten on conflict;
   * `'*'` means every supplied value column not named in `conflict.columns`.
   */
  conflict?: { columns: [string, ...string[]]; update: [string, ...string[]] | '*' }
  /** Injected by the ward compiler, ANDed with `where`. Never author-supplied. */
  ward?: Predicate
}

export type Predicate =
  | { kind: 'const'; value: boolean }
  | { kind: 'compare'; column: string; op: CompareOp; value: unknown }
  | { kind: 'null'; column: string; negated: boolean }
  | { kind: 'in'; column: string; values: unknown[]; negated: boolean }
  | { kind: 'and'; predicates: Predicate[] }
  | { kind: 'or'; predicates: Predicate[] }
  | { kind: 'not'; predicate: Predicate }

Defined semantics

These rules are part of the contract, not driver behavior. Every compiler normalizes before emitting, so two drivers can never disagree about what an IR means:

• where entries and the injected ward compose with AND.
• and: [] is const true. or: [] is const false. They are the identity elements, so

machine-generated IR needs no special cases.

• in: [] matches nothing; negated, it matches everything.
• not over const folds to the opposite const.
• A const false ward or where-set short-circuits: the driver returns an empty result (or affects

zero rows) without a round trip. A const true compiles to nothing.

• Comparisons follow SQL three-valued logic. Client-side ward scoping evaluates the same way, which

M0 verified against live RLS on adversarial null fixtures.

• upsert without conflict is invalid IR and is rejected before it reaches a driver.
• count returns the number of rows matching where AND ward, ignoring select, relations,

order, limit, and offset, which must be absent or empty. It exists so paginate() has a portable total; drivers surface it as QueryResult.count.

• Relation limit applies per parent row, not to the combined related set.

@avelon/supabase compiles that to PostgREST parameters. @avelon/postgres compiles it to parameterized SQL. A MySQL driver would compile it to MySQL. The model layer never learns which.

The IR is serializable on purpose: it can be logged, snapshot-tested, and diffed, which is how you prove two drivers agree on the same query without a live database.

Capabilities that matter

capabilities: {
  transactions: false,        // supabase: use rpc()
  rowSecurity: true,          // wards compile to RLS
  maxRelationDepth: 2,        // PostgREST embed limit
  fullTextSearch: true,
  upsert: true,
  returning: true,
  windowFunctions: false,
  jsonOperators: true,
}

maxRelationDepth is checked by Bailiff against every .with() chain in the codebase, so exceeding the configured driver's limit is a lint error at authoring time rather than an empty array at runtime.

Models

export class Post extends Model {
  static table = 'posts'
  static fillable = ['title', 'body']
  static casts = { published_at: 'datetime', meta: 'json' } as const
  static timestamps = true
  static softDeletes = true

  declare id: string
  declare title: string
  declare body: string
  declare user_id: string
  declare published_at: Date | null

  author() { return this.belongsTo(User, 'user_id') }
  comments() { return this.hasMany(Comment, 'post_id') }

  static published() { return this.query().whereNotNull('published_at') }
}

declare is required rather than stylistic: an emitted class field would overwrite hydrated values during construction.

await Post.published().with('author').latest().paginate(15)
await Post.findOrFail(id)
await Post.query().where('user_id', user.id).where('views', '>', 100).get()
await post.update({ title })
await post.delete()                 // soft delete when enabled
await Post.withTrashed().find(id)

Eager loading issues a second query with a whereIn, never an N+1 loop. On drivers that support embeds it may collapse into one round trip, and that is a driver decision the model layer does not see.

Model lifecycle

Hooks dispatch on the same event bus as application events, which is what makes Observers ordinary listeners:

creating  created  updating  updated  saving  saved  deleting  deleted  restored

A creating listener returning false aborts the write. See 07-events.md.

Types

reeve schema:pull generates database/types.ts from the live schema. Model attribute declarations are checked against it by Bailiff, so a column rename is a type error rather than a runtime surprise in production.

This runs in CI against the migration-applied schema, so a model that drifts from its table fails the build.

Transactions

Driver capability, surfaced by narrowing.

// @avelon/postgres configured
await DB.transaction(async (tx) => {
  const order = await Order.on(tx).create({ ... })
  await Payment.on(tx).create({ order_id: order.id, ... })
})

// @avelon/supabase configured: DB.transaction does not exist.
// Multi-statement atomicity goes through a Postgres function:
await DB.rpc('create_order_with_payment', { ... })

Bailiff errors on hand-rolled "transaction" helpers that wrap sequential writes in a try/catch, since that pattern reads as atomic and is not.

Migrations

Driver-owned. The contract is plan, apply, rollback, status, and reeve migrate is the same command regardless of driver.

@avelon/supabase emits timestamped SQL into a single Supabase-compatible history and shells out to the Supabase CLI, so nothing about the migration workflow is Avelon-specific and an existing project keeps working.

reeve make:migration create_posts_table
reeve migrate
reeve migrate:rollback
reeve migrate:fresh --seed
reeve migrate:status

Ward compilation emits into the same history, so row security travels with the schema rather than living in a dashboard nobody has read since setup. See 06-authorization.md.

A schema builder DSL is deferred, not rejected. See D13.