SurrealDB
SurrealDB Docs Logo

Enter a search query

Sample schemas by industry

This page contains a number of sample schemas, each about 50 lines in length, that can be used to get started on a schema of your own for your own industry.

Have a request for an industry not yet in this page? Get in touch and let us know.

Adding to this page

Have a sample schema of your own that you’d like to add? If it’s about 50 lines in length then feel free to make a PR and we’ll credit the addition with a link to your profile on a code hosting platform (e.g. GitHub, GitLab, Codeberg).

Energy and manufacturing

Project planning

A comprehensive project management schema that demonstrates activity scheduling, milestone tracking, and dependency management using graph relationships. This schema shows how to model complex project workflows with interdependent tasks and progress tracking using futures for calculated fields.

-- Activities in a project schedule DEFINE TABLE activity SCHEMAFULL; DEFINE FIELD name ON activity TYPE string; DEFINE FIELD description ON activity TYPE option<string>; DEFINE FIELD start ON activity TYPE datetime; DEFINE FIELD end ON activity TYPE datetime; DEFINE FIELD duration ON activity VALUE <future> { end - start }; DEFINE FIELD progress ON activity TYPE float ASSERT $value IN 0.0..=1.0; DEFINE FIELD assigned_to ON activity TYPE option<record<employee>>; DEFINE FIELD followed_by ON activity VALUE <future> { <-depends_on<-activity }; -- Milestones DEFINE TABLE milestone SCHEMAFULL; DEFINE FIELD project ON milestone TYPE record<project>; DEFINE FIELD activities ON milestone TYPE array<record<activity>>; DEFINE FIELD name ON milestone TYPE string; DEFINE FIELD last_updated ON milestone VALUE time::now(); DEFINE FIELD progress ON milestone VALUE <future> { math::mean(activities.progress) }; DEFINE FIELD is_complete ON milestone VALUE <future> { activities.all(|$a| $a.progress > 0.95) }; -- Graph-style dependency links DEFINE TABLE depends_on SCHEMAFULL TYPE RELATION IN activity OUT activity; DEFINE TABLE activity_of SCHEMAFULL TYPE RELATION IN activity OUT project; CREATE project:one; CREATE activity:one SET name = "Project kickoff", start = time::now(), end = time::now() + 2d, progress = 1.0, project = project:one; CREATE activity:two SET name = "Pour concrete", start = time::now() + 90d, end = time::now() + 100d, progress = 0.0, project = project:one; CREATE activity:three SET name = "Dry concrete", start = time::now() + 100d, end = time::now() + 107d, progress = 0.0, project = project:two; CREATE activity:four SET name = "Build on top of concrete", start = time::now() + 107d, end = time::now() + 150d, progress = 0.0, project = project:two; RELATE activity:two->depends_on->activity:one; RELATE activity:three->depends_on->activity:two; RELATE activity:four->depends_on->activity:three; RELATE [activity:one,activity:two,activity:three, activity:four]->activity_of->project:one; CREATE milestone:one SET project = project:one, activities = [activity:one], name = "Project start"; CREATE milestone:two SET project = project:one, activities = [activity:two, activity:three, activity:four], name = "Initial construction"; -- See all graph connections between activity and project records SELECT *, ->?, <-? FROM activity, project; -- View the current milestones SELECT * FROM milestone;

SCADA (Oil and Gas)

Industrial monitoring and control system schema for oil and gas operations. Demonstrates real-time sensor data collection, automated alert generation using events, and time-series data management with composite keys. Shows how to handle flexible external data integration and live query monitoring.

DEFINE TABLE sensor SCHEMAFULL; DEFINE FIELD type ON sensor TYPE array<string> ASSERT $value ALLINSIDE ["pressure", "temperature", "flow", "level"]; DEFINE FIELD location ON sensor TYPE point; DEFINE TABLE reading SCHEMAFULL; DEFINE FIELD id ON reading TYPE [record<sensor>, datetime]; DEFINE FIELD pressure ON reading TYPE float; -- Optional telemetry values DEFINE FIELD temperature ON reading TYPE option<float>; DEFINE FIELD humidity ON reading TYPE option<float>; -- Flexible object for weather or external data DEFINE FIELD weather ON reading TYPE option<object> FLEXIBLE; DEFINE TABLE alert SCHEMAFULL; DEFINE FIELD equipment ON alert TYPE record<sensor>; DEFINE FIELD severity ON alert TYPE string ASSERT $value IN ["critical", "high", "medium", "low", "info"]; DEFINE FIELD message ON alert TYPE string; DEFINE FIELD triggered_at ON alert TYPE datetime; -- Create a sensor CREATE sensor:one SET type = ["temperature", "pressure"], location = (50.0, 50.0); -- And a reading for the sensor CREATE reading:[sensor:one, time::now()] SET pressure = 600, // JSON object sourced from somewhere else, `weather` field is FLEXIBLE so // can be any object format weather = { "temperature": 17.4, "humidity": 52.0, "wind_speed": 12.8 }; -- Set up event to generate alerts DEFINE EVENT alert_from_create ON reading WHEN $event = "CREATE" THEN { LET $source = $after.id[0]; LET $time = $after.id[1]; -- Select everything over the past 15 minutes up to but not including the present reading LET $recents_average = math::mean(SELECT VALUE pressure FROM reading:[$source, $time - 15m]..[$source, $time]); LET $drop = $recents_average - $after.pressure; IF $drop { CREATE alert SET equipment = $source, severity = "high", message = "Pressure drop over 15 PSI: drop of " + <string>$drop, triggered_at = time::now(); }; }; -- Some readings with good values FOR $_ IN 0..10 { -- Sleep to keep timestamp in IDs unique, consider a ULID instead if timestamps may not be unique sleep(10ns); CREATE reading:[sensor:one, time::now()] SET pressure = 600; }; -- Pressure has suddenly dropped CREATE reading:[sensor:one, time::now()] SET pressure = 500; -- See the alert SELECT * FROM alert; -- Or use a LIVE SELECT for alerts: https://surrealdb.com/docs/surrealql/statements/live LIVE SELECT * FROM alert;

