-- All four writes commit together or roll back together. No
-- partial state, no compensating logic, no two-phase commit
-- between document, graph, and inventory stores.

BEGIN TRANSACTION;

LET $order = CREATE order SET
    user = $auth.id,
    product = product:headphones,
    total = product:headphones.price;

RELATE $auth.id->purchased->product:headphones
    SET at = time::now();

UPDATE product:headphones SET stock -= 1;

COMMIT TRANSACTION;

-- Define a record-level access method and per-table
-- permissions in SurrealQL. Auth and authorisation live
-- inside the database, not in a sidecar service.

DEFINE ACCESS account ON DATABASE
    TYPE RECORD
    SIGNUP (
        CREATE user SET
            email = $email,
            pass = crypto::argon2::generate($pass)
    )
    SIGNIN (
        SELECT * FROM user WHERE
            email = $email AND
            crypto::argon2::compare(pass, $pass)
    );

DEFINE TABLE order PERMISSIONS
    FOR select WHERE user = $auth.id
    FOR create WHERE $auth.id != NONE;

-- Hybrid RAG retrieval. Blends vector cosine similarity with BM25
-- full-text search into a single weighted score, so results are
-- both semantically relevant and keyword-accurate.

-- Step 1: Embed the user's natural language query

LET $query = "How do I configure access control?";
LET $embedding = fn::embed($query);

-- Step 2: Run a hybrid search combining vector and full-text scores

SELECT
    id, title, content,
    -- Compute semantic similarity via cosine distance
    vector::similarity::cosine(embedding, $embedding) AS vs,
    -- Compute keyword relevance via full-text scoring
    search::score(1) AS ts,
    -- Blend both signals into a single weighted score
    (vs * 0.6) + (ts * 0.4) AS score
FROM document
-- Union of both retrieval methods - score blending handles ranking
WHERE embedding <|4,COSINE|> $embedding
   OR content @1@ $query
ORDER BY score DESC
LIMIT 5;

-- Re-rank vector hits by how often cited from a hub document

LET $q = fn::embed("incident response playbook");
LET $hub = document:runbook_index;

SELECT
    id,
    title,
    vector::similarity::cosine(embedding, $q) AS sim,
    count(SELECT * FROM <-references<-document WHERE id = $hub.id) AS cited_from_hub,
    (sim * 0.75) + (math::min(cited_from_hub, 3) * 0.08) AS order
FROM article
WHERE embedding <|20,COSINE|> $q
ORDER BY order DESC
LIMIT 8;

-- Expand a seed document through typed relationships

LET $seed = document:policy_access;

SELECT
    ->mentions->document AS mentioned,
    ->supersedes->document AS newer
FROM ONLY $seed;

-- Walk two hops for related operational runbooks
SELECT VALUE ->relates->document->relates->document
FROM ONLY $seed;

-- People, roles, and assets as a single traversable graph

SELECT
    name,
    ->works_at->organisation.name AS org,
    ->has_access_to->system.name AS systems
FROM person
WHERE name = "Avery Chen";

-- Find systems reachable within three hops from a team
SELECT VALUE ->member_of->team->owns->system
FROM person:avery;

-- Recent episodic memories plus semantically similar prior facts

LET $now = time::now();
LET $q = fn::embed("database failover procedure");
LET $agent = agent:ops_bot;

SELECT
    content,
    vector::similarity::cosine(embedding, $q) AS score,
    observed_at
FROM agent_memory
WHERE agent = $agent
  AND observed_at > $now - 7d
  AND embedding <|10,COSINE|> $q
ORDER BY score DESC;

-- Invalidate superseded beliefs in one transaction
UPDATE agent_memory SET valid = false WHERE id IN $stale_ids;

-- Messages in this thread, plus entities the user has discussed before

LET $thread = thread:planning_42;

SELECT
    role,
    text,
    sent_at
FROM message
WHERE thread = $thread
ORDER BY sent_at ASC;

SELECT VALUE ->about->entity
FROM message
WHERE thread = $thread;

-- Push every change matching a predicate to connected clients

LIVE SELECT
    id,
    status,
    math::round(total, 2) AS total
FROM order
WHERE account = $account AND status != "archived";

-- Cancel a subscription when the session ends
KILL $live_handle;

-- Items similar to what similar users purchased

LET $user = $auth.id;
LET $vec = (SELECT embedding FROM ONLY $user).embedding;

SELECT
    id,
    title,
    vector::similarity::cosine(embedding, $vec) AS score
FROM product
WHERE id IN (
    SELECT VALUE ->purchased->product
    FROM person
    WHERE ->purchased->product IN (
        SELECT VALUE ->purchased->product FROM ONLY $user
    )
)
ORDER BY score DESC
LIMIT 12;

-- Points inside a bounding area, ordered by distance from the user

LET $here = (-0.1278, 51.5074);

SELECT
    id,
    name,
    geo::distance(location, $here) AS km
FROM venue
WHERE location INSIDE {
    type: "Polygon",
    coordinates: [[
        [-0.2, 51.4],
        [0.05, 51.4],
        [0.05, 51.6],
        [-0.2, 51.6],
        [-0.2, 51.4]
    ]]
}
ORDER BY km ASC
LIMIT 20;

-- React when new high-severity incidents are written

DEFINE EVENT incident_created ON TABLE incident WHEN $after.severity = "high" THEN {
    CREATE notification SET
        channel = "pager",
        incident = $after.id,
        body = "New SEV incident opened";
};

-- Fan-out status changes to an audit stream
DEFINE EVENT order_status ON TABLE order WHEN $before.status != $after.status THEN {
    CREATE audit_log SET
        entity = $after.id,
        from = $before.status,
        to = $after.status,
        at = time::now();
};