Create the Schema and Seed Realistic Data

PRAGMA foreign_keys = ON;CREATE TABLE customer (  customer_id INTEGER PRIMARY KEY,  email TEXT NOT NULL COLLATE NOCASE UNIQUE,  display_name TEXT NOT NULL,  region TEXT NOT NULL CHECK (region IN ('north','south','east','west')),  status TEXT NOT NULL DEFAULT 'active'    CHECK (status IN ('active','suspended','closed')),  created_at TEXT NOT NULL) STRICT;CREATE TABLE address (  address_id INTEGER PRIMARY KEY,  customer_id INTEGER NOT NULL    REFERENCES customer(customer_id) ON DELETE CASCADE,  label TEXT NOT NULL,  city TEXT NOT NULL,  country_code TEXT NOT NULL CHECK (length(country_code) = 2),  is_default INTEGER NOT NULL DEFAULT 0 CHECK (is_default IN (0,1)),  UNIQUE (customer_id, label),  UNIQUE (customer_id, address_id)) STRICT;CREATE UNIQUE INDEX ux_address_one_default  ON address(customer_id) WHERE is_default = 1;CREATE TABLE category (  category_id INTEGER PRIMARY KEY,  parent_category_id INTEGER REFERENCES category(category_id),  category_code TEXT NOT NULL UNIQUE,  category_name TEXT NOT NULL) STRICT;CREATE TABLE product (  product_id INTEGER PRIMARY KEY,  category_id INTEGER NOT NULL REFERENCES category(category_id),  sku TEXT NOT NULL UNIQUE,  product_name TEXT NOT NULL,  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),  active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),  created_at TEXT NOT NULL) STRICT;

CREATE TABLE warehouse (  warehouse_id INTEGER PRIMARY KEY,  warehouse_code TEXT NOT NULL UNIQUE,  warehouse_name TEXT NOT NULL,  city TEXT NOT NULL) STRICT;CREATE TABLE inventory (  warehouse_id INTEGER NOT NULL REFERENCES warehouse(warehouse_id),  product_id INTEGER NOT NULL REFERENCES product(product_id),  on_hand INTEGER NOT NULL CHECK (on_hand >= 0),  reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0),  reorder_point INTEGER NOT NULL DEFAULT 0 CHECK (reorder_point >= 0),  updated_at TEXT NOT NULL,  PRIMARY KEY (warehouse_id, product_id),  CHECK (reserved <= on_hand)) STRICT;CREATE TABLE sales_order (  order_id INTEGER PRIMARY KEY,  order_number TEXT NOT NULL UNIQUE,  customer_id INTEGER NOT NULL REFERENCES customer(customer_id),  shipping_address_id INTEGER NOT NULL,  status TEXT NOT NULL    CHECK (status IN ('draft','submitted','paid','packed','shipped','cancelled')),  currency_code TEXT NOT NULL DEFAULT 'USD' CHECK (length(currency_code) = 3),  ordered_at TEXT NOT NULL,  request_key TEXT NOT NULL UNIQUE,  FOREIGN KEY (customer_id, shipping_address_id)    REFERENCES address(customer_id, address_id)) STRICT;CREATE TABLE order_item (  order_id INTEGER NOT NULL    REFERENCES sales_order(order_id) ON DELETE CASCADE,  line_no INTEGER NOT NULL CHECK (line_no > 0),  product_id INTEGER NOT NULL REFERENCES product(product_id),  quantity INTEGER NOT NULL CHECK (quantity > 0),  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),  line_total_cents INTEGER GENERATED ALWAYS AS    (quantity * unit_price_cents) STORED,  PRIMARY KEY (order_id, line_no),  UNIQUE (order_id, product_id)) STRICT;