Risk management

Project risk assessment and mitigation tracking schema. Features temporal risk modeling with active/inactive periods, probability-impact calculations, and automated risk scoring using futures. Demonstrates unique constraints and complex mathematical aggregations across related records.

DEFINE TABLE risk SCHEMAFULL; DEFINE FIELD project ON risk TYPE record<project>; DEFINE FIELD description ON risk TYPE string; DEFINE FIELD category ON risk TYPE string; -- e.g. "technical", "commercial", "regulatory" DEFINE FIELD likelihood ON risk TYPE float ASSERT $value IN 0.0..=1.0; DEFINE FIELD maximum_impact ON risk TYPE int; -- in dollars, etc. DEFINE FIELD start ON risk TYPE datetime; DEFINE FIELD end ON risk TYPE datetime; -- Use a future to calculate value on each SELECT DEFINE FIELD active ON risk VALUE <future> { time::now() IN start..=end }; -- Ensure no duplicate `risk` records exist for each project DEFINE INDEX risk_name ON risk FIELDS project, description UNIQUE; -- See all total_impact DEFINE FIELD total_risk_impact ON project VALUE <future> { math::sum((SELECT * FROM risk WHERE project = $parent.id).map(|$risk| $risk.maximum_impact * $risk.likelihood)) }; -- See risks at the current date DEFINE FIELD current_risk_impact ON project VALUE <future> { math::sum((SELECT * FROM risk WHERE project = $parent.id).filter(|$r| $r.active).map(|$risk| $risk.maximum_impact * $risk.likelihood)) }; CREATE project:one; CREATE risk SET project = project:one, description = "Migratory elk", category = "regulatory", likelihood = 0.9, start = d'2025-10-01', end = d'2025-12-15', maximum_impact = 1000000; CREATE risk SET project = project:one, description = "Wildfires", category = "technical", likelihood = 0.5, start = d'2025-06-01', end =d'2025-10-15', maximum_impact = 10000000; SELECT * FROM project;

Supply chain and contract management

Vendor relationship and contract lifecycle management schema. Covers contract value tracking with change orders, deliverable management, and automated total commitment calculations using futures. Demonstrates complex financial calculations and status tracking across multiple related entities.

-- Vendors who supply goods or services DEFINE TABLE vendor SCHEMAFULL; DEFINE FIELD name ON vendor TYPE string; -- Contracts awarded under a project DEFINE TABLE contract SCHEMAFULL; DEFINE FIELD project ON contract TYPE record<project>; DEFINE FIELD vendor ON contract TYPE record<vendor>; DEFINE FIELD title ON contract TYPE string; DEFINE FIELD original_value ON contract TYPE int; DEFINE FIELD total_value ON contract VALUE <future> { original_value + math::sum(SELECT VALUE amount FROM change_order WHERE contract = $parent.id) }; DEFINE FIELD currency ON contract TYPE "dollars" | "euro"; DEFINE FIELD start ON contract TYPE datetime; DEFINE FIELD end ON contract TYPE datetime; -- Deliverables expected under a contract DEFINE TABLE deliverable SCHEMAFULL; DEFINE FIELD contract ON deliverable TYPE record<contract>; DEFINE FIELD description ON deliverable TYPE string; DEFINE FIELD due_date ON deliverable TYPE datetime; DEFINE FIELD received ON deliverable TYPE option<datetime>; DEFINE FIELD status ON deliverable VALUE <future> { IF $parent.received { "complete" } ELSE { "pending" } }; -- Change orders during a project DEFINE TABLE change_order SCHEMAFULL; DEFINE FIELD contract ON change_order TYPE record<contract>; DEFINE FIELD amount ON change_order TYPE int; DEFINE FIELD description ON change_order TYPE string; DEFINE FIELD signed_on ON change_order TYPE option<datetime>; -- Total committed value of a project (sum of all contract values) DEFINE FIELD total_commitment ON project VALUE <future> { math::sum((SELECT VALUE value FROM contract WHERE project = $parent.id)) }; CREATE project:one; CREATE vendor:one SET name = "Good vendor"; CREATE contract:one SET project = project:one, currency = "euro", start = d'2025-12-01', end = d'2026-01-01', original_value = 1000, title = "Services for so-and-so project", vendor = vendor:one; CREATE change_order SET contract = contract:one, amount = 500, description = "Highway wasn't set up yet"; SELECT * FROM contract;

HSSE (Health, Safety, Security, Environment) incidents

Incident reporting and investigation schema using graph relationships. Models safety events as edges between employees and projects with severity classification and role identification. Demonstrates graph-style data modeling for complex incident tracking and analysis.

-- Projects and employees (nodes) DEFINE TABLE project SCHEMAFULL; DEFINE TABLE employee SCHEMAFULL; -- Edges: incident links employee → project DEFINE TABLE incident SCHEMAFULL TYPE RELATION IN project OUT employee; DEFINE FIELD severity ON incident TYPE string ASSERT $value IN ["minor", "moderate", "major", "fatal"]; DEFINE FIELD type ON incident TYPE string ASSERT $value IN ["safety", "environment", "security", "health"]; DEFINE FIELD description ON incident TYPE string; DEFINE FIELD occurred_at ON incident TYPE datetime; DEFINE FIELD role ON incident TYPE string ASSERT $value IN ["witness", "injured", "involved"]; -- Create nodes CREATE employee:one SET name = "John Doe"; CREATE employee:two SET name = "Sally Lee"; CREATE project:one SET name = "Pad 3 Expansion"; -- Create incidents as edges with properties RELATE employee:one->incident->project:one SET severity = "moderate", type = "safety", description = "Pinched hand during pipe fitting", occurred_at = time::now() - 5d, role = "injured"; RELATE employee:two->incident->project:one SET severity = "moderate", type = "safety", description = "Pinched hand during pipe fitting", occurred_at = time::now() - 5d, role = "witness"; SELECT id, <-incident[WHERE severity = "moderate"]<-employee FROM project;

