Spreadsheet-UX op een echte relationele engine
Deze repository wordt als momentopname gepubliceerd: een clone bevat de volledige inhoud, maar geen commitgeschiedenis.
import { isComputed, slugifyColumnName, type Filter, type Locale, type Sort } from '@wellformed/shared';
import { recordAudit, SYSTEM_ACTOR } from '../engine/audit.js';
import { newId } from '../engine/ids.js';
import {
addColumn,
createTable,
defaultViewName,
reorderColumns,
updateColumn,
type AddColumnInput,
} from '../engine/schema.js';
import { insertRow } from '../engine/mutate.js';
import { invalidateMeta, transact, type Db } from './driver.js';
import { loadMeta } from './meta.js';
/**
* The demo: one small business, built whole.
*
* Where there used to be four templates to choose between, there is one
* workspace to look at — a bike workshop selling, stocking, talking and
* billing, shelved under the sidebar groups (Sales, Inventory, Relations,
* Projects, Team) and deliberately linked *across* them: orders read prices
* from products, stock movements move the same products, projects belong to
* customers, hours multiply by the project's rate. The groups organise the
* sidebar; the relations ignore them — which is exactly the point being made.
*
* Pure data, applied through the same functions the API uses. No demo may
* contain code, because a demo with code in it is a vertical plugin wearing a
* hat — and those are the thing this product has decided not to have.
*
* Built in the workspace's language. A workspace is full of names in one
* language — see the languages note in the README — so a Dutch workspace gets
* Dutch tables, columns, options and rows, not an English demo with a Dutch
* menu around it. Formulas and views are assembled from the same localized
* names, so both editions compile identically.
*/
/** Build the whole demo, or only its tables, views and rules. */
export type DemoMode = 'structure' | 'full';
/** What a workspace-creation request may ask for. */
export type DemoChoice = 'none' | DemoMode;
interface DemoTable {
name: string;
label: string;
/** The label of the column that labels a row. `createTable` makes it. */
display: string;
/** The sidebar shelf this table goes on. */
group: string;
/** Make the display column mandatory, for rows that are worthless unlabelled. */
requireDisplay?: boolean;
/** A numbering pattern for the display column: it fills itself on insert. */
displaySequence?: string;
columns: AddColumnInput[];
rows: (Record<string, unknown> & { id: string })[];
}
interface DemoSpec {
tables: DemoTable[];
/**
* The sidebar order, which is not the build order. Tables are built
* masters-first because a link needs its target to exist, but the first
* table in the sidebar is the first thing a visitor sees — and a visitor
* shown three employees learns nothing this product claims. Orders leads:
* links, a formula, a workflow rule and a saved view, all on one screen.
*/
order: string[];
/**
* Columns added once every table exists — links pointing forwards and the
* rollups that read back across them. Ordering, not a second kind of column.
*/
lateColumns: { table: string; column: AddColumnInput }[];
views: {
table: string;
name: string;
filters: Filter[];
sort: Sort | null;
/** Columns this view leaves out of its SELECT. */
hidden?: string[];
}[];
rules: {
table: string;
column: string;
equals: string;
label: string;
actions: { type: 'set'; column: string; value: string }[];
}[];
}
// eslint-disable-next-line complexity -- one long literal, not logic
function demoSpec(locale: Locale): DemoSpec {
const L = (en: string, nl: string) => (locale === 'nl' ? nl : en);
/* Columns are defined by their label; rows, formulas, filters and rules
address them by identifier. `n` is the same derivation the server applies
when the column is made, so the two can never disagree. */
const n = slugifyColumnName;
/* Every name that appears twice — in a definition and in a formula, filter
or rule — is named exactly once here, so the two can never disagree. */
const T = {
employees: L('employees', 'medewerkers'),
customers: L('customers', 'klanten'),
products: L('products', 'producten'),
orders: 'orders',
suppliers: L('suppliers', 'leveranciers'),
movements: L('movements', 'mutaties'),
contacts: L('contacts', 'contactpersonen'),
conversations: L('conversations', 'gesprekken'),
projects: L('projects', 'projecten'),
tasks: L('tasks', 'taken'),
hours: L('hours', 'uren'),
};
const GROUP = {
sales: L('Sales', 'Verkoop'),
inventory: L('Inventory', 'Voorraad'),
relations: L('Relations', 'Relaties'),
projects: L('Projects', 'Projecten'),
team: L('Team', 'Team'),
};
const NAME = L('Name', 'Naam');
const DATE = L('Date', 'Datum');
const QTY = L('Qty', 'Aantal');
const KIND = L('Kind', 'Soort');
const STATUS = 'Status';
const PRICE = L('Price', 'Prijs');
const TOTAL = L('Total', 'Totaal');
const RATE = L('Hourly rate', 'Uurtarief');
const HOURS = L('Hours', 'Uren');
const AMOUNT = L('Amount', 'Bedrag');
const ON_HAND = L('On hand', 'Op voorraad');
const CUSTOMER = L('Customer', 'Klant');
const PRODUCT = L('Product', 'Product');
const PROJECT = 'Project';
const PAID_ON = L('Paid on', 'Betaald op');
const DONE_ON = L('Done on', 'Klaar op');
const PAID = L('Paid', 'Betaald');
const DONE = L('Done', 'Klaar');
return {
order: [
T.orders,
T.customers,
T.products,
T.movements,
T.suppliers,
T.contacts,
T.conversations,
T.projects,
T.tasks,
T.hours,
T.employees,
],
tables: [
{
name: T.employees,
label: L('Employees', 'Medewerkers'),
display: NAME,
group: GROUP.team,
columns: [
{
label: L('Role', 'Rol'),
type: 'select',
options: [L('Sales', 'Verkoop'), L('Workshop', 'Werkplaats'), L('Finance', 'Financiën')],
},
{ label: L('Started on', 'In dienst sinds'), type: 'date' },
],
rows: [
{ id: 'e1', [n(NAME)]: 'Mara Ilić', [n(L('Role', 'Rol'))]: L('Sales', 'Verkoop'), [n(L('Started on', 'In dienst sinds'))]: '2023-04-12' },
{ id: 'e2', [n(NAME)]: 'Ben Okafor', [n(L('Role', 'Rol'))]: L('Workshop', 'Werkplaats'), [n(L('Started on', 'In dienst sinds'))]: '2024-01-08' },
{ id: 'e3', [n(NAME)]: 'Sofia Reyes', [n(L('Role', 'Rol'))]: L('Finance', 'Financiën'), [n(L('Started on', 'In dienst sinds'))]: '2022-09-01' },
],
},
{
name: T.customers,
label: L('Customers', 'Klanten'),
display: NAME,
group: GROUP.sales,
columns: [
{
label: L('Region', 'Regio'),
type: 'select',
options: [L('North', 'Noord'), L('East', 'Oost'), L('South', 'Zuid'), L('West', 'West')],
},
{ label: L('Email', 'E-mail'), type: 'text' },
],
rows: [
{ id: 'c1', [n(NAME)]: 'Van Dijk & ' + L('Sons', 'Zonen'), [n(L('Region', 'Regio'))]: L('East', 'Oost'), [n(L('Email', 'E-mail'))]: 'info@vandijk.example' },
{ id: 'c2', [n(NAME)]: L('Hotel Meridian', 'Hotel Meridiaan'), [n(L('Region', 'Regio'))]: 'West', [n(L('Email', 'E-mail'))]: 'facility@meridiaan.example' },
{ id: 'c3', [n(NAME)]: 'Pronto ' + L('Deliveries', 'Bezorgdienst'), [n(L('Region', 'Regio'))]: L('South', 'Zuid'), [n(L('Email', 'E-mail'))]: 'fleet@pronto.example' },
{ id: 'c4', [n(NAME)]: L('Greenland Nursery', 'Kwekerij Groenland'), [n(L('Region', 'Regio'))]: L('North', 'Noord'), [n(L('Email', 'E-mail'))]: 'ines@groenland.example' },
],
},
{
// Before products in build order: product rows link to supplier rows,
// and a row can only point at one that already exists.
name: T.suppliers,
label: L('Suppliers', 'Leveranciers'),
display: NAME,
group: GROUP.inventory,
columns: [
{ label: 'Contact', type: 'text' },
{ label: L('Email', 'E-mail'), type: 'text' },
{ label: L('Lead time (days)', 'Levertijd (dagen)'), type: 'number' },
],
rows: [
{ id: 's1', [n(NAME)]: 'Rhein Cycleparts', contact: 'Dana Fenwick', [n(L('Email', 'E-mail'))]: 'orders@rhein.example', [n(L('Lead time (days)', 'Levertijd (dagen)'))]: 14 },
{ id: 's2', [n(NAME)]: 'Batavia Frames', contact: 'Iris Vos', [n(L('Email', 'E-mail'))]: 'sales@batavia.example', [n(L('Lead time (days)', 'Levertijd (dagen)'))]: 21 },
{ id: 's3', [n(NAME)]: 'NoordAccu', contact: 'Tom Oster', [n(L('Email', 'E-mail'))]: 'hello@noordaccu.example', [n(L('Lead time (days)', 'Levertijd (dagen)'))]: 7 },
],
},
{
name: T.products,
label: L('Products', 'Producten'),
display: NAME,
group: GROUP.sales,
columns: [
// Unique: two products with one SKU is the mistake this exists to refuse.
{ label: 'SKU', type: 'text', unique: true },
{ label: PRICE, type: 'currency' },
],
// The supplier link is a late column (suppliers are created after
// products), but rows are inserted later still, so they may carry it.
rows: [
{ id: 'p1', [n(NAME)]: L('Cargo bike', 'Bakfiets'), sku: 'CB-100', [n(PRICE)]: 3450, [n(L('Supplier', 'Leverancier'))]: 's2' },
{ id: 'p2', [n(NAME)]: L('City e-bike', 'Stads-e-bike'), sku: 'EB-210', [n(PRICE)]: 2190, [n(L('Supplier', 'Leverancier'))]: 's2' },
{ id: 'p3', [n(NAME)]: L('Child seat', 'Kinderzitje'), sku: 'AC-031', [n(PRICE)]: 89, [n(L('Supplier', 'Leverancier'))]: 's1' },
{ id: 'p4', [n(NAME)]: L('Spare battery', 'Reserveaccu'), sku: 'AC-115', [n(PRICE)]: 395, [n(L('Supplier', 'Leverancier'))]: 's3' },
{ id: 'p5', [n(NAME)]: L('Service visit', 'Servicebeurt'), sku: 'SV-001', [n(PRICE)]: 95 },
],
},
{
name: T.orders,
label: 'Orders',
display: L('Number', 'Nummer'),
group: GROUP.sales,
requireDisplay: true,
// Self-numbering: a new order names itself, continuing from the
// highest number already in the column.
displaySequence: '#{nnnn}',
columns: [
{ label: CUSTOMER, type: 'link', target: T.customers },
{ label: PRODUCT, type: 'link', target: T.products },
{ label: QTY, type: 'number' },
{ label: TOTAL, type: 'formula', expr: `${n(QTY)} * ${n(PRODUCT)}.${n(PRICE)}`, format: 'currency' },
{ label: L('Owner', 'Verkoper'), type: 'link', target: T.employees },
{
label: STATUS,
type: 'select',
options: [L('Draft', 'Concept'), L('Invoiced', 'Gefactureerd'), PAID, L('Overdue', 'Achterstallig')],
},
{ label: DATE, type: 'date' },
{ label: PAID_ON, type: 'date' },
],
rows: [
{ id: 'o1', [n(L('Number', 'Nummer'))]: '#1041', [n(CUSTOMER)]: 'c1', [n(PRODUCT)]: 'p1', [n(QTY)]: 1, [n(L('Owner', 'Verkoper'))]: 'e1', [n(STATUS)]: PAID, [n(DATE)]: '2026-06-12', [n(PAID_ON)]: '2026-06-30' },
{ id: 'o2', [n(L('Number', 'Nummer'))]: '#1042', [n(CUSTOMER)]: 'c2', [n(PRODUCT)]: 'p2', [n(QTY)]: 2, [n(L('Owner', 'Verkoper'))]: 'e1', [n(STATUS)]: L('Invoiced', 'Gefactureerd'), [n(DATE)]: '2026-07-08' },
{ id: 'o3', [n(L('Number', 'Nummer'))]: '#1043', [n(CUSTOMER)]: 'c3', [n(PRODUCT)]: 'p1', [n(QTY)]: 2, [n(L('Owner', 'Verkoper'))]: 'e2', [n(STATUS)]: L('Overdue', 'Achterstallig'), [n(DATE)]: '2026-06-20' },
{ id: 'o4', [n(L('Number', 'Nummer'))]: '#1044', [n(CUSTOMER)]: 'c1', [n(PRODUCT)]: 'p3', [n(QTY)]: 2, [n(L('Owner', 'Verkoper'))]: 'e2', [n(STATUS)]: PAID, [n(DATE)]: '2026-07-15', [n(PAID_ON)]: '2026-07-28' },
{ id: 'o5', [n(L('Number', 'Nummer'))]: '#1045', [n(CUSTOMER)]: 'c4', [n(PRODUCT)]: 'p4', [n(QTY)]: 1, [n(L('Owner', 'Verkoper'))]: 'e1', [n(STATUS)]: L('Draft', 'Concept'), [n(DATE)]: '2026-08-05' },
{ id: 'o6', [n(L('Number', 'Nummer'))]: '#1046', [n(CUSTOMER)]: 'c2', [n(PRODUCT)]: 'p5', [n(QTY)]: 3, [n(L('Owner', 'Verkoper'))]: 'e2', [n(STATUS)]: L('Invoiced', 'Gefactureerd'), [n(DATE)]: '2026-08-12' },
],
},
{
name: T.movements,
label: L('Stock movements', 'Voorraadmutaties'),
display: 'Ref',
group: GROUP.inventory,
columns: [
{ label: PRODUCT, type: 'link', target: T.products },
{ label: QTY, type: 'number' },
{
label: KIND,
type: 'select',
options: [L('Received', 'Ontvangen'), L('Sold', 'Verkocht'), L('Adjusted', 'Gecorrigeerd')],
},
{ label: DATE, type: 'date' },
],
rows: [
{ id: 'm1', ref: 'IN-001', [n(PRODUCT)]: 'p1', [n(QTY)]: 4, [n(KIND)]: L('Received', 'Ontvangen'), [n(DATE)]: '2026-06-02' },
{ id: 'm2', ref: 'OUT-001', [n(PRODUCT)]: 'p1', [n(QTY)]: -3, [n(KIND)]: L('Sold', 'Verkocht'), [n(DATE)]: '2026-07-01' },
{ id: 'm3', ref: 'IN-002', [n(PRODUCT)]: 'p2', [n(QTY)]: 6, [n(KIND)]: L('Received', 'Ontvangen'), [n(DATE)]: '2026-06-15' },
{ id: 'm4', ref: 'OUT-002', [n(PRODUCT)]: 'p2', [n(QTY)]: -2, [n(KIND)]: L('Sold', 'Verkocht'), [n(DATE)]: '2026-07-10' },
{ id: 'm5', ref: 'IN-003', [n(PRODUCT)]: 'p4', [n(QTY)]: 10, [n(KIND)]: L('Received', 'Ontvangen'), [n(DATE)]: '2026-07-03' },
{ id: 'm6', ref: 'IN-004', [n(PRODUCT)]: 'p3', [n(QTY)]: 8, [n(KIND)]: L('Received', 'Ontvangen'), [n(DATE)]: '2026-06-20' },
{ id: 'm7', ref: 'ADJ-001', [n(PRODUCT)]: 'p3', [n(QTY)]: -1, [n(KIND)]: L('Adjusted', 'Gecorrigeerd'), [n(DATE)]: '2026-07-25' },
],
},
{
name: T.contacts,
label: L('Contacts', 'Contactpersonen'),
display: NAME,
group: GROUP.relations,
columns: [
{ label: CUSTOMER, type: 'link', target: T.customers },
{ label: L('Role', 'Functie'), type: 'text' },
{ label: L('Email', 'E-mail'), type: 'text' },
{ label: L('Phone', 'Telefoon'), type: 'text' },
],
rows: [
{ id: 'ct1', [n(NAME)]: 'Joris van Dijk', [n(CUSTOMER)]: 'c1', [n(L('Role', 'Functie'))]: L('Owner', 'Eigenaar'), [n(L('Email', 'E-mail'))]: 'joris@vandijk.example', [n(L('Phone', 'Telefoon'))]: '+31 20 555 0141' },
{ id: 'ct2', [n(NAME)]: 'Femke Sanders', [n(CUSTOMER)]: 'c2', [n(L('Role', 'Functie'))]: L('Facility manager', 'Facilitair manager'), [n(L('Email', 'E-mail'))]: 'femke@meridiaan.example' },
{ id: 'ct3', [n(NAME)]: 'Marco Bruni', [n(CUSTOMER)]: 'c3', [n(L('Role', 'Functie'))]: L('Fleet lead', 'Wagenparkbeheerder'), [n(L('Email', 'E-mail'))]: 'marco@pronto.example' },
{ id: 'ct4', [n(NAME)]: 'Ines Groen', [n(CUSTOMER)]: 'c4', [n(L('Role', 'Functie'))]: L('Owner', 'Eigenaar'), [n(L('Email', 'E-mail'))]: 'ines@groenland.example' },
],
},
{
name: T.conversations,
label: L('Conversations', 'Gesprekken'),
display: L('Subject', 'Onderwerp'),
group: GROUP.relations,
columns: [
{ label: 'Contact', type: 'link', target: T.contacts },
{ label: CUSTOMER, type: 'link', target: T.customers },
{
label: KIND,
type: 'select',
options: [L('Call', 'Telefoongesprek'), L('Email', 'E-mail'), L('Meeting', 'Afspraak')],
},
{ label: DATE, type: 'date' },
{ label: L('Notes', 'Notities'), type: 'text' },
],
rows: [
{ id: 'g1', [n(L('Subject', 'Onderwerp'))]: L('Quote for two cargo bikes', 'Offerte voor twee bakfietsen'), contact: 'ct1', [n(CUSTOMER)]: 'c1', [n(KIND)]: L('Call', 'Telefoongesprek'), [n(DATE)]: '2026-06-05', [n(L('Notes', 'Notities'))]: L('Wants them before the summer rush.', 'Wil ze vóór de zomerdrukte binnen hebben.') },
{ id: 'g2', [n(L('Subject', 'Onderwerp'))]: L('Winter service plan', 'Winteronderhoudsplan'), contact: 'ct2', [n(CUSTOMER)]: 'c2', [n(KIND)]: L('Meeting', 'Afspraak'), [n(DATE)]: '2026-07-14' },
{ id: 'g3', [n(L('Subject', 'Onderwerp'))]: L('Battery range questions', 'Vragen over accubereik'), contact: 'ct3', [n(CUSTOMER)]: 'c3', [n(KIND)]: L('Email', 'E-mail'), [n(DATE)]: '2026-07-22' },
{ id: 'g4', [n(L('Subject', 'Onderwerp'))]: L('Second child seat', 'Tweede kinderzitje'), contact: 'ct1', [n(CUSTOMER)]: 'c1', [n(KIND)]: L('Email', 'E-mail'), [n(DATE)]: '2026-08-02' },
],
},
{
name: T.projects,
label: L('Projects', 'Projecten'),
display: NAME,
group: GROUP.projects,
columns: [
{ label: CUSTOMER, type: 'link', target: T.customers },
{ label: RATE, type: 'currency' },
{
label: STATUS,
type: 'select',
options: [L('Quoted', 'Offerte'), L('Running', 'Lopend'), L('Done', 'Afgerond')],
},
{ label: L('Starts', 'Start'), type: 'date' },
],
rows: [
{ id: 'pr1', [n(NAME)]: L('Pronto fleet rollout', 'Wagenpark Pronto'), [n(CUSTOMER)]: 'c3', [n(RATE)]: 95, [n(STATUS)]: L('Running', 'Lopend'), [n(L('Starts', 'Start'))]: '2026-07-01' },
{ id: 'pr2', [n(NAME)]: L('Hotel bike programme', 'Hotelfietsprogramma'), [n(CUSTOMER)]: 'c2', [n(RATE)]: 85, [n(STATUS)]: L('Quoted', 'Offerte'), [n(L('Starts', 'Start'))]: '2026-09-01' },
{ id: 'pr3', [n(NAME)]: L('Bakery delivery setup', 'Bezorgopzet Van Dijk'), [n(CUSTOMER)]: 'c1', [n(RATE)]: 95, [n(STATUS)]: L('Done', 'Afgerond'), [n(L('Starts', 'Start'))]: '2026-05-01' },
],
},
{
name: T.tasks,
label: L('Tasks', 'Taken'),
display: L('Title', 'Titel'),
group: GROUP.projects,
columns: [
{ label: PROJECT, type: 'link', target: T.projects },
{ label: L('Owner', 'Eigenaar'), type: 'link', target: T.employees },
{
label: STATUS,
type: 'select',
options: [L('To do', 'Te doen'), L('Doing', 'Bezig'), DONE],
},
{ label: L('Due', 'Deadline'), type: 'date' },
{ label: DONE_ON, type: 'date' },
],
rows: [
{ id: 'tk1', [n(L('Title', 'Titel'))]: L('Survey the routes', 'Routes inventariseren'), [n(PROJECT)]: 'pr1', [n(L('Owner', 'Eigenaar'))]: 'e1', [n(STATUS)]: DONE, [n(L('Due', 'Deadline'))]: '2026-07-10', [n(DONE_ON)]: '2026-07-08' },
{ id: 'tk2', [n(L('Title', 'Titel'))]: L('Fit the trailers', 'Aanhangers monteren'), [n(PROJECT)]: 'pr1', [n(L('Owner', 'Eigenaar'))]: 'e2', [n(STATUS)]: L('Doing', 'Bezig'), [n(L('Due', 'Deadline'))]: '2026-08-20' },
{ id: 'tk3', [n(L('Title', 'Titel'))]: L('Rider training', 'Rijderstraining'), [n(PROJECT)]: 'pr1', [n(L('Owner', 'Eigenaar'))]: 'e1', [n(STATUS)]: L('To do', 'Te doen'), [n(L('Due', 'Deadline'))]: '2026-09-05' },
{ id: 'tk4', [n(L('Title', 'Titel'))]: L('Draft the proposal', 'Voorstel schrijven'), [n(PROJECT)]: 'pr2', [n(L('Owner', 'Eigenaar'))]: 'e1', [n(STATUS)]: L('Doing', 'Bezig'), [n(L('Due', 'Deadline'))]: '2026-08-15' },
{ id: 'tk5', [n(L('Title', 'Titel'))]: L('Handover', 'Oplevering'), [n(PROJECT)]: 'pr3', [n(L('Owner', 'Eigenaar'))]: 'e2', [n(STATUS)]: DONE, [n(L('Due', 'Deadline'))]: '2026-05-28', [n(DONE_ON)]: '2026-05-26' },
],
},
{
name: T.hours,
label: L('Hours', 'Uren'),
display: 'Ref',
group: GROUP.projects,
columns: [
{ label: L('Task', 'Taak'), type: 'link', target: T.tasks },
{ label: PROJECT, type: 'link', target: T.projects },
{ label: L('Person', 'Persoon'), type: 'link', target: T.employees },
{ label: HOURS, type: 'number' },
{ label: AMOUNT, type: 'formula', expr: `${n(HOURS)} * ${n(PROJECT)}.${n(RATE)}`, format: 'currency' },
{ label: DATE, type: 'date' },
],
rows: [
{ id: 'h1', ref: L('H-001', 'U-001'), [n(L('Task', 'Taak'))]: 'tk1', [n(PROJECT)]: 'pr1', [n(L('Person', 'Persoon'))]: 'e1', [n(HOURS)]: 6, [n(DATE)]: '2026-07-05' },
{ id: 'h2', ref: L('H-002', 'U-002'), [n(L('Task', 'Taak'))]: 'tk1', [n(PROJECT)]: 'pr1', [n(L('Person', 'Persoon'))]: 'e1', [n(HOURS)]: 2.5, [n(DATE)]: '2026-07-08' },
{ id: 'h3', ref: L('H-003', 'U-003'), [n(L('Task', 'Taak'))]: 'tk2', [n(PROJECT)]: 'pr1', [n(L('Person', 'Persoon'))]: 'e2', [n(HOURS)]: 8, [n(DATE)]: '2026-08-06' },
{ id: 'h4', ref: L('H-004', 'U-004'), [n(L('Task', 'Taak'))]: 'tk2', [n(PROJECT)]: 'pr1', [n(L('Person', 'Persoon'))]: 'e2', [n(HOURS)]: 5, [n(DATE)]: '2026-08-12' },
{ id: 'h5', ref: L('H-005', 'U-005'), [n(L('Task', 'Taak'))]: 'tk4', [n(PROJECT)]: 'pr2', [n(L('Person', 'Persoon'))]: 'e1', [n(HOURS)]: 3, [n(DATE)]: '2026-08-11' },
{ id: 'h6', ref: L('H-006', 'U-006'), [n(L('Task', 'Taak'))]: 'tk5', [n(PROJECT)]: 'pr3', [n(L('Person', 'Persoon'))]: 'e2', [n(HOURS)]: 4, [n(DATE)]: '2026-05-26' },
],
},
],
lateColumns: [
// Sales reads back across its orders…
{ table: T.customers, column: { label: L('Lifetime value', 'Omzet'), type: 'rollup', expr: `SUM(${T.orders}.${n(TOTAL)})`, format: 'currency' } },
{ table: T.customers, column: { label: L('Order count', 'Aantal orders'), type: 'rollup', expr: `COUNT(${T.orders})`, format: 'number' } },
// …and across Relations, one shelf over.
{ table: T.customers, column: { label: L('Last contact', 'Laatste contact'), type: 'rollup', expr: `MAX(${T.conversations}.${n(DATE)})` } },
// Inventory reaches into Sales: the products being moved are the ones being sold.
{ table: T.products, column: { label: L('Supplier', 'Leverancier'), type: 'link', target: T.suppliers } },
{ table: T.products, column: { label: ON_HAND, type: 'rollup', expr: `SUM(${T.movements}.${n(QTY)})`, format: 'number' } },
{ table: T.products, column: { label: L('Stock value', 'Voorraadwaarde'), type: 'formula', expr: `${n(ON_HAND)} * ${n(PRICE)}`, format: 'currency' } },
{ table: T.suppliers, column: { label: L('Product count', 'Aantal producten'), type: 'rollup', expr: `COUNT(${T.products})`, format: 'number' } },
// Projects bill through their hours; people carry their own total.
{ table: T.projects, column: { label: L('Hours booked', 'Uren geboekt'), type: 'rollup', expr: `SUM(${T.hours}.${n(HOURS)})`, format: 'number' } },
{ table: T.projects, column: { label: L('Billable', 'Factureerbaar'), type: 'rollup', expr: `SUM(${T.hours}.${n(AMOUNT)})`, format: 'currency' } },
{ table: T.employees, column: { label: L('Hours logged', 'Uren geboekt'), type: 'rollup', expr: `SUM(${T.hours}.${n(HOURS)})`, format: 'number' } },
],
views: [
// The not-yet-done views hide their done-date column: on these rows it
// is empty by definition, so the view's SELECT leaves it out entirely.
{ table: T.orders, name: L('Unpaid', 'Nog te betalen'), filters: [{ col: n(STATUS), op: '≠', val: PAID }], sort: { col: n(DATE), dir: 'asc' }, hidden: [n(PAID_ON)] },
{ table: T.products, name: L('Low stock', 'Bijna op'), filters: [{ col: n(ON_HAND), op: '<', val: '5' }], sort: { col: n(ON_HAND), dir: 'asc' } },
{ table: T.tasks, name: L('Still open', 'Nog open'), filters: [{ col: n(STATUS), op: '≠', val: DONE }], sort: { col: n(L('Due', 'Deadline')), dir: 'asc' }, hidden: [n(DONE_ON)] },
{ table: T.projects, name: L('Running', 'Lopend'), filters: [{ col: n(STATUS), op: '=', val: L('Running', 'Lopend') }], sort: { col: n(L('Starts', 'Start')), dir: 'asc' } },
],
rules: [
{
table: T.orders,
column: n(STATUS),
equals: PAID,
label: L(`Status → ${PAID} stamps ${PAID_ON}`, `Status → ${PAID} stempelt ${PAID_ON}`),
actions: [{ type: 'set', column: n(PAID_ON), value: '@today' }],
},
{
table: T.tasks,
column: n(STATUS),
equals: DONE,
label: L(`Status → ${DONE} stamps ${DONE_ON}`, `Status → ${DONE} stempelt ${DONE_ON}`),
actions: [{ type: 'set', column: n(DONE_ON), value: '@today' }],
},
],
};
}
/** How many tables the demo builds, for the plan check before it does. */
export function demoTableCount(): number {
return demoSpec('en').tables.length;
}
/**
* Build the demo into a workspace, through the same schema and row functions
* the HTTP routes use. Nothing here writes SQL of its own, so the demo can
* never produce a table the rest of the product would not accept.
*
* The order matters and is the whole trick: every table and its stored columns
* first, then the links and rollups that reference them, then rows, then views,
* then rules. Rules go last so that seeding does not fire them — a "stamp
* today's date" rule would otherwise overwrite the historical dates that make
* the sample data look like real history.
*/
export function applyDemo(db: Db, options: { locale: Locale; mode: DemoMode }): void {
const spec = demoSpec(options.locale);
const actor = SYSTEM_ACTOR;
// Three passes, because each kind of column can only resolve once the one
// before it exists: a link needs its target table, and a formula needs the
// link it reads across.
for (const table of spec.tables) {
createTable(
db,
{
name: table.name,
label: table.label,
displayLabel: table.display,
group: table.group,
defaultViewName: defaultViewName(table.label, options.locale),
},
actor,
);
}
for (const table of spec.tables) {
for (const column of table.columns) {
if (isComputed(column.type)) continue;
addColumn(db, table.name, column, actor);
}
}
for (const table of spec.tables) {
for (const column of table.columns) {
if (isComputed(column.type)) addColumn(db, table.name, column, actor);
}
}
for (const { table, column } of spec.lateColumns) {
addColumn(db, table, column, actor);
}
// The passes above append in their own order; put the columns back in the
// order the demo declared them, which is the order they read in. The spec
// speaks labels; the catalogue speaks identifiers, derived the same way
// `addColumn` derived them.
const derived = (column: { label?: string; name?: string }) =>
column.name ?? slugifyColumnName(column.label!);
for (const table of spec.tables) {
const late = spec.lateColumns.filter((l) => l.table === table.name).map((l) => derived(l.column));
reorderColumns(
db,
table.name,
[slugifyColumnName(table.display), ...table.columns.map(derived), ...late],
actor,
);
}
for (const table of spec.tables) {
const display = slugifyColumnName(table.display);
if (table.requireDisplay) {
updateColumn(db, table.name, display, { required: true }, actor);
}
if (table.displaySequence) {
updateColumn(db, table.name, display, { sequence: table.displaySequence }, actor);
}
}
if (options.mode === 'full') {
for (const table of spec.tables) {
for (const { id, ...values } of table.rows) {
insertRow(db, loadMeta(db), table.name, values, actor, id);
}
}
}
transact(db, () => {
// The narrative order, not the build order — see the note on `spec.order`.
// Written directly, like the views and rules below, so seeding does not
// fill the audit trail with its own scaffolding.
const place = db.sqlite.prepare('UPDATE _wellformed_table SET position = ? WHERE name = ?');
spec.order.forEach((name, index) => place.run(index, name));
const view = db.sqlite.prepare(
'INSERT INTO _wellformed_view (id, table_name, name, filters, sort, hidden, position, system) VALUES (?, ?, ?, ?, ?, ?, ?, 0)',
);
spec.views.forEach((v, index) => {
view.run(
newId('vw'),
v.table,
v.name,
JSON.stringify(v.filters),
JSON.stringify(v.sort),
JSON.stringify(v.hidden ?? []),
index + 1,
);
});
const rule = db.sqlite.prepare(
'INSERT INTO _wellformed_rule (id, table_name, column_name, equals, label, actions, enabled) VALUES (?, ?, ?, ?, ?, ?, 1)',
);
for (const r of spec.rules) {
rule.run(newId('rl'), r.table, r.column, r.equals, r.label, JSON.stringify(r.actions));
}
});
invalidateMeta(db);
recordAudit(db, actor, {
kind: 'schema',
nw: options.mode === 'full' ? 'built the demo workspace' : 'built the demo structure',
});
}