schema.rs

//! The SQL that defines EdgeTable: schema, GBNF grammar, labelling prompt, trigger.
//!
//! All four were measured in the Phase 0 spike (`examples/spike.py`).
//! Change any of them and re-run `make spike` —
//! it scores the categories against hand labels and fails below 85%.

/// Every `NOT NULL` column carries a `DEFAULT` because `cloudsync_init()` refuses
/// the table otherwise: CRDT merges arrive column by column, so a `NOT NULL` column
/// with no default has nothing to hold the row together mid-merge.
///
/// `ai_json` keeps the raw generation. It costs a little space and makes every
/// label auditable — useful on stage when someone asks "how do you know it isn't
/// making that up?".
pub const SCHEMA: &str = r#"
CREATE TABLE IF NOT EXISTS feedback (
  id          TEXT PRIMARY KEY,
  author      TEXT NOT NULL DEFAULT '',
  channel     TEXT NOT NULL DEFAULT 'email',
  body        TEXT NOT NULL DEFAULT '',
  created_at  TEXT NOT NULL DEFAULT '',
  summary     TEXT,
  sentiment   TEXT,
  category    TEXT,
  ai_json     TEXT,
  embedding   BLOB
);

-- Field metadata, so the "+ add field" menu is data-driven. Deliberately NOT
-- synced: `visible` is per-device state. Revealing an AI field is something you
-- do on the laptop in front of you, and it should not reach into the second
-- device and pre-open its columns. The rest of the row (label, kind, position)
-- is derived from DEFAULT_FIELDS below, so every device builds an identical list
-- on first run without any of it crossing the wire.
CREATE TABLE IF NOT EXISTS fields (
  id           TEXT PRIMARY KEY,
  table_name   TEXT NOT NULL DEFAULT 'feedback',
  column_name  TEXT NOT NULL DEFAULT '',
  label        TEXT NOT NULL DEFAULT '',
  kind         TEXT NOT NULL DEFAULT 'text',
  config       TEXT NOT NULL DEFAULT '{}',
  position     INTEGER NOT NULL DEFAULT 0,
  visible      INTEGER NOT NULL DEFAULT 0
);

-- Device-local key/value. Today it holds the SQLite Cloud credentials typed into
-- the app, so a machine with no checkout to edit -- a second laptop, a packaged
-- .app -- can be pointed at a database without a .env file.
--
-- Like `fields`, and much more emphatically: this table must NEVER be registered
-- with cloudsync. It would change the schema hash and break sync against the
-- server, and it would push an API key to the cloud. It is in LOCAL_ONLY_TABLES,
-- which un-registers it on open if it somehow ever was.
--
-- The value is stored in plain text, in a file in per-user app data. The correct
-- home for a secret is the OS keychain; the reason it is not here is that a
-- keychain prompt appearing in the middle of a demo is a worse failure than a
-- plaintext key on a demo laptop. "Forget credentials" deletes the row.
CREATE TABLE IF NOT EXISTS settings (
  key   TEXT PRIMARY KEY NOT NULL,
  value TEXT NOT NULL DEFAULT ''
);
"#;

/// The tables that participate in sync -- `feedback` only.
///
/// This set defines the schema hash: sqlite-sync builds it from the columns of
/// exactly these tables, so it must match what the cloud database has enabled
/// under OffSync. Adding a table here means adding it there too.
pub const SYNCED_TABLES: &[&str] = &["feedback"];

/// Tables that must NOT sync, and are actively un-registered if an older database
/// still has them enabled from a previous version of this schema.
pub const LOCAL_ONLY_TABLES: &[&str] = &["fields", "settings"];

pub const EMBEDDING_DIMS: usize = 384;

/// Constrains the decoder to exactly one JSON object with the labels drawn from
/// fixed enums. Off-enum output is unrepresentable, not merely unlikely — which is
/// also why no output normalizer exists anywhere in this crate.
///
/// It has a second effect worth knowing: Qwen3 is a reasoning model that would
/// normally open with a `<think>` block, and the grammar forbids that because the
/// first token must be `{`.
pub const GRAMMAR: &str = r#"
root      ::= "{" ws "\"summary\":" ws summary "," ws "\"sentiment\":" ws sentiment "," ws "\"category\":" ws category ws "}"
summary   ::= "\"" char{10,110} "\""
sentiment ::= "\"positive\"" | "\"neutral\"" | "\"negative\""
category  ::= "\"billing\"" | "\"bugs\"" | "\"feature_request\"" | "\"ux\"" | "\"performance\"" | "\"other\""
char      ::= [^"\\\x00-\x1F] | "\\" ["\\/bfnrt]
ws        ::= [ \t\n]*
"#;