Finance

General bank schema (graph schema)

Multi-currency banking system using graph relationships. Demonstrates polymorphic account types (JPY, EUR, CAD, USD) with different field structures, customer-bank relationships, and unique constraint enforcement. Shows how to model complex financial relationships with type-specific behaviors.

DEFINE TABLE bank SCHEMAFULL; DEFINE FIELD name ON bank TYPE string; DEFINE TABLE customer SCHEMAFULL; DEFINE FIELD name ON customer TYPE string; DEFINE TABLE customer_of TYPE RELATION IN customer OUT bank; DEFINE FIELD since ON customer_of TYPE datetime VALUE time::now() READONLY; DEFINE TABLE jpy SCHEMAFULL; DEFINE FIELD amount ON jpy TYPE int DEFAULT 0; DEFINE FIELD cent ON jpy TYPE int READONLY VALUE 0; DEFINE TABLE eur SCHEMAFULL; DEFINE FIELD amount ON eur TYPE int DEFAULT 0; DEFINE FIELD cent ON eur TYPE int ASSERT $value IN 0..=99; DEFINE FIELD total ON eur VALUE amount + (<float>$parent.cent / 100); DEFINE TABLE cad SCHEMAFULL; DEFINE FIELD amount ON cad TYPE int DEFAULT 0; DEFINE FIELD cent ON cad TYPE int ASSERT $value IN 0..=99; DEFINE FIELD total ON cad VALUE $parent.amount + (<float>$parent.cent / 100); DEFINE TABLE usd SCHEMAFULL; DEFINE FIELD amount ON usd TYPE int DEFAULT 0; DEFINE FIELD cent ON usd TYPE int ASSERT $value IN 0..=99; DEFINE FIELD total ON usd VALUE $parent.amount + (<float>$parent.cent / 100); DEFINE TABLE account TYPE RELATION IN customer OUT jpy|eur|cad|usd; DEFINE FIELD since ON account TYPE datetime VALUE time::now() READONLY; -- stop the same customer opening two wallets in the same currency DEFINE INDEX unique_wallet ON account FIELDS in, out UNIQUE; CREATE bank:one SET name = "Central Bank"; CREATE customer:one SET name = "Billy"; RELATE customer:one->customer_of->bank:one; RELATE customer:one->account->(CREATE ONLY eur SET amount = 100, cent = 50); RELATE customer:one->account->(CREATE ONLY jpy SET amount = 10000); SELECT ->account->eur.total FROM customer:one;

Other bank-customer schema

Traditional bank-customer schema with advanced features including record references, automated cent handling through events, and historical interest rate tracking. Demonstrates event-driven data validation, parameter usage, and complex relationship management with reference fields.

DEFINE PARAM $CURRENCIES VALUE ["EUR", "JPY", "USD", "CAD"]; DEFINE TABLE account SCHEMAFULL; DEFINE FIELD customer ON account TYPE record<customer> REFERENCE; DEFINE FIELD currency ON account TYPE string ASSERT $value IN $CURRENCIES; DEFINE FIELD amount ON account TYPE int; DEFINE FIELD cent ON account TYPE option<int>; DEFINE TABLE bank SCHEMAFULL; DEFINE FIELD name ON bank TYPE string; DEFINE FIELD code ON bank TYPE string; -- e.g., BIC or internal short code DEFINE FIELD swift ON bank TYPE option<string>; DEFINE FIELD supported_currencies ON bank TYPE array<string> ASSERT $value ALLINSIDE $CURRENCIES; DEFINE FIELD interest_rate ON bank TYPE float DEFAULT 0.0; DEFINE FIELD historical_interest_rates ON bank TYPE array<{ rate: float, set_at: datetime }> DEFAULT []; DEFINE FIELD customers ON bank TYPE references<customer>; DEFINE TABLE customer SCHEMAFULL; DEFINE FIELD bank ON customer TYPE record<bank> REFERENCE; -- No assert for cent field, but event to update when > 100 or < 0 DEFINE EVENT update_cents ON account WHEN $event = "UPDATE" THEN { IF cent > 99 { UPDATE $after SET cent -= 100, amount += 1; } ELSE IF cent < 0 { UPDATE $after SET cent += 100, amount -= 1; } }; -- No assert for cent field, but event to update when > 100 or < 0 DEFINE EVENT update_interest_rate ON bank WHEN $event = "UPDATE" THEN { IF $before.interest_rate != $after.interest_rate { UPDATE $this SET historical_interest_rates += { rate: $after.interest_rate, set_at: time::now() }; } }; CREATE bank:one SET name = "Bank of One", code = "ONEBANK", supported_currencies = ["EUR", "JPY"]; UPDATE bank:one SET interest_rate = 5.0; CREATE customer:one SET bank = bank:one; CREATE account:one SET customer = customer:one, bank = bank:one, currency = "JPY", amount = 10000; SELECT * FROM bank FETCH customers;

Customers and money transfers

Secure money transfer system with credit-based limits and transaction logging. Features custom functions for atomic transfers, credit level enforcement, and comprehensive audit trails. Demonstrates transaction safety, business rule enforcement, and financial data integrity.

