Shared commerce schema

The documentation, runnable applications, and playground use one commerce model. Query recipes build on these tables rather than introducing unrelated databases.

Table	Purpose and relationships
`users`	Email, display name, account status, profile JSON, and creation time
`teams`	Team names and unique slugs
`memberships`	Users belong to teams with an owner, admin, or member role
`products`	SKU, title, integer price in cents, availability, and optional JSON metadata
`orders`	A user's order, status, total in cents, creation time, and optional shipping time
`order_items`	Products, quantities, and unit prices belonging to an order
`audit_events`	Event names and JSON payloads, optionally associated with a user

PostgreSQL declares account_status, order_status, and membership_role as enum types. MySQL uses column enums. SQLite uses text with CHECK constraints; its inferred types remain string.

The schemas include defaults for tutorial inserts and deterministic sample rows. Monetary amounts are integer cents. PostgreSQL and MySQL infer those integer columns as number; SQLite's default integer policy uses bigint. PostgreSQL timestamp columns and MySQL datetime columns use Date, while SQLite stores timestamps as text. JSON inference and decoding follow each grammar's type mappings.

PostgreSQL

sql

CREATE TYPE account_status AS ENUM ('active', 'suspended');
CREATE TYPE order_status AS ENUM ('draft', 'paid', 'shipped', 'cancelled');
CREATE TYPE membership_role AS ENUM ('owner', 'admin', 'member');

CREATE TABLE users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  display_name text NOT NULL DEFAULT '',
  status account_status NOT NULL DEFAULT 'active',
  profile jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE teams (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  slug text NOT NULL UNIQUE
);

CREATE TABLE memberships (
  user_id bigint NOT NULL REFERENCES users(id),
  team_id bigint NOT NULL REFERENCES teams(id),
  role membership_role NOT NULL,
  PRIMARY KEY (user_id, team_id)
);

CREATE TABLE products (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  title text NOT NULL,
  price_cents integer NOT NULL,
  active boolean NOT NULL,
  metadata jsonb
);

CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  status order_status NOT NULL,
  total_cents integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  shipped_at timestamptz
);

CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders(id),
  product_id bigint NOT NULL REFERENCES products(id),
  quantity integer NOT NULL,
  unit_price_cents integer NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE audit_events (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id bigint REFERENCES users(id),
  event_name text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (email, status, display_name) VALUES
  ('alice@example.com', 'active', 'Alice'),
  ('bob@example.com', 'suspended', 'Bob');

INSERT INTO teams (name, slug) VALUES ('Studio', 'studio');
INSERT INTO memberships (user_id, team_id, role) VALUES (1, 1, 'owner');
INSERT INTO products (sku, title, price_cents, active) VALUES ('DESK-01', 'Studio desk', 12500, true);
INSERT INTO orders (user_id, status, total_cents) VALUES (1, 'paid', 12500);
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES (1, 1, 1, 12500);

MySQL

sql

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  display_name VARCHAR(255) NOT NULL DEFAULT '',
  status ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
  profile JSON NOT NULL DEFAULT (JSON_OBJECT()),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE teams (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE memberships (
  user_id BIGINT UNSIGNED NOT NULL,
  team_id BIGINT UNSIGNED NOT NULL,
  role ENUM('owner', 'admin', 'member') NOT NULL,
  PRIMARY KEY (user_id, team_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (team_id) REFERENCES teams(id)
);

CREATE TABLE products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(255) NOT NULL UNIQUE,
  title TEXT NOT NULL,
  price_cents integer NOT NULL,
  active boolean NOT NULL,
  metadata JSON
);

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('draft', 'paid', 'shipped', 'cancelled') NOT NULL,
  total_cents integer NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  shipped_at DATETIME,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity integer NOT NULL,
  unit_price_cents integer NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE audit_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED,
  event_name TEXT NOT NULL,
  payload JSON NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (id, email, status, display_name) VALUES
  (1, 'alice@example.com', 'active', 'Alice'),
  (2, 'bob@example.com', 'suspended', 'Bob');

INSERT INTO teams (id, name, slug) VALUES (1, 'Studio', 'studio');
INSERT INTO memberships (user_id, team_id, role) VALUES (1, 1, 'owner');
INSERT INTO products (id, sku, title, price_cents, active) VALUES (1, 'DESK-01', 'Studio desk', 12500, true);
INSERT INTO orders (id, user_id, status, total_cents) VALUES (1, 1, 'paid', 12500);
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES (1, 1, 1, 12500);

SQLite

sql

PRAGMA foreign_keys = ON;

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL DEFAULT '',
  status TEXT CHECK (status IN ('active', 'suspended')) NOT NULL DEFAULT 'active',
  profile TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

CREATE TABLE teams (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE memberships (
  user_id INTEGER NOT NULL REFERENCES users(id),
  team_id INTEGER NOT NULL REFERENCES teams(id),
  role TEXT CHECK (role IN ('owner', 'admin', 'member')) NOT NULL,
  PRIMARY KEY (user_id, team_id)
) STRICT;

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  active INTEGER NOT NULL,
  metadata TEXT
) STRICT;

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  status TEXT CHECK (status IN ('draft', 'paid', 'shipped', 'cancelled')) NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  shipped_at TEXT
) STRICT;

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
) STRICT;

CREATE TABLE audit_events (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  event_name TEXT NOT NULL,
  payload TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

INSERT INTO users (id, email, status, display_name) VALUES
  (1, 'alice@example.com', 'active', 'Alice'),
  (2, 'bob@example.com', 'suspended', 'Bob');

INSERT INTO teams (id, name, slug) VALUES (1, 'Studio', 'studio');
INSERT INTO memberships (user_id, team_id, role) VALUES (1, 1, 'owner');
INSERT INTO products (id, sku, title, price_cents, active) VALUES (1, 'DESK-01', 'Studio desk', 12500, 1);
INSERT INTO orders (id, user_id, status, total_cents) VALUES (1, 1, 'paid', 12500);
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES (1, 1, 1, 12500);

SQLite enables foreign keys for each connection. The schema uses strict tables and stores JSON as text.

Shared SQL functions

The PostgreSQL and MySQL playground workspaces also include schema/functions.sql, which declares display_email(text) using lower(). Imported SQL queries and TypeScript templates share that signature. SQLite uses the built-in lower() function because application-defined functions are registered by its connection, rather than declared with CREATE FUNCTION.

Explore the queries

The playground opens this workspace by default, including imported SQL CTEs, tagged templates, nullable joins, mapped and fixed inserts, JSON documents, audit writes, batches, and transactions. Switch dialects to inspect native syntax and parameter types. MySQL writes use execute() because it has no PostgreSQL-style RETURNING; SQLite JSON writes explicitly serialize their payloads.

The PostgreSQL, MySQL, and SQLite runnable applications exercise the same tables through their application-owned adapters.

Shared commerce schema ​

PostgreSQL ​

MySQL ​

SQLite ​

Shared SQL functions ​

Explore the queries ​