Sample industry schemas

-- 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/reference/query-language/statements/live-select
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;

DEFINE TABLE project SCHEMAFULL;
DEFINE FIELD name ON project TYPE string ASSERT string::len($value) > 3;

DEFINE TABLE vendor SCHEMAFULL;
DEFINE FIELD name ON vendor TYPE string ASSERT string::len($value) > 3;

DEFINE TABLE payment_terms SCHEMAFULL;
DEFINE FIELD code ON payment_terms TYPE string ASSERT $value.len() IN 1..=10;
DEFINE FIELD net_due ON payment_terms TYPE duration;
DEFINE FIELD description ON payment_terms TYPE string;
DEFINE INDEX payment_terms_code ON payment_terms FIELDS code UNIQUE;

DEFINE FIELD sap_vendor_number ON vendor TYPE option<string> ASSERT $value IS NONE OR $value = /[0-9]{10}/;
DEFINE FIELD default_payment_terms ON vendor TYPE option<record<payment_terms>>;

DEFINE TABLE wbs_element SCHEMAFULL;
DEFINE FIELD sap_id ON wbs_element TYPE string ASSERT $value.len() IN 1..=24;
DEFINE FIELD project ON wbs_element TYPE record<project> REFERENCE ON DELETE REJECT;
DEFINE FIELD parent ON wbs_element TYPE option<record<wbs_element>>;
DEFINE FIELD name ON wbs_element TYPE string ASSERT $value.len() > 0;
DEFINE INDEX wbs_sap_id ON wbs_element FIELDS sap_id UNIQUE;

-- Graph: WBS hierarchy and project membership (dual-write with record fields above)
DEFINE TABLE wbs_of SCHEMAFULL TYPE RELATION IN wbs_element OUT project ENFORCED;
ALTER FIELD IN ON wbs_of ASSERT IN.project = out;
ALTER FIELD out ON wbs_of ASSERT in.project = out;

DEFINE TABLE wbs_child_of SCHEMAFULL TYPE RELATION IN wbs_element OUT wbs_element ENFORCED;
ALTER FIELD IN ON wbs_child_of ASSERT IN  != out
AND in.project = out.project
AND in.parent = out;
ALTER FIELD out ON wbs_child_of ASSERT in != out
AND in.project = out.project
AND in.parent = out;

DEFINE TABLE purchase_order SCHEMAFULL;
-- Ten-digit numeric document numbers are common; add a prefix (e.g. /^45/) per deployment.
DEFINE FIELD sap_po_number ON purchase_order TYPE string ASSERT $value = /[0-9]{10}/;
DEFINE FIELD vendor ON purchase_order TYPE record<vendor> REFERENCE ON DELETE REJECT;
DEFINE FIELD currency ON purchase_order TYPE "CAD" | "USD" | "EUR";
DEFINE FIELD status ON purchase_order TYPE "open" | "closed" | "cancelled" DEFAULT "open";
DEFINE FIELD issued_at ON purchase_order TYPE datetime;
DEFINE INDEX po_sap_number ON purchase_order FIELDS sap_po_number UNIQUE;

DEFINE TABLE ordered_from SCHEMAFULL TYPE RELATION IN purchase_order OUT vendor ENFORCED;
ALTER FIELD IN ON ordered_from ASSERT IN.vendor = out;
ALTER FIELD out ON ordered_from ASSERT in.vendor = out;

DEFINE TABLE po_line SCHEMAFULL;
DEFINE FIELD purchase_order ON po_line TYPE record<purchase_order> REFERENCE ON DELETE CASCADE;
DEFINE FIELD line_number ON po_line TYPE int ASSERT $value IN 0..=9990;
DEFINE FIELD description ON po_line TYPE string ASSERT $value.len() > 0;
DEFINE FIELD amount_cents ON po_line TYPE int ASSERT $value >= 100;
DEFINE FIELD wbs ON po_line TYPE record<wbs_element> REFERENCE ON DELETE REJECT;
DEFINE INDEX po_line_key ON po_line FIELDS purchase_order, line_number UNIQUE;

DEFINE TABLE charges SCHEMAFULL TYPE RELATION IN po_line OUT wbs_element ENFORCED;
ALTER FIELD IN ON charges ASSERT IN.wbs = out;
ALTER FIELD out ON charges ASSERT in.wbs = out;