DEFINE TABLE customer SCHEMAFULL; -- trusted customers can have greater negative amounts DEFINE FIELD amount ON customer ASSERT $value >= -1000 * credit_level; DEFINE FIELD credit_level ON customer TYPE int ASSERT $value IN 0..=5; -- Logs for money transfers DEFINE TABLE transfer SCHEMAFULL; DEFINE FIELD from ON transfer TYPE record<customer>; DEFINE FIELD to ON transfer TYPE record<customer>; DEFINE FIELD amount ON transfer TYPE int; DEFINE FIELD ts ON transfer TYPE datetime DEFAULT time::now(); DEFINE FUNCTION fn::send_money($from: record<customer>, $to: record<customer>, $amount: int) { -- Use manual transaction for all statements so all changes are rolled back -- if something is wrong BEGIN; If $amount < 1 { THROW "Can't send less than 1 "; }; UPDATE $from SET amount -= $amount; UPDATE $to SET amount += $amount; CREATE transfer SET from = $from, to = $to, amount = $amount; COMMIT; }; CREATE customer:one SET amount = 100, credit_level = 0; CREATE customer:two SET amount = 500, credit_level = 5; -- customer:one has bad credit, can't be negative fn::send_money(customer:one, customer:two, 500); -- but customer:two can fn::send_money(customer:two, customer:one, 1000); SELECT * FROM customer; SELECT * FROM transfer;

Loans and repayments

Loan management system with automated interest calculations and repayment scheduling. Features parameterized loan terms, mathematical payment calculations using custom functions, and status tracking. Demonstrates complex financial formulas, temporal data management, and regulatory compliance constraints.

-- Some government-set maximum term for loans DEFINE PARAM $MAX_TERM VALUE 84; DEFINE TABLE loan SCHEMAFULL; DEFINE FIELD customer ON loan TYPE record<customer>; DEFINE FIELD principal ON loan TYPE int; -- Total borrowed, in cents DEFINE FIELD interest_rate ON loan TYPE float; -- e.g., 5.5 for 5.5% DEFINE FIELD issued_at ON loan TYPE datetime; -- loans issuable at units of 6 months each DEFINE FIELD term_months ON loan TYPE int ASSERT $value % 6 = 0 AND $value <= $MAX_TERM; DEFINE FIELD balance ON loan TYPE int; -- Remaining amount to repay DEFINE FIELD status ON loan TYPE string ASSERT $value IN ["active", "paid", "defaulted"]; DEFINE TABLE repayment SCHEMAFULL; DEFINE FIELD loan ON repayment TYPE record<loan>; DEFINE FIELD due_date ON repayment TYPE datetime; DEFINE FIELD amount ON repayment TYPE int; DEFINE FIELD paid ON repayment TYPE bool DEFAULT false; DEFINE FIELD paid_at ON repayment TYPE option<datetime>; DEFINE FIELD method ON repayment TYPE option<string>; -- e.g., "auto", "manual" FOR $loan IN SELECT * FROM loan { LET $update_rate = 1 + ($loan.interest_rate / 365); UPDATE $loan SET amount = amount * $update_rate; }; DEFINE FUNCTION fn::repayment_amount($loan: record<loan>) { LET $P = $loan.principal; LET $annual = $loan.interest_rate / 100; LET $r = $annual / 12; -- Monthly interest rate LET $n = $loan.term_months; math::round( $P * $r / (1 - math::pow(1 + $r, -$n)) ); }; CREATE customer:one; CREATE loan:one SET customer = customer:one, principal = 5000000, interest_rate = 5.0, issued_at = time::now(), term_months = 12, balance = 5000000, status = "active"; UPDATE loan:one SET balance -= fn::repayment_amount(loan:one);

Fraud prevention patterns

Anti-fraud detection system using events and temporal analysis. Implements velocity checks, new account restrictions, and suspicious transaction pattern detection. Demonstrates real-time fraud prevention, temporal constraints, and complex business rule enforcement through database events.

DEFINE FIELD created_at ON account VALUE time::now() READONLY; DEFINE EVENT cancel_high_volume ON TABLE sends WHEN $event = "CREATE" THEN { IF $after.amount > 1000 AND time::now() - $after.in.created_at < 1d { THROW "New accounts can only send up to $1000 per transaction"; } }; DEFINE FIELD sent_at ON TABLE sends VALUE time::now() READONLY; DEFINE EVENT cancel_high_volume ON TABLE sends WHEN $event = "CREATE" THEN { LET $sender = $after.in; LET $receiver = $after.out; -- Disallow more than two transactions within a 5 minute period LET $recents = $sender->sends[WHERE out = $receiver] .filter(|$tx| time::now() - $tx.sent_at < 5m); IF $recents.len() > 2 { THROW "Can't send that many times within a short period of time"; }; };

Using Surrealist’s graph visualization to see fraudulent activities

For more on these queries and their visual output, see this dedicated blog post.

Star pattern: one card used to pay large number of accounts:

DEFINE FIELD paid_at ON pays DEFAULT time::now(); -- sketchy cards FOR $card IN CREATE |card:10| { FOR $_ IN 0..rand::int(5, 15) { LET $payee = UPSERT ONLY account; RELATE $card->pays->$payee SET amount = rand::int(100, 1000); }; }; -- regular card CREATE card:normal; FOR $_ IN 0..rand::int(5, 15) { LET $payee = UPSERT ONLY account; RELATE card:normal->pays->$payee SET amount = rand::int(100, 1000), paid_at = time::now() - rand::duration(1d, 100d); }; SELECT id, ->pays.filter(|$payment| time::now() - $payment.paid_at < 1d).out FROM card;

Tight communities that interact mostly among themselves:

