-- sibyl-memory-client SQLite schema v1
--
-- Port of the canonical sibyl_memory.* Postgres schema (scripts/sibyl-memory-schema.sql,
-- applied to Neon 2026-05-01) to SQLite for the local-first plugin v1.
--
-- Dialect translations:
--   UUID                → TEXT (Python uuid.uuid4() at write-time)
--   JSONB               → TEXT with CHECK(json_valid(col)) using SQLite json1
--   TIMESTAMPTZ + now() → TEXT ISO 8601 UTC via strftime('%Y-%m-%dT%H:%M:%fZ','now')
--   gin jsonb_path_ops  → SQLite json_extract expression indexes where useful
--   NUMERIC             → REAL (sufficient precision for plugin v1 use)
--   tsvector            → FTS5 virtual tables for text search
--
-- Multi-tenant: every table carries tenant_id. Local-first means typically
-- one tenant per machine, but the schema accepts N tenants (paid Team-tier
-- federation forward-compatible).
--
-- Idempotent. Apply via CREATE TABLE IF NOT EXISTS. Schema version recorded
-- in sibyl_memory_schema_version for future migrations.

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

-- ============================================================================
-- WARM tier: entities (single source of truth per rule 43)
-- ============================================================================
CREATE TABLE IF NOT EXISTS entities (
  id          TEXT PRIMARY KEY,
  tenant_id   TEXT NOT NULL,
  category    TEXT NOT NULL,
  name        TEXT NOT NULL,
  status      TEXT,
  body        TEXT NOT NULL CHECK (json_valid(body)),
  created_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  updated_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  UNIQUE (tenant_id, category, name)
);

CREATE INDEX IF NOT EXISTS entities_tenant_cat_status
  ON entities (tenant_id, category, status);
CREATE INDEX IF NOT EXISTS entities_updated_at
  ON entities (tenant_id, updated_at DESC);