DEFINE TABLE vendor_invoice SCHEMAFULL;
DEFINE FIELD sap_doc_number ON vendor_invoice TYPE string ASSERT $value = /[0-9]{10}/;
DEFINE FIELD purchase_order ON vendor_invoice TYPE record<purchase_order> REFERENCE ON DELETE REJECT;
DEFINE FIELD vendor ON vendor_invoice TYPE record<vendor> ASSERT vendor = purchase_order.vendor;
DEFINE FIELD amount_cents ON vendor_invoice TYPE int ASSERT $value >= 100;
DEFINE FIELD payment_terms ON vendor_invoice TYPE record<payment_terms>;
DEFINE FIELD baseline_date ON vendor_invoice TYPE datetime;
DEFINE FIELD due_date ON vendor_invoice TYPE datetime ASSERT $value >= baseline_date;
DEFINE FIELD status ON vendor_invoice TYPE "parked" | "approved" | "paid" | "blocked" DEFAULT "parked";
DEFINE INDEX invoice_sap_number ON vendor_invoice FIELDS sap_doc_number UNIQUE;

DEFINE TABLE invoices SCHEMAFULL TYPE RELATION IN vendor_invoice OUT purchase_order ENFORCED;
ALTER FIELD IN ON invoices ASSERT IN.purchase_order = out
AND in.vendor = out.vendor;
ALTER FIELD out ON invoices ASSERT in.purchase_order = out
AND in.vendor = out.vendor;

DEFINE EVENT po_must_have_lines ON purchase_order
    WHEN $event = "UPDATE"
    AND $before.status != "closed"
    AND $after.status = "closed"
    THEN {
        LET $lines = (
            SELECT id
            FROM po_line
            WHERE purchase_order = $after.id
        );
        IF $lines.len() = 0 {
            THROW "Cannot close a purchase order with no lines";
        };
    };

CREATE payment_terms:net30 SET
    code = "NT30",
    net_due = 30d,
    description = "Net 30 days";
CREATE payment_terms:net27 SET
    code = "Z027",
    net_due = 27d,
    description = "Net 27 days";
CREATE payment_terms:payrcpt SET
    code = "Z000",
    net_due = 0d,
    description = "Due on receipt";

CREATE project:pad3 SET name = "Pad 3 expansion";
CREATE wbs_element:pad3 SET
    sap_id = "EP-24-PAD3",
    project = project:pad3,
    name = "Pad 3 package";
CREATE wbs_element:pad3_civil SET
    sap_id = "EP-24-PAD3-CIVIL",
    project = project:pad3,
    parent = wbs_element:pad3,
    name = "Civil & earthworks";

RELATE wbs_element:pad3 -> wbs_of -> project:pad3;
RELATE wbs_element:pad3_civil -> wbs_of -> project:pad3;
RELATE wbs_element:pad3_civil -> wbs_child_of -> wbs_element:pad3;

CREATE vendor:welding SET
    name = "Northern Weld Services Ltd",
    sap_vendor_number = "0000100042",
    default_payment_terms = payment_terms:net30;
CREATE vendor:small SET
    name = "Camp Catering Co-op",
    sap_vendor_number = "0000100891",
    default_payment_terms = payment_terms:payrcpt;

CREATE purchase_order:450001 SET
    sap_po_number = "4500000123",
    vendor = vendor:welding,
    currency = "CAD",
    issued_at = d'2026-05-15';
RELATE purchase_order:450001 -> ordered_from -> vendor:welding;

CREATE po_line:line10 SET
    purchase_order = purchase_order:450001,
    line_number = 10,
    description = "Structural welding — pipe rack",
    amount_cents = 18500000,
    wbs = wbs_element:pad3_civil;
RELATE po_line:line10 -> charges -> wbs_element:pad3_civil;

CREATE vendor_invoice:510001 SET
    sap_doc_number = "5100000456",
    purchase_order = purchase_order:450001,
    vendor = vendor:welding,
    amount_cents = 9250000,
    payment_terms = payment_terms:net30,
    baseline_date = d'2026-06-01',
    due_date = d'2026-07-01',
    status = "approved";
RELATE vendor_invoice:510001 -> invoices -> purchase_order:450001;

-- Record fields (connector / tabular queries)
SELECT *,
purchase_order.{sap_po_number, status, vendor: vendor.name} AS po
FROM po_line
WHERE wbs = wbs_element:pad3_civil;

-- Graph: lines charging a WBS, then their POs and vendor
SELECT
    name,
    sap_id,
    <-charges<-po_line.{ line_number, amount_cents, sap_po_number: purchase_order.sap_po_number } AS lines,
    ->wbs_child_of->wbs_element.{ name, sap_id } AS parent_wbs
FROM wbs_element:pad3_civil;

SELECT
    sap_doc_number,
    amount_cents / 100 AS amount_dollars,
    due_date,
    status
FROM vendor_invoice
WHERE status IN ["parked", "approved"]
ORDER BY due_date;

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