-- Regular community of 200 CREATE |account:200|; -- Smaller community that interacts among itself CREATE |account:5| SET is_sketchy = true; -- The sketchy community interacts only between itself -- the regular community has more general interactions -- and sometimes sends money to the sketchy accounts FOR $account IN SELECT * FROM account { FOR $_ IN 0..10 { LET $counterpart = IF $account.is_sketchy { rand::enum(SELECT * FROM account WHERE is_sketchy) } ELSE { rand::enum(SELECT * FROM account) }; RELATE $account->sends_to->$counterpart SET amount = rand::int(100, 1000); } }; SELECT id, ->sends_to->account FROM account;

Circles showing loops of money returning to its origin:

CREATE |account:50|; CREATE |account:1..15| SET is_sketchy = true; FOR $sketchy IN SELECT * FROM account WHERE is_sketchy { LET $counterpart = rand::enum(SELECT * FROM account WHERE is_sketchy AND !<-sent); RELATE $sketchy->sent->$counterpart SET amount = rand::int(100, 1000); }; LET $normal = SELECT * FROM account WHERE !is_sketchy; FOR $account IN SELECT * FROM account WHERE !is_sketchy { LET $counterpart = rand::enum(SELECT * FROM $normal); RELATE $account->sent->$counterpart SET amount = rand::int(100, 1000); }; SELECT id, ->sent->account FROM account;

Gaming

Characters and quests

RPG game system with character progression, inventory management, and quest tracking. Features polymorphic item effects, character statistics, and complex game state management. Demonstrates flexible data modeling for gaming applications with rich object structures and relationship tracking.

-- Characters controlled by players DEFINE TABLE character SCHEMAFULL; DEFINE FIELD name ON character TYPE string; DEFINE FIELD level ON character TYPE int DEFAULT 1; DEFINE FIELD xp ON character TYPE int DEFAULT 0; DEFINE FIELD class ON character TYPE string ASSERT $value IN ["warrior", "mage", "rogue"]; DEFINE FIELD stats ON character TYPE { str: int, dex: int, int: int }; -- Items in the game world DEFINE TABLE item SCHEMAFULL; DEFINE FIELD name ON item TYPE string; DEFINE FIELD type ON item TYPE string ASSERT $value IN ["weapon", "armor", "potion"]; DEFINE FIELD rarity ON item TYPE string ASSERT $value IN ["common", "rare", "epic", "legendary"]; DEFINE FIELD effects ON item TYPE array<{ str: int } | { heal: int }>; // etc. -- Items possessed by characters DEFINE TABLE owns TYPE RELATION IN character OUT item; DEFINE FIELD equipped ON owns TYPE bool DEFAULT false; -- Quests available in the world DEFINE TABLE quest SCHEMAFULL; DEFINE FIELD name ON quest TYPE string; DEFINE FIELD required_level ON quest TYPE int DEFAULT 1; DEFINE FIELD rewards ON quest TYPE { exp: int, items: array<record<item>> }; -- Character quest progress DEFINE TABLE quest_log TYPE RELATION IN character OUT quest; DEFINE FIELD status ON quest_log TYPE string ASSERT $value IN ["active", "completed"]; DEFINE FIELD started_at ON quest_log TYPE datetime DEFAULT time::now(); DEFINE FIELD completed_at ON quest_log TYPE option<datetime>; -- Events DEFINE TABLE character_event SCHEMAFULL; DEFINE FIELD character ON character_event TYPE record<character>; DEFINE FIELD details ON character_event TYPE { type: "combat", exp: int, against: string, summary: string } | { type: "item_used", item: record<item>, summary: string } | { type: "quest_update", summary: string }; DEFINE FIELD ts ON character_event TYPE datetime DEFAULT time::now(); -- Create a new character CREATE character:aria SET name = "Aria", class = "mage", stats = { str: 4, dex: 6, int: 12 }; -- Give Aria an item RELATE character:aria->owns->(CREATE ONLY item SET name = "Wand of Sparks", type = "weapon", rarity = "rare", effects = { int: 2 }); -- Start a quest RELATE character:aria->quest_log->quest:slime_hunt SET status = "active";

Aerospace and astronomy

Telescopes and observations

Astronomical observation tracking system with instrument management and data collection. Features geospatial telescope locations, flexible observation metadata, and scientific data URL management. Demonstrates point data types, complex temporal relationships, and scientific data organization patterns.

-- Telescopes (instruments) DEFINE TABLE telescope SCHEMAFULL; DEFINE FIELD name ON telescope TYPE string; DEFINE FIELD location ON telescope TYPE point; DEFINE FIELD aperture_mm ON telescope TYPE int; -- e.g. 200 for 8" scope -- Astronomical targets DEFINE TABLE target SCHEMAFULL; DEFINE FIELD name ON target TYPE string; DEFINE FIELD type ON target TYPE string ASSERT $value IN ["star", "planet", "nebula", "galaxy", "asteroid"]; -- Observation logs DEFINE TABLE observed SCHEMAFULL TYPE RELATION IN telescope OUT target; DEFINE FIELD observer ON observed TYPE record<person>; DEFINE FIELD observed_at ON observed TYPE datetime; DEFINE FIELD observed_until ON observed TYPE option<datetime>; DEFINE FIELD exposure_length ON observed VALUE IF observed_until { observed_until - observed_at } ELSE { 0ns }; DEFINE FIELD notes ON observed TYPE option<string>; DEFINE FIELD filter ON observed TYPE option<string> ASSERT $value IN ["B", "V", "R", "I", "H-alpha", "OIII", "IR"]; DEFINE FIELD sky_conditions ON observed TYPE option<string> ASSERT $value IN ["clear", "thin cloud", "hazy", "overcast"]; DEFINE FIELD data_url ON observed TYPE option<string>; -- e.g. to FITS file, rendered image, or DOI CREATE telescope:one SET name = "The one telescope", location = (-68.44, -29.14), aperture_mm = 200; CREATE target:venus SET type = "planet", name = "Venus"; CREATE person:one; RELATE telescope:one->observed->target:venus SET observer = person:one, observed_at = time::now(), observed_until = time::now() + 1h, filter = "R", seeing = 0.7, sky_conditions = "clear", data_url = "https://astro.example.org/data/venus-2025.fits";

