Sample schemas by industry

-- 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 COMPUTED 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 COMPUTED <-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 COMPUTED math::mean(activities.progress);
DEFINE FIELD is_complete  ON milestone COMPUTED 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;

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>;

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 a schemaless object 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 > 15 {
      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;

DEFINE TABLE risk SCHEMAFULL;
DEFINE FIELD project        ON risk TYPE record<project> REFERENCE;
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 computed field to calculate value on each SELECT
DEFINE FIELD active         ON risk COMPUTED 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 COMPUTED
    math::sum(<~risk.map(|$risk| $risk.maximum_impact * $risk.likelihood));

-- See risks at the current date
DEFINE FIELD current_risk_impact ON project COMPUTED
    math::sum(<~risk.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;

-- 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 COMPUTED 
    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 COMPUTED 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 COMPUTED
  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;

-- 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;

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;

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 customer SCHEMAFULL;
DEFINE FIELD name     ON customer TYPE string;
DEFINE FIELD bank     ON customer TYPE record<bank> REFERENCE;

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 set<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 COMPUTED <~customer;

-- 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, name = "Galen Pathwarden";
CREATE account:one SET customer = customer:one, bank = bank:one, currency = "JPY", amount = 10000;

SELECT *, customers.{id, name} FROM bank;

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;

-- 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);

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";
    };
};

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;

-- 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;

CREATE |account:50|;
CREATE |account:1..16| 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;

-- 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";

-- 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";

-- 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;

-- 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);