/// A SQL expression that builds the labelling prompt from `NEW.body`.
///
/// `llm_text_generate()` applies no chat template, so the ChatML markers are carried
/// explicitly. The numbered precedence rules are what take Qwen3-1.7B from 62% to
/// 92% category accuracy — they are not decorative, and the two worked examples at
/// the end each fix a specific misclassification the model made without them.
pub const PROMPT_EXPR: &str = r#"
  '<|im_start|>system' || char(10) ||
  'You label incoming customer feedback for a SaaS product. Reply with one JSON object.' || char(10) ||
  'summary: one plain sentence under 15 words, restating only what the message says.' || char(10) ||
  'Do not invent numbers, causes or details that are not in the message.' || char(10) ||
  'sentiment: positive | neutral | negative' || char(10) ||
  'category: what the message is ABOUT. Test the rules in order and stop at the first match:' || char(10) ||
  '  1. billing - money or plans: a charge, refund, invoice, price, seat count, upgrade or downgrade.' || char(10) ||
  '  2. feature_request - the user asks for something that does not exist yet, however small.' || char(10) ||
  '  3. bugs - something is broken: it crashes, errors, fails silently, or does the wrong thing.' || char(10) ||
  '  4. performance - it works correctly but is too slow.' || char(10) ||
  '  5. ux - how the existing product looks or feels to use, including praise for it.' || char(10) ||
  '  6. other - anything else, such as documentation or general praise.' || char(10) ||
  'Asking for a dark theme or a shortcut is feature_request, not ux.' || char(10) ||
  'Logging in to the wrong place is bugs, not ux.<|im_end|>' || char(10) ||
  '<|im_start|>user' || char(10) || BODY_EXPR || '<|im_end|>' || char(10) ||
  '<|im_start|>assistant' || char(10)
"#;

/// Build the prompt expression against an arbitrary body expression —
/// `NEW.body` inside the trigger, a bound `?` in the bulk worker.
pub fn prompt_for(body_expr: &str) -> String {
    PROMPT_EXPR.replace("BODY_EXPR", body_expr)
}

/// The enrichment trigger.
///
/// Two guards, both load-bearing:
///
/// * `NEW.summary IS NULL` — a row already carrying its AI values is not inferred
///   again. Note this guard is **not** what protects incoming sync traffic:
///   sqlite-sync applies a row column by column and re-enters the trigger on each
///   write, and on the write that carries `body` the summary has not arrived yet,
///   so the guard passes and the row is inferred anyway — 101s versus 4.6s for a
///   24-row first sync, for values that were then overwritten. Sync switches the
///   trigger off instead; see `sync::without_trigger`.
/// * `NEW.body <> ''` — sqlite-sync may apply columns separately, and `body` has a
///   `DEFAULT ''`. A row whose body has not landed yet would otherwise be labelled
///   from an empty string. Such a row stays NULL and the bulk worker sweeps it up.
///
/// Note this writes to a synchronized table, which sqlite-sync's schema guide
/// advises against. It is a deliberate, tested exception: the write is idempotent
/// under the guards above, and it is the entire point of the product.
pub fn trigger_sql() -> String {
    format!(
        r#"
CREATE TRIGGER IF NOT EXISTS feedback_ai AFTER INSERT ON feedback
WHEN NEW.summary IS NULL AND NEW.body <> ''
BEGIN
  UPDATE feedback SET ai_json = llm_text_generate({prompt}, 'n_predict=140')
   WHERE id = NEW.id;
  UPDATE feedback
     SET summary   = json_extract(ai_json, '$.summary'),
         sentiment = json_extract(ai_json, '$.sentiment'),
         category  = json_extract(ai_json, '$.category')
   WHERE id = NEW.id;
END;
"#,
        prompt = prompt_for("NEW.body")
    )
}

/// A column the grid can draw: database column, header label, kind, visible at start.
pub struct FieldDef {
    pub column: &'static str,
    pub label: &'static str,
    pub kind: &'static str,
    pub visible: bool,
}

/// The starting field list.
///
/// The three `ai_*` fields start **hidden** on purpose: revealing them is demo
/// beat 2. Because one generation fills all three at once, showing the first one
/// pays for the backfill and the other two then appear instantly — which is the
/// designed behaviour, not a shortcut. See §5 of the plan.
pub const DEFAULT_FIELDS: &[FieldDef] = &[
    FieldDef { column: "author",     label: "Author",       kind: "text",         visible: true },
    FieldDef { column: "channel",    label: "Channel",      kind: "select",       visible: true },
    FieldDef { column: "body",       label: "Feedback",     kind: "text",         visible: true },
    FieldDef { column: "created_at", label: "Received",     kind: "date",         visible: true },
    FieldDef { column: "summary",    label: "AI Summary",   kind: "ai_summary",   visible: false },
    FieldDef { column: "sentiment",  label: "AI Sentiment", kind: "ai_sentiment", visible: false },
    FieldDef { column: "category",   label: "AI Category",  kind: "ai_category",  visible: false },
];