Launch telemetry

Space launch monitoring system with real-time telemetry data collection. Features component-level tracking, time-series data management with composite keys, and launch lifecycle status tracking. Demonstrates high-frequency data ingestion, temporal range queries, and live data streaming.

-- A specific launch instance (e.g., Falcon 9 Flight 100) DEFINE TABLE launch SCHEMAFULL; DEFINE FIELD name ON launch TYPE string; DEFINE FIELD vehicle_name ON launch TYPE option<string>; DEFINE FIELD scheduled_at ON launch TYPE datetime; DEFINE FIELD liftoff_at ON launch TYPE option<datetime>; DEFINE FIELD status ON launch TYPE string ASSERT $value IN ["scheduled", "launched", "scrubbed", "failed", "success"] DEFAULT "scheduled"; DEFINE FIELD completed ON launch TYPE option<datetime>; -- Components involved in the launch DEFINE TABLE component SCHEMAFULL; DEFINE FIELD launch ON component TYPE record<launch>; DEFINE FIELD name ON component TYPE string; -- e.g., "first_stage", "engine_1" DEFINE FIELD type ON component TYPE string ASSERT $value IN ["stage", "engine", "payload", "fairing"]; -- Time-series telemetry linked to a component DEFINE TABLE telemetry SCHEMAFULL; DEFINE FIELD id ON telemetry TYPE [record<component>, datetime]; -- [component, ulid] DEFINE FIELD altitude_m ON telemetry TYPE option<float>; DEFINE FIELD velocity_mps ON telemetry TYPE option<float>; DEFINE FIELD thrust_kN ON telemetry TYPE option<float>; DEFINE FIELD pressure_kPa ON telemetry TYPE option<float>; DEFINE FIELD temperature_C ON telemetry TYPE option<float>; DEFINE FIELD status ON telemetry TYPE option<string>; CREATE launch:one SET name = "Launch 1", vehicle_name = "Fire rocket", scheduled_at = time::now() - 5s, liftoff_at = time::now() - 1s; CREATE component:one SET launch = launch:one, name = "Engine 1", type = "engine"; CREATE component:two SET launch = launch:one, name = "Engine 2", type = "engine"; -- Add durations to all datetimes below to simulate passage of time CREATE telemetry:[component:one, time::now()] SET temperature_c = 30.5, status = "good"; CREATE telemetry:[component:one, time::now() + 1s] SET temperature_c = 30.7, status = "good"; CREATE telemetry:[component:one, time::now() + 2s] SET temperature_c = 30.9, status = "good"; CREATE telemetry:[component:one, time::now() + 3s] SET temperature_c = 35.0, status = "good"; CREATE telemetry:[component:two, time::now()] SET temperature_c = 30.5, status = "good"; CREATE telemetry:[component:two, time::now() + 1s] SET temperature_c = 30.7, status = "good"; CREATE telemetry:[component:two, time::now() + 2s] SET temperature_c = 30.9, status = "good"; CREATE telemetry:[component:two, time::now() + 3s] SET temperature_c = 35.0, status = "good"; UPDATE launch:one SET completed = time::now() + 5s; -- Get all telemetry for component:two during launch:one SELECT * FROM telemetry:[component:two, launch:one.liftoff_at]..=[component:two, launch:one.completed]; -- Or LIVE SELECT during the flight LIVE SELECT * FROM telemetry WHERE id[0] = component:two;

Defense / mission operations

Missions and tasks

Military mission management system with unit tracking and operational logging. Features hierarchical command structure, real-time status updates, and comprehensive audit trails. Demonstrates complex organizational modeling, geospatial tracking, and mission-critical data management patterns.

-- Mission-level directive DEFINE TABLE operation SCHEMAFULL; DEFINE FIELD name ON operation TYPE string; DEFINE FIELD status ON operation TYPE string ASSERT $value IN ["planned", "active", "complete", "aborted"] DEFAULT "planned"; DEFINE FIELD commander ON operation TYPE option<record<person>>; DEFINE FIELD start_time ON operation TYPE option<datetime>; DEFINE FIELD end_time ON operation TYPE option<datetime>; DEFINE TABLE unit SCHEMAFULL; DEFINE FIELD members ON unit TYPE array<record<person>>; DEFINE FIELD operation ON unit TYPE record<operation>; DEFINE FIELD name ON unit TYPE string; -- e.g., "drone-2", "squad-a" DEFINE FIELD type ON unit TYPE string ASSERT $value IN ["drone", "vehicle", "infantry", "support"]; DEFINE FIELD status ON unit TYPE string ASSERT $value IN ["ready", "deployed", "engaged", "inactive"]; -- Time-stamped unit log (e.g., movement, engagement, report) DEFINE TABLE log SCHEMAFULL; DEFINE FIELD id ON log TYPE [record<unit>, datetime]; -- [unit, timestamp] DEFINE FIELD message ON log TYPE string; DEFINE FIELD status ON log TYPE option<string>; -- e.g., "engaged", "moving", "waiting" DEFINE FIELD lonlat ON log TYPE option<point>; DEFINE FIELD visibility ON log TYPE option<string> ASSERT $value IN ["clear", "obscured", "night"]; -- Tasks assigned within a mission DEFINE TABLE task SCHEMAFULL; DEFINE FIELD operation ON task TYPE record<operation>; DEFINE FIELD name ON task TYPE string; DEFINE FIELD objective ON task TYPE string; DEFINE FIELD assigned_to ON task TYPE option<array<record<unit>>>; DEFINE FIELD priority ON task TYPE string ASSERT $value IN ["high", "medium", "low"]; DEFINE FIELD completed ON task TYPE bool DEFAULT false; CREATE operation:alpha SET name = "Operation Alpha", commander = person:one, start_time = time::now(); CREATE unit:squad1 SET operation = operation:alpha, name = "squad-1", type = "infantry", status = "deployed", members = [person:one, person:two]; CREATE unit:drone1 SET operation = operation:alpha, name = "drone-1", type = "drone", status = "ready", members = [person:three, person:four]; CREATE task SET operation = operation:alpha, name = "Secure Ridge", objective = "Clear hilltop sector", assigned_to = [unit:squad1], priority = "high"; -- Log messages (simulate time with + durations) CREATE log:[unit:squad1, time::now()] SET message = "Entered zone", status = "moving", lonlat = (44.2, 6.3); CREATE log:[unit:squad1, time::now() + 3m] SET message = "Engaged hostiles", status = "engaged", visibility = "clear"; CREATE log:[unit:drone1, time::now()] SET message = "Recon sweep complete", status = "waiting", lonlat = (44.3, 6.2);

