Required rowsCopy page

A status, a role, or a plan is a row other code assumes is there. You list those rows next to the table. okm migrate apply inserts one when its key is not stored yet. A row that is already stored is left as it is. A row you take off the list is not deleted.

A migration file changes the catalog. An insert in that file is OKM1542: it changes rows outside backfill(). These rows are not a migration step, so that check does not see them. They are not copied into the catalog, and they do not change the catalog hash. Adding a key needs no new DDL file. The next apply inserts it.

An empty database gets them when apply installs the head snapshot. A database that already has history gets them after the pending migrations. provision() does the same inserts on an empty target and records provisioned@ plus the migration id.

Key

key is the field that identifies a row, or several fields. The order is the order of the primary key, the unique constraint, or a unique index that has no predicate. key uses the field name. Snake casing and sqlName are what the insert and the constraint use.

An empty key is invalid: Table statuses reference key is empty. A key that does not match is invalid: Reference key label on statuses is not a primary key or a unique constraint. A table that is not in the head snapshot yet is invalid: Table statuses declares reference rows and is not in the head snapshot. Generate first, so the snapshot includes the table.

one

code is the primary key. open and done are the rows the application looks up.

import { table, t } from "okmodel/pg";

export const statuses = table(
  "statuses",
  {
    code: t.text().primaryKey(),
    label: t.text(),
  },
  {
    reference: {
      key: "code", 
      rows: [
        { code: "open", label: "Open" },
        { code: "done", label: "Done" },
      ],
    },
  },
);

several

The key is kind then code, the same order as primaryKey. ["code", "kind"] does not match that constraint.

import { table, t } from "okmodel/pg";

export const codes = table(
  "codes",
  {
    kind: t.text(),
    code: t.text(),
    label: t.text(),
  },
  {
    primaryKey: ["kind", "code"],
    reference: {
      key: ["kind", "code"], 
      rows: [
        { kind: "status", code: "open", label: "Open" },
        { kind: "status", code: "done", label: "Done" },
      ],
    },
  },
);

Rows

rows is a list. Each item is an object of field names. A value is a string, a number, a boolean, or null. The key fields are required on every row. Other fields are inserted with them.

A value that is not a list is invalid: Table statuses reference rows must be a list. A row that is not an object is invalid: Table statuses reference row 0 is not an object. A missing key is invalid: Table statuses reference row 0 is missing key code. An unknown field is invalid: Table statuses reference names rank, which is not a column. Anything else is invalid: Table statuses reference row 0 column label is not a string, number, boolean, or null.

import { table, t } from "okmodel/pg";

export const plans = table(
  "plans",
  {
    code: t.text().primaryKey(),
    rank: t.integer(),
    active: t.boolean(),
    note: t.text().nullable(),
  },
  {
    reference: {
      key: "code",
      rows: [
        { code: "free", rank: 1, active: true, note: null }, 
        { code: "pro", rank: 2, active: false, note: "paid" },
      ],
    },
  },
);

Apply

Each row is its own insert. A missing key is inserted. A stored key is skipped, including when the label in this list has changed. Nothing is updated or deleted.

insert into "public"."statuses" ("code", "label") values ('open', 'Open') on conflict ("code") do nothing

simple

Two statuses. Apply inserts open and done when those codes are absent.

import { table, t } from "okmodel/pg";

export const statuses = table(
  "statuses",
  {
    code: t.text().primaryKey(),
    label: t.text(),
  },
  {
    reference: {
      key: "code",
      rows: [
        { code: "open", label: "Open" }, 
        { code: "done", label: "Done" },
      ],
    },
  },
);

complex

The key is two columns. The row also stores a number, a boolean, and null. A later apply that adds { kind: "status", code: "archived", ... } inserts that key and leaves open and done as they are.

import { table, t } from "okmodel/pg";

export const codes = table(
  "codes",
  {
    kind: t.text(),
    code: t.text(),
    label: t.text(),
    rank: t.integer(),
    active: t.boolean(),
    note: t.text().nullable(),
  },
  {
    primaryKey: ["kind", "code"],
    reference: {
      key: ["kind", "code"], 
      rows: [
        { kind: "status", code: "open", label: "Open", rank: 1, active: true, note: null },
        { kind: "status", code: "done", label: "Done", rank: 2, active: false, note: null },
      ],
    },
  },
);