CREATE TABLE payment (  payment_id INTEGER PRIMARY KEY,  order_id INTEGER NOT NULL REFERENCES sales_order(order_id),  provider_ref TEXT NOT NULL UNIQUE,  amount_cents INTEGER NOT NULL CHECK (amount_cents > 0),  status TEXT NOT NULL    CHECK (status IN ('authorized','captured','failed','refunded')),  paid_at TEXT) STRICT;CREATE TABLE shipment (  shipment_id INTEGER PRIMARY KEY,  order_id INTEGER NOT NULL REFERENCES sales_order(order_id),  warehouse_id INTEGER NOT NULL REFERENCES warehouse(warehouse_id),  tracking_code TEXT UNIQUE,  status TEXT NOT NULL    CHECK (status IN ('pending','packed','shipped','delivered','returned')),  shipped_at TEXT,  delivered_at TEXT,  CHECK (delivered_at IS NULL OR shipped_at IS NOT NULL)) STRICT;CREATE TABLE audit_event (  event_id INTEGER PRIMARY KEY,  entity_type TEXT NOT NULL,  entity_id TEXT NOT NULL,  action TEXT NOT NULL,  actor TEXT NOT NULL,  occurred_at TEXT NOT NULL,  details TEXT NOT NULL DEFAULT '{}') STRICT;

INSERT INTO customer VALUES  (1,'ada@example.com','Ada Lovelace','north','active','2026-01-10 09:00:00'),  (2,'grace@example.com','Grace Hopper','east','active','2026-02-15 10:30:00'),  (3,'linus@example.com','Linus Torvalds','west','active','2026-03-01 14:20:00'),  (4,'margaret@example.com','Margaret Hamilton','south','active','2026-04-18 08:45:00');INSERT INTO address VALUES  (101,1,'home','Berlin','DE',1),  (102,1,'office','Hamburg','DE',0),  (201,2,'home','Munich','DE',1),  (301,3,'home','Cologne','DE',1),  (401,4,'lab','Frankfurt','DE',1);INSERT INTO category VALUES  (1,NULL,'office','Office Equipment'),  (2,1,'computing','Computing Accessories'),  (3,1,'reference','Reference Materials');INSERT INTO product VALUES  (10,3,'DB-BOOK','Database Design Handbook',4200,1,'2026-01-01'),  (11,3,'SQL-CARD','SQL Reference Cards',1800,1,'2026-01-01'),  (12,2,'USB-HUB','Seven-Port USB Hub',3500,1,'2026-01-05'),  (13,2,'MECH-KB','Mechanical Keyboard',8900,1,'2026-01-05'),  (14,1,'NOTE-A5','A5 Engineering Notebook',900,1,'2026-01-08'),  (15,1,'ARCHIVE','Archived Desk Lamp',6100,0,'2025-12-10');INSERT INTO warehouse VALUES  (1,'BER-01','Berlin Central','Berlin'),  (2,'MUC-01','Munich South','Munich');

INSERT INTO inventory VALUES  (1,10,18,2,5,'2026-08-04 18:00:00'),  (1,11,45,5,12,'2026-08-04 18:00:00'),  (1,12,12,3,4,'2026-08-04 18:00:00'),  (1,13,7,1,3,'2026-08-04 18:00:00'),  (1,14,90,10,25,'2026-08-04 18:00:00'),  (2,10,9,1,4,'2026-08-04 18:00:00'),  (2,11,20,2,8,'2026-08-04 18:00:00'),  (2,12,3,0,3,'2026-08-04 18:00:00'),  (2,13,4,0,2,'2026-08-04 18:00:00'),  (2,14,40,4,15,'2026-08-04 18:00:00');INSERT INTO sales_order VALUES  (1001,'NS-2026-1001',1,101,'paid','USD','2026-05-10 09:15:00','req-1001'),  (1002,'NS-2026-1002',1,102,'submitted','USD','2026-06-03 11:40:00','req-1002'),  (1003,'NS-2026-1003',2,201,'shipped','USD','2026-06-18 13:05:00','req-1003'),  (1004,'NS-2026-1004',3,301,'cancelled','USD','2026-07-02 16:30:00','req-1004'),  (1005,'NS-2026-1005',4,401,'paid','USD','2026-07-22 10:10:00','req-1005'),  (1006,'NS-2026-1006',2,201,'draft','USD','2026-08-04 15:25:00','req-1006');INSERT INTO order_item(order_id,line_no,product_id,quantity,unit_price_cents) VALUES  (1001,1,10,1,4200),(1001,2,11,2,1800),  (1002,1,12,2,3500),(1002,2,14,5,900),  (1003,1,13,1,8900),(1003,2,11,3,1800),  (1004,1,15,1,6100),  (1005,1,10,2,4200),(1005,2,14,10,900),  (1006,1,12,1,3500);