Retail

People, products and commerce

E-commerce platform schema with customer profiles, product catalog, and shopping cart management. Features flexible address storage, multi-currency support, and comprehensive timestamp tracking. Demonstrates modern e-commerce data modeling with flexible objects and relationship management.

-- Person / customer profile DEFINE TABLE person SCHEMAFULL; DEFINE FIELD name ON person TYPE string; DEFINE FIELD email ON person TYPE string ASSERT string::is::email($value); DEFINE FIELD address ON person FLEXIBLE TYPE object; DEFINE FIELD time ON person TYPE object; DEFINE FIELD time.created_at ON person TYPE datetime DEFAULT time::now(); DEFINE FIELD time.updated_at ON person TYPE datetime VALUE time::now(); -- Payment method linked to person DEFINE TABLE payment_details SCHEMAFULL; DEFINE FIELD person ON payment_details TYPE record<person>; DEFINE FIELD stored_cards ON payment_details FLEXIBLE TYPE array<object>; DEFINE FIELD time ON payment_details TYPE object; DEFINE FIELD time.created_at ON payment_details TYPE datetime DEFAULT time::now(); DEFINE FIELD time.updated_at ON payment_details TYPE datetime VALUE time::now(); -- Seller profile DEFINE TABLE seller SCHEMAFULL; DEFINE FIELD name ON seller TYPE string; DEFINE FIELD email ON seller TYPE string ASSERT string::is::email($value); DEFINE FIELD time ON seller TYPE object; DEFINE FIELD time.created_at ON seller TYPE datetime DEFAULT time::now(); DEFINE FIELD time.updated_at ON seller TYPE datetime VALUE time::now(); -- Product listings DEFINE TABLE product SCHEMAFULL; DEFINE FIELD name ON product TYPE string; DEFINE FIELD price ON product TYPE number; DEFINE FIELD currency ON product TYPE string ASSERT $value IN ["USD", "GBP", "CAD"]; DEFINE FIELD category ON product TYPE string; DEFINE FIELD seller ON product TYPE record<seller>; DEFINE FIELD time ON product TYPE object; DEFINE FIELD time.created_at ON product TYPE datetime DEFAULT time::now(); DEFINE FIELD time.updated_at ON product TYPE datetime VALUE time::now(); -- Wishlist links (person -> product) DEFINE TABLE wishlist TYPE RELATION FROM person TO product SCHEMAFULL; DEFINE FIELD colour ON wishlist TYPE string; DEFINE FIELD size ON wishlist TYPE string; DEFINE FIELD time ON wishlist TYPE object; DEFINE FIELD time.created_at ON wishlist TYPE datetime DEFAULT time::now(); DEFINE FIELD time.deleted_at ON wishlist TYPE option<datetime>; -- Cart links (person -> product) DEFINE TABLE cart TYPE RELATION FROM person TO product SCHEMAFULL; DEFINE FIELD quantity ON cart TYPE number; DEFINE FIELD price ON cart TYPE number; DEFINE FIELD currency ON cart TYPE string ASSERT $value IN ["CAD", "EUR", "USD"]; DEFINE FIELD time ON cart TYPE object; DEFINE FIELD time.created_at ON cart TYPE datetime DEFAULT time::now(); DEFINE FIELD time.updated_at ON cart TYPE datetime VALUE time::now();

Orders, reviews, reports

Order processing and analytics system with review management and business intelligence. Features order lifecycle tracking, automated analytics tables, and full-text search capabilities. Demonstrates complex aggregations, materialized views, and search optimization for e-commerce applications.