-- ============================================================================
-- Cross-references: typed relations between entities
-- ============================================================================
CREATE TABLE IF NOT EXISTS entity_relations (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL,
  from_id       TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
  to_id         TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
  relation_type TEXT NOT NULL,
  metadata      TEXT CHECK (metadata IS NULL OR json_valid(metadata)),
  created_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE INDEX IF NOT EXISTS entity_relations_from
  ON entity_relations (tenant_id, from_id, relation_type);
CREATE INDEX IF NOT EXISTS entity_relations_to
  ON entity_relations (tenant_id, to_id, relation_type);

-- ============================================================================
-- HOT tier: state documents (treasury, priorities, session, index analogs)
-- ============================================================================
CREATE TABLE IF NOT EXISTS state_documents (
  tenant_id    TEXT NOT NULL,
  document_key TEXT NOT NULL,
  body         TEXT NOT NULL CHECK (json_valid(body)),
  updated_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  PRIMARY KEY (tenant_id, document_key)
);

-- ============================================================================
-- COLD tier: append-only journal of events
-- ============================================================================
CREATE TABLE IF NOT EXISTS journal_events (
  id         TEXT PRIMARY KEY,
  tenant_id  TEXT NOT NULL,
  ts         TEXT NOT NULL,
  evaluated  TEXT CHECK (evaluated IS NULL OR json_valid(evaluated)),
  acted      TEXT CHECK (acted IS NULL OR json_valid(acted)),
  forward    TEXT CHECK (forward IS NULL OR json_valid(forward)),
  extra      TEXT CHECK (extra IS NULL OR json_valid(extra))
);

CREATE INDEX IF NOT EXISTS journal_events_tenant_ts
  ON journal_events (tenant_id, ts DESC);

-- ============================================================================
-- COLD tier: revenue events with optional entity ref
-- ============================================================================
CREATE TABLE IF NOT EXISTS revenue_events (
  id                  TEXT PRIMARY KEY,
  tenant_id           TEXT NOT NULL,
  ts                  TEXT NOT NULL,
  event_type          TEXT,
  gross_usd           REAL,
  operator_share_usd  REAL,
  source              TEXT,
  tx                  TEXT,
  entity_id           TEXT REFERENCES entities(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS revenue_events_tenant_ts
  ON revenue_events (tenant_id, ts DESC);

-- ============================================================================
-- COLD tier: error events
-- ============================================================================
CREATE TABLE IF NOT EXISTS error_events (
  id         TEXT PRIMARY KEY,
  tenant_id  TEXT NOT NULL,
  ts         TEXT NOT NULL,
  code       TEXT,
  message    TEXT,
  context    TEXT CHECK (context IS NULL OR json_valid(context))
);

CREATE INDEX IF NOT EXISTS error_events_tenant_ts
  ON error_events (tenant_id, ts DESC);

-- ============================================================================
-- REFERENCE tier: static documents (markdown bodies, lookup-only)
-- ============================================================================
CREATE TABLE IF NOT EXISTS reference_documents (
  tenant_id   TEXT NOT NULL,
  doc_key     TEXT NOT NULL,
  body        TEXT,
  metadata    TEXT CHECK (metadata IS NULL OR json_valid(metadata)),
  updated_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  PRIMARY KEY (tenant_id, doc_key)
);

-- ============================================================================
-- ARCHIVE tier: frozen entities (out of working set, retrievable)
-- ============================================================================
CREATE TABLE IF NOT EXISTS archived_entities (
  id                  TEXT PRIMARY KEY,
  tenant_id           TEXT NOT NULL,
  original_entity_id  TEXT,
  category            TEXT,
  name                TEXT,
  body                TEXT CHECK (body IS NULL OR json_valid(body)),
  archived_at         TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  archive_reason      TEXT
);

CREATE INDEX IF NOT EXISTS archived_entities_tenant_cat
  ON archived_entities (tenant_id, category, name);

-- ============================================================================
-- FLAGGED tier: actors flagged for social-engineering / fraud (rule 13/14/15)
-- ============================================================================
CREATE TABLE IF NOT EXISTS flagged_actors (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  actor_handle    TEXT,
  actor_address   TEXT,
  flagged_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  reason          TEXT,
  evidence        TEXT CHECK (evidence IS NULL OR json_valid(evidence))
);

CREATE INDEX IF NOT EXISTS flagged_actors_tenant
  ON flagged_actors (tenant_id);

-- ============================================================================
-- FTS5 virtual tables for full-text retrieval (the tsvector port)
-- ============================================================================
-- v3 (2026-05-18): all FTS5 tables now use external-content (or contentless
-- for journal). Body lives in the base table, FTS5 stores only the index.
-- Triggers fire transparently. Disk footprint stays flat (vs v2's 2x dup).
-- Cross-tier search lands here: entities + state + reference + journal.
-- v2 → v3 migration is handled in storage.py:_migrate_to_v3.

-- ENTITIES: external-content FTS5 over entities table
CREATE VIRTUAL TABLE IF NOT EXISTS entities_fts USING fts5(
  name, category, body, tenant_id UNINDEXED,
  content='entities', content_rowid='rowid',
  tokenize = 'porter unicode61'
);

CREATE TRIGGER IF NOT EXISTS entities_ai_fts
AFTER INSERT ON entities BEGIN
  INSERT INTO entities_fts(rowid, name, category, body, tenant_id)
  VALUES (new.rowid, new.name, new.category, new.body, new.tenant_id);
END;

CREATE TRIGGER IF NOT EXISTS entities_ad_fts
AFTER DELETE ON entities BEGIN
  INSERT INTO entities_fts(entities_fts, rowid, name, category, body, tenant_id)
  VALUES ('delete', old.rowid, old.name, old.category, old.body, old.tenant_id);
END;

CREATE TRIGGER IF NOT EXISTS entities_au_fts
AFTER UPDATE ON entities BEGIN
  INSERT INTO entities_fts(entities_fts, rowid, name, category, body, tenant_id)
  VALUES ('delete', old.rowid, old.name, old.category, old.body, old.tenant_id);
  INSERT INTO entities_fts(rowid, name, category, body, tenant_id)
  VALUES (new.rowid, new.name, new.category, new.body, new.tenant_id);
END;

-- STATE: external-content FTS5 over state_documents
CREATE VIRTUAL TABLE IF NOT EXISTS state_documents_fts USING fts5(
  document_key, body, tenant_id UNINDEXED,
  content='state_documents', content_rowid='rowid',
  tokenize = 'porter unicode61'
);

CREATE TRIGGER IF NOT EXISTS state_documents_ai_fts
AFTER INSERT ON state_documents BEGIN
  INSERT INTO state_documents_fts(rowid, document_key, body, tenant_id)
  VALUES (new.rowid, new.document_key, new.body, new.tenant_id);
END;

CREATE TRIGGER IF NOT EXISTS state_documents_ad_fts
AFTER DELETE ON state_documents BEGIN
  INSERT INTO state_documents_fts(state_documents_fts, rowid, document_key, body, tenant_id)
  VALUES ('delete', old.rowid, old.document_key, old.body, old.tenant_id);
END;

CREATE TRIGGER IF NOT EXISTS state_documents_au_fts
AFTER UPDATE ON state_documents BEGIN
  INSERT INTO state_documents_fts(state_documents_fts, rowid, document_key, body, tenant_id)
  VALUES ('delete', old.rowid, old.document_key, old.body, old.tenant_id);
  INSERT INTO state_documents_fts(rowid, document_key, body, tenant_id)
  VALUES (new.rowid, new.document_key, new.body, new.tenant_id);
END;

-- REFERENCE: external-content FTS5 over reference_documents
CREATE VIRTUAL TABLE IF NOT EXISTS reference_documents_fts USING fts5(
  doc_key, body, tenant_id UNINDEXED,
  content='reference_documents', content_rowid='rowid',
  tokenize = 'porter unicode61'
);

CREATE TRIGGER IF NOT EXISTS reference_ai_fts
AFTER INSERT ON reference_documents BEGIN
  INSERT INTO reference_documents_fts(rowid, doc_key, body, tenant_id)
  VALUES (new.rowid, new.doc_key, new.body, new.tenant_id);
END;

CREATE TRIGGER IF NOT EXISTS reference_ad_fts
AFTER DELETE ON reference_documents BEGIN
  INSERT INTO reference_documents_fts(reference_documents_fts, rowid, doc_key, body, tenant_id)
  VALUES ('delete', old.rowid, old.doc_key, old.body, old.tenant_id);
END;

CREATE TRIGGER IF NOT EXISTS reference_au_fts
AFTER UPDATE ON reference_documents BEGIN
  INSERT INTO reference_documents_fts(reference_documents_fts, rowid, doc_key, body, tenant_id)
  VALUES ('delete', old.rowid, old.doc_key, old.body, old.tenant_id);
  INSERT INTO reference_documents_fts(rowid, doc_key, body, tenant_id)
  VALUES (new.rowid, new.doc_key, new.body, new.tenant_id);
END;

-- JOURNAL: standalone FTS5 over journal_events (concatenated payload).
-- Standalone (not external-content) because journal_events has 4 separate
-- JSON payload columns we want searchable as one concatenated field, and
-- there's no single base-table column we could external-content against.
-- Acceptable cost: journal is append-only (no updates), so the body
-- duplication doesn't compound on edits like it would on warm entities.
CREATE VIRTUAL TABLE IF NOT EXISTS journal_events_fts USING fts5(
  ts UNINDEXED, payload, tenant_id UNINDEXED, event_id UNINDEXED,
  tokenize = 'porter unicode61'
);

CREATE TRIGGER IF NOT EXISTS journal_events_ai_fts
AFTER INSERT ON journal_events BEGIN
  INSERT INTO journal_events_fts(rowid, ts, payload, tenant_id, event_id)
  VALUES (
    new.rowid, new.ts,
    COALESCE(new.evaluated, '') || ' ' || COALESCE(new.acted, '') || ' ' ||
      COALESCE(new.forward, '') || ' ' || COALESCE(new.extra, ''),
    new.tenant_id, new.id
  );
END;

-- ============================================================================
-- Schema version tracking
-- ============================================================================
CREATE TABLE IF NOT EXISTS sibyl_memory_schema_version (
  version       INTEGER PRIMARY KEY,
  applied_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  description   TEXT
);

INSERT OR IGNORE INTO sibyl_memory_schema_version (version, description)
VALUES (1, 'sibyl-memory-client v1. SQLite port of sibyl_memory.* Postgres schema. 10 tables (entities, entity_relations, state_documents, journal_events, revenue_events, error_events, reference_documents, archived_entities, flagged_actors, schema_version) + 2 FTS5 virtual tables. Local-first plugin foundation.');

-- ============================================================================
-- Schema v2 — self-learning skill proposals (review queue)
-- ============================================================================
-- The Learner module scans journal_events for repeating patterns and writes
-- proposed skill documents here. The user reviews via `sibyl learn review`
-- and either accepts (which writes to reference_documents under skill/<slug>)
-- or rejects. Idempotent; safe to re-apply against a v1 database.
CREATE TABLE IF NOT EXISTS skill_proposals (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),

  -- detector output
  pattern_kind    TEXT NOT NULL,   -- 'repeated_action' / 'structural_similarity' / 'temporal_routine' / 'co_occurrence'
  proposed_slug   TEXT NOT NULL,   -- the reference_documents.doc_key it would land under (skill/<slug>)
  proposed_title  TEXT,            -- one-line human-readable title
  proposed_body   TEXT NOT NULL,   -- the actual skill body (markdown text)

  -- evidence + provenance
  evidence        TEXT NOT NULL CHECK (json_valid(evidence)),  -- list of source journal_event ids + snippets
  confidence      REAL NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
  summarizer      TEXT NOT NULL,   -- 'local-deterministic' / 'byok-anthropic' / 'byok-openai' / 'venice-x402' / etc.

  -- review state
  status          TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'rejected', 'superseded')),
  reviewed_at     TEXT,
  review_note     TEXT,

  -- when accepted, points at the reference_documents row that was created
  accepted_doc_key TEXT
);

CREATE INDEX IF NOT EXISTS skill_proposals_tenant_status
  ON skill_proposals (tenant_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS skill_proposals_slug
  ON skill_proposals (tenant_id, proposed_slug);

-- ============================================================================
-- Schema v2 — learning run log (so detectors don't re-scan ground they covered)
-- ============================================================================
CREATE TABLE IF NOT EXISTS learning_runs (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  started_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  completed_at    TEXT,
  summarizer      TEXT NOT NULL,
  events_scanned  INTEGER NOT NULL DEFAULT 0,
  proposals_made  INTEGER NOT NULL DEFAULT 0,
  cursor_after_ts TEXT,             -- watermark — newest journal ts processed
  notes           TEXT
);

CREATE INDEX IF NOT EXISTS learning_runs_tenant
  ON learning_runs (tenant_id, started_at DESC);

INSERT OR IGNORE INTO sibyl_memory_schema_version (version, description)
VALUES (2, 'sibyl-memory-client v2. Adds skill_proposals (self-learning review queue) and learning_runs (detector watermark log). Idempotent migration; v1 databases auto-upgrade on first open. Free tier uses local-deterministic summarizer; paid tier can opt into BYOK or Venice/x402-routed summarization.');

INSERT OR IGNORE INTO sibyl_memory_schema_version (version, description)
VALUES (3, 'sibyl-memory-client v3. External-content FTS5 across entities + state_documents + reference_documents + contentless FTS5 over journal_events. Fixes the v0.3.0 "search covers warm entities only" bug. Eliminates body duplication (v2 stored body twice — base table + FTS5). v2 to v3 migration handled in storage.py:_migrate_to_v3: drops the standalone FTS5 tables and rebuilds in external-content shape from existing base-table data. No data loss.');

-- ============================================================================
-- Schema v4 — folded-trigram search shadow (v0.5.0 multi-language search)
-- ============================================================================
-- The standalone ``search_shadow`` FTS5 trigram table + its 10 maintenance
-- triggers are NOT declared here: their DDL is GENERATED at runtime by
-- sibyl_memory_client/shadow.py, because two pieces cannot live in a static file
-- — (a) the fold map (the ł/ß/ø/... non-decomposable → ASCII rendering baked into
-- the trigger + backfill SQL, single source of truth in shadow.FOLD_MAP), and
-- (b) the tokenizer clause, which is selected at runtime across the SQLite 3.45
-- boundary ('trigram remove_diacritics 1' on >= 3.45, bare 'trigram' below).
-- The shadow holds a FOLDED copy of the searchable text across all four tiers and
-- is consulted ONLY as a zero-hit fallback (client.py search()), giving substring
-- + non-decomposable-fold matching for scripts the porter-unicode61 primary index
-- cannot reach. v3 → v4 migration + crash-atomic marker + heal: storage.py
-- (_migrate_if_needed, _SHADOW_MARKER). Rollback to exact v3 behaviour: DROP the
-- 10 *_shadow triggers + DROP TABLE search_shadow + PRAGMA user_version = 3.
INSERT OR IGNORE INTO sibyl_memory_schema_version (version, description)
VALUES (4, 'sibyl-memory-client v4. Adds the standalone folded-trigram search_shadow table + maintenance triggers (DDL generated by shadow.py — fold map + runtime tokenizer clause cannot live in a static file). A FOLDED copy of searchable text across all four tiers, maintained by DB-side triggers, consulted only as a zero-hit fallback in MemoryClient.search() to give substring + non-decomposable-diacritic (ł ß ø æ đ ı œ þ ð) matching for the scripts the porter-unicode61 primary index cannot reach. Strictly additive: primary results are byte-identical to v3. v3 → v4 migration + crash-atomic PRAGMA user_version marker + portability/heal handled in storage.py:_migrate_if_needed. Derived state, always rebuildable from the base tables; base data untouched. Rollback: DROP the *_shadow triggers + search_shadow, PRAGMA user_version = 3.');

-- ============================================================================
-- Schema v5 (0.9.0) - pre-sign-in adoption audit (not tenant-scoped)
-- ============================================================================
-- One row per adoption of DEFAULT_TENANT rows into an account tenant
-- (_adopt.py), written in the SAME transaction as the move, plus a 'seen' row
-- the first time a verified account uses this store (so automatic adoption runs
-- for an account's FIRST sign-in only, and never merges into a different
-- account once another one has used this store). Counts and renamed business
-- keys only; never bodies. Local only; nothing reads it over the network.
CREATE TABLE IF NOT EXISTS tenant_adoptions (
  id           TEXT PRIMARY KEY,
  from_tenant  TEXT NOT NULL,
  to_tenant    TEXT NOT NULL,
  account_id   TEXT NOT NULL,
  kind         TEXT NOT NULL CHECK (kind IN ('auto', 'command', 'seen')),
  moved_at     TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  counts       TEXT NOT NULL CHECK (json_valid(counts)),
  renamed      TEXT CHECK (renamed IS NULL OR json_valid(renamed))
);

CREATE INDEX IF NOT EXISTS tenant_adoptions_account
  ON tenant_adoptions (account_id, to_tenant);

INSERT OR IGNORE INTO sibyl_memory_schema_version (version, description)
VALUES (5, 'sibyl-memory-client v5 (0.9.0). Adds tenant_adoptions: the local audit of moving pre-sign-in DEFAULT_TENANT rows into the signed-in account tenant (_adopt.py), written in the same transaction as the move. Additive; no existing table changes.');


-- ============================================================================
-- Schema v6 (spec tiered-retrieval 2.B) - memory_links: typed edges across tiers
-- ============================================================================
-- Edges between nodes addressed by BUSINESS KEYS (entity: category + name;
-- reference: doc_key; state: document_key), so they survive archive_entity and
-- re-creation, which id foreign keys with ON DELETE CASCADE would not. The
-- category columns are NOT NULL: entity endpoints always carry a non-empty
-- category and reference/state endpoints store '' (SQLite UNIQUE treats NULLs
-- as distinct, so a NULL would defeat the uniqueness of reference/state edges);
-- the CHECKs enforce (tier = 'entity') = (category <> '').
-- These statements are ALSO the Python constant _links.V6_DDL (a test asserts
-- the two are identical). A fresh store gets them here; a pre-v6 store gets
-- them from Storage._ensure_schema in ONE transaction with the one-time copy of
-- entity_relations rows, so the table's existence is the crash-atomic "copied"
-- marker. from_entity_id / to_entity_id stamp entity endpoints with the row id
-- the edge was written against (NULL for reference/state, or unknown); a key now
-- held by another id reads stale (_links.STALE_SIDE_SQL). Stores created before
-- the stamp get these columns by ALTER on open (_links.apply_identity_columns).
-- entity_relations stays in place, unwritten (frozen), until R4. Nothing gates on
-- the version-6 row below (another release may claim the same number).
CREATE TABLE IF NOT EXISTS memory_links (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL,
  from_tier     TEXT NOT NULL CHECK (from_tier IN ('entity','reference','state')),
  from_category TEXT NOT NULL,
  from_key      TEXT NOT NULL,
  to_tier       TEXT NOT NULL CHECK (to_tier IN ('entity','reference','state')),
  to_category   TEXT NOT NULL,
  to_key        TEXT NOT NULL,
  relation_type TEXT NOT NULL,
  metadata      TEXT CHECK (metadata IS NULL OR json_valid(metadata)),
  created_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  from_entity_id TEXT,
  to_entity_id   TEXT,
  updated_at    TEXT,
  CHECK ((from_tier = 'entity') = (from_category <> '')),
  CHECK ((to_tier = 'entity') = (to_category <> '')),
  UNIQUE (tenant_id, from_tier, from_category, from_key, to_tier, to_category, to_key, relation_type)
);

CREATE INDEX IF NOT EXISTS memory_links_from
  ON memory_links (tenant_id, from_tier, from_category, from_key, relation_type);

CREATE INDEX IF NOT EXISTS memory_links_to
  ON memory_links (tenant_id, to_tier, to_category, to_key, relation_type);

CREATE INDEX IF NOT EXISTS memory_links_created
  ON memory_links (tenant_id, created_at, id);

INSERT OR IGNORE INTO sibyl_memory_schema_version (version, description)
VALUES (6, 'sibyl-memory-client v6. Adds memory_links: typed, directed edges between entity / reference / state nodes keyed by business keys (link, unlink, neighbors, links_of, links= on set_entity and set_reference). Pre-v6 entity_relations rows are copied once, in the same transaction that creates the table; entity_relations stays, unwritten, until it is dropped in a later major release. Additive.');

-- ============================================================================
-- Stage-0 exact-name index (spec tiered-retrieval 2.B DDL / 2.C)
-- ============================================================================
-- multi_record_search's stage 0 resolves a query that IS an entity name with a
-- parameterised `lower(name) = ?` equality (no MATCH syntax). This expression
-- index makes that one probe, not a tenant scan. Idempotent and applied on
-- every open like the rest of this file, so existing stores get it on their
-- next open (one index build, no data change). Not part of _links.V6_DDL: it
-- belongs to search, not to the memory_links migration transaction.
CREATE INDEX IF NOT EXISTS entities_tenant_lname
  ON entities (tenant_id, lower(name));