INSERT INTO payment VALUES  (5001,1001,'pay-1001',7800,'captured','2026-05-10 09:17:00'),  (5002,1003,'pay-1003',14300,'captured','2026-06-18 13:08:00'),  (5003,1004,'pay-1004',6100,'refunded','2026-07-02 16:34:00'),  (5004,1005,'pay-1005',17400,'captured','2026-07-22 10:12:00');INSERT INTO shipment VALUES  (7001,1003,2,'TRK-1003','shipped','2026-06-19 08:00:00',NULL),  (7002,1005,1,'TRK-1005','packed',NULL,NULL);INSERT INTO audit_event VALUES  (9001,'sales_order','1001','created','api:checkout','2026-05-10 09:15:00','{"status":"paid"}'),  (9002,'sales_order','1004','cancelled','user:3','2026-07-02 16:35:00','{"reason":"customer request"}'),  (9003,'inventory','1/10','reserved','api:checkout','2026-07-22 10:10:00','{"quantity":2}');

set -euo pipefailrm -f northstar.dbsqlite3 northstar.db < db/001_schema.sqlsqlite3 northstar.db < db/002_seed.sqlsqlite3 northstar.db < db/queries/verification.sqlsqlite3 northstar.db "PRAGMA foreign_key_check;"sqlite3 northstar.db "PRAGMA integrity_check;"

SELECT 'customer_count' AS check_name, COUNT(*) AS actual, 4 AS expectedFROM customerUNION ALLSELECT 'product_count', COUNT(*), 6 FROM productUNION ALLSELECT 'order_count', COUNT(*), 6 FROM sales_orderUNION ALLSELECT 'orphan_order_items', COUNT(*), 0FROM order_item oiLEFT JOIN sales_order so ON so.order_id = oi.order_idWHERE so.order_id IS NULL;SELECT so.order_number,       SUM(oi.line_total_cents) AS calculated_total_cents,       COALESCE(SUM(CASE WHEN p.status = 'captured' THEN p.amount_cents END),0)         AS captured_centsFROM sales_order soJOIN order_item oi ON oi.order_id = so.order_idLEFT JOIN payment p ON p.order_id = so.order_idGROUP BY so.order_id, so.order_numberORDER BY so.order_number;

import sqlite3cases = [    ("duplicate email", "INSERT INTO customer VALUES(9,'ADA@example.com','Duplicate','north','active','2026-08-05')"),    ("negative quantity", "INSERT INTO order_item(order_id,line_no,product_id,quantity,unit_price_cents) VALUES(1001,9,12,-1,3500)"),    ("over-reserved stock", "UPDATE inventory SET reserved=99 WHERE warehouse_id=2 AND product_id=12"),    ("missing parent", "INSERT INTO sales_order VALUES(9999,'NS-X',999,101,'draft','USD','2026-08-05','req-x')"),]with sqlite3.connect('northstar.db') as db:    db.execute('PRAGMA foreign_keys = ON')    for name, statement in cases:        db.execute('SAVEPOINT negative_test')        try:            db.execute(statement)        except sqlite3.IntegrityError:            print(f'PASS: {name}')        else:            raise AssertionError(f'constraint did not reject: {name}')        finally:            db.execute('ROLLBACK TO negative_test')            db.execute('RELEASE negative_test')