-- Orders placed (person -> product) DEFINE TABLE order TYPE RELATION FROM person TO product SCHEMAFULL; DEFINE FIELD quantity ON order TYPE number; DEFINE FIELD price ON order TYPE number; DEFINE FIELD currency ON order TYPE string; DEFINE FIELD order_status ON order TYPE string ASSERT $value IN ["pending", "processed", "shipped", "cancelled"]; DEFINE FIELD shipping_address ON order FLEXIBLE TYPE object; DEFINE FIELD payment_method ON order TYPE string; DEFINE FIELD time ON order TYPE object; DEFINE FIELD time.created_at ON order TYPE datetime DEFAULT time::now(); DEFINE FIELD time.updated_at ON order TYPE datetime VALUE time::now(); DEFINE FIELD time.processed_at ON order TYPE option<datetime>; DEFINE FIELD time.shipped_at ON order TYPE option<datetime>; -- Product reviews DEFINE TABLE review TYPE RELATION FROM person TO product SCHEMAFULL; DEFINE FIELD rating ON review TYPE number ASSERT $value IN 0..=5; DEFINE FIELD review_text ON review TYPE string; DEFINE FIELD time ON review TYPE object; DEFINE FIELD time.created_at ON review TYPE datetime DEFAULT time::now(); DEFINE FIELD time.updated_at ON review TYPE datetime VALUE time::now(); -- Indexes and analytics DEFINE FUNCTION fn::number_of_unfulfilled_orders() { RETURN (SELECT count() FROM order WHERE order_status NOTINSIDE ["processed", "shipped"] GROUP ALL); }; -- Monthly order summary DEFINE TABLE monthly_sales TYPE NORMAL SCHEMAFULL AS SELECT count() AS number_of_orders, time::format(time.created_at, '%Y-%m') AS month, math::sum(price * quantity) AS sum_sales, currency FROM order GROUP BY month, currency; -- Average product rating DEFINE TABLE avg_product_review TYPE NORMAL SCHEMAFULL AS SELECT count() AS number_of_reviews, math::mean(<float> rating) AS avg_review, ->product.id AS product_id, ->product.name AS product_name FROM review GROUP BY product_id, product_name; -- Full-text search DEFINE ANALYZER blank_snowball TOKENIZERS blank FILTERS lowercase, snowball(english); DEFINE INDEX review_content ON review FIELDS review_text SEARCH ANALYZER blank_snowball BM25 HIGHLIGHTS;

Medical

Patient records and encounters

Healthcare management system with patient records, encounter tracking, and clinical data management. Features vital signs time-series data, medication tracking, and automated encounter lifecycle management using events. Demonstrates healthcare data modeling with temporal data, clinical workflows, and medical record compliance patterns.

-- Patient record DEFINE TABLE patient SCHEMAFULL; DEFINE FIELD name ON patient TYPE string; DEFINE FIELD dob ON patient TYPE datetime; DEFINE FIELD gender ON patient TYPE string ASSERT $value IN ["male", "female", "other", "uncertain"]; DEFINE FIELD email ON patient TYPE option<string> ASSERT string::is::email($value); DEFINE FIELD created_at ON patient TYPE datetime DEFAULT time::now(); -- One healthcare visit DEFINE TABLE encounter SCHEMAFULL; DEFINE FIELD patient ON encounter TYPE record<patient>; DEFINE FIELD occurred_at ON encounter TYPE datetime DEFAULT time::now(); DEFINE FIELD type ON encounter TYPE string ASSERT $value IN ["checkup", "emergency", "followup", "consult"]; DEFINE FIELD reason ON encounter TYPE option<string>; DEFINE FIELD location ON encounter TYPE option<string>; DEFINE FIELD ongoing ON encounter TYPE bool DEFAULT true; DEFINE FIELD ended_at ON encounter TYPE option<datetime>; DEFINE EVENT close_encounter ON encounter WHEN $event = "UPDATE" THEN { IF $before.ongoing = true AND $after.ongoing = false { UPDATE $this SET ended_at = time::now(); } }; -- Vital signs time-series (per encounter) DEFINE TABLE vital_signs SCHEMAFULL; DEFINE FIELD id ON vital_signs TYPE [record<encounter>, datetime]; DEFINE FIELD heart_rate ON vital_signs TYPE option<int> ASSERT $value IN 20..=300; DEFINE FIELD bp_systolic ON vital_signs TYPE option<int> ASSERT $value IN 40..=300; DEFINE FIELD bp_diastolic ON vital_signs TYPE option<int> ASSERT $value IN 20..=200; DEFINE FIELD temp_c ON vital_signs TYPE option<float> ASSERT $value IN 25.0..=45.0; DEFINE FIELD notes ON vital_signs TYPE option<string>; -- Diagnoses made during encounter DEFINE TABLE diagnosis SCHEMAFULL; DEFINE FIELD encounter ON diagnosis TYPE record<encounter>; DEFINE FIELD code ON diagnosis TYPE string; -- e.g., ICD-10 DEFINE FIELD label ON diagnosis TYPE string; DEFINE FIELD confirmed ON diagnosis TYPE bool DEFAULT true; -- Medications prescribed DEFINE TABLE medication SCHEMAFULL; DEFINE FIELD encounter ON medication TYPE record<encounter>; DEFINE FIELD name ON medication TYPE string; DEFINE FIELD dose_mg ON medication TYPE float; DEFINE FIELD frequency ON medication TYPE string; -- e.g., "2x daily" DEFINE FIELD duration ON medication TYPE string; -- e.g., "7 days" DEFINE FIELD prn ON medication TYPE bool DEFAULT false; -- "as needed" -- Notes written by practitioner DEFINE TABLE note SCHEMAFULL; DEFINE FIELD id ON note TYPE [record<encounter>, datetime]; DEFINE FIELD author ON note TYPE string; DEFINE FIELD content ON note TYPE string; DEFINE FIELD tags ON note TYPE option<array<string>>; CREATE patient:one SET name = "Alex Quinn", dob = d'1988-06-12', gender = "male"; CREATE encounter:one SET patient = patient:one, type = "checkup", reason = "Routine annual"; -- Vital signs log CREATE vital_signs:[encounter:one, time::now()] SET heart_rate = 72, bp_systolic = 120, bp_diastolic = 80, temp_c = 36.8; -- Diagnosis CREATE diagnosis SET encounter = encounter:one, code = "E66.9", label = "Obesity, unspecified"; -- Medication CREATE medication SET encounter = encounter:one, name = "Metformin", dose_mg = 500, frequency = "2x daily", duration = "30 days"; -- Progress note CREATE note:[encounter:one, time::now()] SET author = "Dr. Leung", content = "Patient reports improved energy since last visit.";
Edit this page on GitHub