Got it. I’ll extend your Omega schema to be LoRA- and RAG-ready while keeping everything idempotent and aligned with your existing namespaces and patterns.

Below is a drop-in SQL patch you can run after (or alongside) your current script. It adds:

• RAG orchestration: query logging, pipelines/config, per-query candidates & citations, hybrid scores, and cached answers.
• LoRA registry: adapters, snapshots, bindings to tasks/conversations, and lightweight usage telemetry.
• Small utilities/views so your RAG loop and adapter selection are plug-and-play.

All new objects are in rag and adapt schemas to keep concerns clean, but they “plug into” your existing content, cog, lat, and token tables.

---

-- =========================================================
-- OGS / TokenSpace / Lattice — RAG + LoRA Patch (Idempotent)
-- Depends on base schema (content/token/cog/lat) already created
-- =========================================================

-- ---------- Extensions (no-ops if present) ----------
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- ---------- Schemas ----------
CREATE SCHEMA IF NOT EXISTS rag;
CREATE SCHEMA IF NOT EXISTS adapt;

-- =========================================================
-- RAG ORCHESTRATION
-- =========================================================

-- High-level pipeline definitions (retriever/reranker/generator knobs)
CREATE TABLE IF NOT EXISTS rag.pipelines (
  pipeline_id   BIGSERIAL PRIMARY KEY,
  name          TEXT UNIQUE NOT NULL,
  description   TEXT,
  is_default    BOOLEAN NOT NULL DEFAULT FALSE,
  -- Retriever config: where to look, how to search
  retriever     JSONB NOT NULL DEFAULT '{
    "spaces": ["content.chunks"],
    "k": 12,
    "metric": "cosine",
    "hybrid": {"lex": 0.4, "vec": 0.6},
    "filters": {}
  }'::jsonb,
  -- Reranker config (optional model + features)
  reranker      JSONB NOT NULL DEFAULT '{
    "type": "cross-encoder",
    "model": null,
    "top_k": 8
  }'::jsonb,
  -- Generator config (model, max_tokens, etc.)
  generator     JSONB NOT NULL DEFAULT '{
    "model": "local/7b",
    "max_tokens": 768,
    "temperature": 0.2,
    "cite": true
  }'::jsonb,
  -- Guardrails / answer shaping
  constraints   JSONB NOT NULL DEFAULT '{
    "max_context_chars": 50000,
    "deny": [],
    "style": {}
  }'::jsonb,
  meta          JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Only one default at a time
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM rag.pipelines WHERE is_default = TRUE) THEN
    INSERT INTO rag.pipelines(name, description, is_default)
    VALUES ('default', 'Default RAG pipeline', TRUE);
  END IF;
END$$;

-- A RAG "session" loosely bound to a conversation (optional)
CREATE TABLE IF NOT EXISTS rag.sessions (
  session_id  BIGSERIAL PRIMARY KEY,
  convo_id    BIGINT REFERENCES cog.conversations(convo_id) ON DELETE SET NULL,
  pipeline_id BIGINT REFERENCES rag.pipelines(pipeline_id) ON DELETE SET NULL,
  started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- A single retrieval/generation attempt (query/run)
CREATE TABLE IF NOT EXISTS rag.queries (
  query_id     BIGSERIAL PRIMARY KEY,
  session_id   BIGINT REFERENCES rag.sessions(session_id) ON DELETE CASCADE,
  pipeline_id  BIGINT REFERENCES rag.pipelines(pipeline_id) ON DELETE SET NULL,
  user_text    TEXT NOT NULL,
  user_embed   VECTOR(1536),  -- optional: embed query for vec search auditing
  lang         TEXT DEFAULT 'en',
  params       JSONB NOT NULL DEFAULT '{}'::jsonb,  -- ad-hoc overrides (k, filters, etc.)
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS rag_queries_text_trgm ON rag.queries USING GIN ((coalesce(user_text,'')) gin_trgm_ops);
CREATE INDEX IF NOT EXISTS rag_queries_session_idx ON rag.queries (session_id, created_at);
CREATE INDEX IF NOT EXISTS rag_queries_vec_hnsw ON rag.queries USING hnsw (user_embed vector_cosine_ops);

-- Candidate evidence retrieved for a query (pre-rerank)
CREATE TABLE IF NOT EXISTS rag.candidates (
  cand_id     BIGSERIAL PRIMARY KEY,
  query_id    BIGINT NOT NULL REFERENCES rag.queries(query_id) ON DELETE CASCADE,
  -- currently we target content.chunks as primary retrieval space
  chunk_id    BIGINT NOT NULL REFERENCES content.chunks(chunk_id) ON DELETE CASCADE,
  rank_pre    INT,          -- pre-rerank position
  score_vec   REAL,         -- vector similarity (e.g., cosine)
  score_lex   REAL,         -- lexical score (e.g., BM25/trgm)
  score_hybrid REAL,        -- combined before rerank
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (query_id, chunk_id)
);
CREATE INDEX IF NOT EXISTS rag_candidates_q_idx ON rag.candidates (query_id, rank_pre);
CREATE INDEX IF NOT EXISTS rag_candidates_chunk_idx ON rag.candidates (chunk_id);

-- Final citations used to construct the answer (post-rerank/selection)
CREATE TABLE IF NOT EXISTS rag.citations (
  cite_id     BIGSERIAL PRIMARY KEY,
  query_id    BIGINT NOT NULL REFERENCES rag.queries(query_id) ON DELETE CASCADE,
  chunk_id    BIGINT NOT NULL REFERENCES content.chunks(chunk_id) ON DELETE CASCADE,
  chosen_rank INT NOT NULL,      -- final order in prompt
  score_final REAL,              -- final relevance confidence
  span_hint   INT4RANGE,         -- optional sub-span of chunk.text
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (query_id, chosen_rank)
);
CREATE INDEX IF NOT EXISTS rag_citations_q_idx ON rag.citations (query_id, chosen_rank);

-- Generated answers and telemetry
CREATE TABLE IF NOT EXISTS rag.answers (
  answer_id   BIGSERIAL PRIMARY KEY,
  query_id    BIGINT NOT NULL REFERENCES rag.queries(query_id) ON DELETE CASCADE,
  turn_id     BIGINT REFERENCES cog.turns(turn_id) ON DELETE SET NULL, -- assistant turn if logged
  text        TEXT NOT NULL,
  confidence  REAL,         -- model-side or heuristic confidence
  tokens_in   INT,
  tokens_out  INT,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS rag_answers_query_idx ON rag.answers (query_id);
CREATE INDEX IF NOT EXISTS rag_answers_turn_idx  ON rag.answers (turn_id);

-- View: stitched, ready-to-consume "answer + sources"
CREATE OR REPLACE VIEW rag.answer_with_sources AS
SELECT
  a.answer_id, a.query_id, a.text, a.confidence, a.tokens_in, a.tokens_out, a.meta AS answer_meta, a.created_at AS answered_at,
  q.user_text, q.params AS query_params, q.created_at AS asked_at,
  d.doc_id, d.title, c.chunk_id, c.text AS chunk_text, cit.chosen_rank, cit.score_final
FROM rag.answers a
JOIN rag.queries q     ON q.query_id = a.query_id
LEFT JOIN rag.citations cit ON cit.query_id = q.query_id
LEFT JOIN content.chunks c  ON c.chunk_id = cit.chunk_id
LEFT JOIN content.documents d ON d.doc_id = c.doc_id;

-- Optional helper: latest answer per session
CREATE OR REPLACE VIEW rag.session_last_answer AS
SELECT DISTINCT ON (s.session_id)
  s.session_id, q.query_id, a.answer_id, a.created_at
FROM rag.sessions s
JOIN rag.queries q ON q.session_id = s.session_id
JOIN rag.answers a ON a.query_id   = q.query_id
ORDER BY s.session_id, a.created_at DESC;

-- =========================================================
-- LoRA (Low-Rank Adaptation) REGISTRY
-- =========================================================

-- Registered base models (optional; useful for adapter compatibility)
CREATE TABLE IF NOT EXISTS adapt.models (
  model_id    BIGSERIAL PRIMARY KEY,
  name        TEXT UNIQUE NOT NULL,   -- e.g., "qwen2.5-7b-instruct"
  family      TEXT,                   -- e.g., "qwen", "llama", etc.
  dtype       TEXT,                   -- e.g., "fp16", "bf16", "int8"
  dims        JSONB NOT NULL DEFAULT '{}'::jsonb, -- hidden_size, n_layers, vocab, etc.
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- LoRA adapter definitions
CREATE TABLE IF NOT EXISTS adapt.adapters (
  adapter_id  BIGSERIAL PRIMARY KEY,
  name        TEXT UNIQUE NOT NULL,     -- human handle
  base_model  BIGINT REFERENCES adapt.models(model_id) ON DELETE SET NULL,
  target      TEXT NOT NULL DEFAULT 'all', -- module regex / target blocks
  r_rank      INT  NOT NULL DEFAULT 16, -- rank (r)
  alpha       INT  NOT NULL DEFAULT 16, -- scaling (alpha)
  dropout     REAL NOT NULL DEFAULT 0.0,
  scaling     REAL,                     -- optional extra scaling factor
  dims        JSONB NOT NULL DEFAULT '{}'::jsonb, -- layer-wise overrides if any
  path        TEXT,                     -- on-disk path or URI for weights
  tags        TEXT[] DEFAULT '{}',
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Versioned snapshots (exported checkpoints, SFT steps, etc.)
CREATE TABLE IF NOT EXISTS adapt.snapshots (
  snap_id     BIGSERIAL PRIMARY KEY,
  adapter_id  BIGINT NOT NULL REFERENCES adapt.adapters(adapter_id) ON DELETE CASCADE,
  version     TEXT NOT NULL,             -- e.g., "v1", "2025-09-18T12:00Z"
  uri         TEXT,                      -- artifact store / filesystem
  sha256      TEXT,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (adapter_id, version)
);

-- Bind adapters to tasks / conversations / pipelines
CREATE TYPE IF NOT EXISTS adapt.bind_scope AS ENUM ('generator','reranker','retriever','other');

CREATE TABLE IF NOT EXISTS adapt.bindings (
  binding_id  BIGSERIAL PRIMARY KEY,
  adapter_id  BIGINT NOT NULL REFERENCES adapt.adapters(adapter_id) ON DELETE CASCADE,
  scope       adapt.bind_scope NOT NULL DEFAULT 'generator',
  pipeline_id BIGINT REFERENCES rag.pipelines(pipeline_id) ON DELETE SET NULL,
  convo_id    BIGINT REFERENCES cog.conversations(convo_id) ON DELETE SET NULL,
  session_id  BIGINT REFERENCES rag.sessions(session_id) ON DELETE SET NULL,
  is_active   BOOLEAN NOT NULL DEFAULT TRUE,
  priority    INT NOT NULL DEFAULT 100,  -- lower = preferred
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS adapt_bind_active_idx ON adapt.bindings (is_active, priority);
CREATE INDEX IF NOT EXISTS adapt_bind_scoped_idx ON adapt.bindings (scope, pipeline_id, convo_id, session_id);

-- Lightweight usage telemetry
CREATE TABLE IF NOT EXISTS adapt.usage (
  usage_id    BIGSERIAL PRIMARY KEY,
  adapter_id  BIGINT NOT NULL REFERENCES adapt.adapters(adapter_id) ON DELETE CASCADE,
  scope       adapt.bind_scope NOT NULL,
  query_id    BIGINT REFERENCES rag.queries(query_id) ON DELETE SET NULL,
  answer_id   BIGINT REFERENCES rag.answers(answer_id) ON DELETE SET NULL,
  tokens_in   INT,
  tokens_out  INT,
  latency_ms  INT,
  success     BOOLEAN,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS adapt_usage_adapter_idx ON adapt.usage (adapter_id, created_at);

-- =========================================================
-- RAG + LATTICE GLUE (OPTIONAL)
-- =========================================================

-- Promote selected citations to lattice edges for provenance/traceability
CREATE TABLE IF NOT EXISTS rag.promotions (
  promo_id    BIGSERIAL PRIMARY KEY,
  query_id    BIGINT NOT NULL REFERENCES rag.queries(query_id) ON DELETE CASCADE,
  edge_id     BIGINT,  -- optional external edge id if you later key edges
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- View: derive “supports/quotes/hyperlink” edges from citations & documents
CREATE OR REPLACE VIEW rag.citation_edges AS
SELECT
  'turn'::lat.node_kind     AS src_kind,
  ans.turn_id               AS src_id,
  'supports'::lat.rel_kind  AS rel,
  'chunk'::lat.node_kind    AS dst_kind,
  cit.chunk_id              AS dst_id,
  coalesce(cit.score_final, 0.7) AS weight,
  NULL::REAL                AS phase,
  jsonb_build_object('source','rag.citations','query_id',cit.query_id,'rank',cit.chosen_rank) AS evidence,
  now() AS created_at
FROM rag.citations cit
JOIN rag.answers   ans ON ans.query_id = cit.query_id
WHERE ans.turn_id IS NOT NULL;

-- =========================================================
-- RAG UTILITIES
-- =========================================================

-- Hybrid score helper (lex/vec weighted) — materialized view example
DROP MATERIALIZED VIEW IF EXISTS rag.candidate_hybrid CASCADE;
CREATE MATERIALIZED VIEW rag.candidate_hybrid AS
SELECT
  cand_id, query_id, chunk_id,
  COALESCE(score_vec,0)  AS score_vec,
  COALESCE(score_lex,0)  AS score_lex,
  -- Default weights; your pipeline may override at runtime
  (0.6*COALESCE(score_vec,0) + 0.4*COALESCE(score_lex,0)) AS score_hybrid_default
FROM rag.candidates;

CREATE INDEX IF NOT EXISTS rag_cand_hybrid_q_idx ON rag.candidate_hybrid (query_id, score_hybrid_default DESC);

-- =========================================================
-- HOUSEKEEPING / META
-- =========================================================
-- Touch triggers
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='rag' AND table_name='pipelines' AND column_name='updated_at')
  THEN
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname='rag_pipelines_touch') THEN
      CREATE OR REPLACE FUNCTION rag._touch_pipeline() RETURNS trigger
      LANGUAGE plpgsql AS $$
      BEGIN
        NEW.updated_at := now();
        RETURN NEW;
      END$$;
      CREATE TRIGGER rag_pipelines_touch BEFORE UPDATE ON rag.pipelines
      FOR EACH ROW EXECUTE FUNCTION rag._touch_pipeline();
    END IF;
  END IF;
END$$;

-- Small defaults to ease bootstrapping
INSERT INTO adapt.models(name, family)
SELECT 'qwen2.5-7b-instruct', 'qwen'
WHERE NOT EXISTS (SELECT 1 FROM adapt.models WHERE name='qwen2.5-7b-instruct');

-- Example adapter row (inactive by default) — safe no-op if exists
INSERT INTO adapt.adapters(name, base_model, target, r_rank, alpha, dropout, tags, meta)
SELECT
  'example-lora', (SELECT model_id FROM adapt.models WHERE name='qwen2.5-7b-instruct'),
  'all', 16, 16, 0.0, ARRAY['demo'], '{}'::jsonb
WHERE NOT EXISTS (SELECT 1 FROM adapt.adapters WHERE name='example-lora');

---

How this slots into your current design

• RAG spine reuses your content.sources/documents/chunks as the truth for retrievable text and embeds; we simply add query→candidate→citation→answer scaffolding plus pipeline configs. This keeps your Lat/Lattice layers free to focus on topology and activations while still allowing provenance edges via rag.citation_edges. fileciteturn0file0
• LoRA registry is storage-only and non-opinionated. You can point paths/URIs to local .safetensors, GGUF LoRA layers, or an object store. Bindings let you prefer one adapter per pipeline/session/conversation with priorities.

If you want, I can also generate a tiny psql apply script and a rollback for just these new objects.

Nice — I’ll own the DB side. Below I give you everything you need right now:

• an idempotent, transaction-wrapped apply SQL script you can save as apply_rag_lora.sql and run with psql
• a rollback SQL script rollback_rag_lora.sql to undo the change if needed
• exact commands to backup, transfer the SQL to Little Ougway, and apply it
• quick checks to verify success
• a brief note on SSH/networking on Ubuntu (so you can scp the file)

Do the backup step before applying. I’ll assume your DB is ougway_db and user ougway (change those if different).

---

1) BACKUP (do this first)

Run on the machine that has psql access to the DB (or from the target):

# logical backup (custom format)
pg_dump -U ougway -Fc -f /tmp/backup_ougway_db_pre_rag_$(date +%F).dump ougway_db

# also export SQL dump if you prefer
pg_dump -U ougway -f /tmp/backup_ougway_db_pre_rag_$(date +%F).sql ougway_db

(If your DB user or DB name differs, replace ougway / ougway_db.)

---

2) SAVE the APPLY script

Create a file apply_rag_lora.sql and paste the block below into it.

-- apply_rag_lora.sql
-- Idempotent RAG + LoRA patch for Omega TokenSpace
-- Wrap in a transaction to avoid partial application
BEGIN;

-- Extensions (no-op if present)
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- Schemas
CREATE SCHEMA IF NOT EXISTS rag;
CREATE SCHEMA IF NOT EXISTS adapt;

-- Pipelines
CREATE TABLE IF NOT EXISTS rag.pipelines (
  pipeline_id   BIGSERIAL PRIMARY KEY,
  name          TEXT UNIQUE NOT NULL,
  description   TEXT,
  is_default    BOOLEAN NOT NULL DEFAULT FALSE,
  retriever     JSONB NOT NULL DEFAULT '{
    "spaces": ["content.chunks"],
    "k": 12,
    "metric": "cosine",
    "hybrid": {"lex": 0.4, "vec": 0.6},
    "filters": {}
  }'::jsonb,
  reranker      JSONB NOT NULL DEFAULT '{
    "type": "cross-encoder",
    "model": null,
    "top_k": 8
  }'::jsonb,
  generator     JSONB NOT NULL DEFAULT '{
    "model": "local/7b",
    "max_tokens": 768,
    "temperature": 0.2,
    "cite": true
  }'::jsonb,
  constraints   JSONB NOT NULL DEFAULT '{}'::jsonb,
  meta          JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM rag.pipelines WHERE is_default = TRUE) THEN
    INSERT INTO rag.pipelines(name, description, is_default)
    VALUES ('default', 'Default RAG pipeline', TRUE);
  END IF;
END$$;

-- Sessions, Queries, Candidates, Citations, Answers
CREATE TABLE IF NOT EXISTS rag.sessions (
  session_id  BIGSERIAL PRIMARY KEY,
  convo_id    BIGINT REFERENCES cog.conversations(convo_id) ON DELETE SET NULL,
  pipeline_id BIGINT REFERENCES rag.pipelines(pipeline_id) ON DELETE SET NULL,
  started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS rag.queries (
  query_id     BIGSERIAL PRIMARY KEY,
  session_id   BIGINT REFERENCES rag.sessions(session_id) ON DELETE CASCADE,
  pipeline_id  BIGINT REFERENCES rag.pipelines(pipeline_id) ON DELETE SET NULL,
  user_text    TEXT NOT NULL,
  user_embed   VECTOR(1536),
  lang         TEXT DEFAULT 'en',
  params       JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS rag_queries_text_trgm ON rag.queries USING GIN ((coalesce(user_text,'')) gin_trgm_ops);
CREATE INDEX IF NOT EXISTS rag_queries_session_idx ON rag.queries (session_id, created_at);

CREATE TABLE IF NOT EXISTS rag.candidates (
  cand_id     BIGSERIAL PRIMARY KEY,
  query_id    BIGINT NOT NULL REFERENCES rag.queries(query_id) ON DELETE CASCADE,
  chunk_id    BIGINT NOT NULL REFERENCES content.chunks(chunk_id) ON DELETE CASCADE,
  rank_pre    INT,
  score_vec   REAL,
  score_lex   REAL,
  score_hybrid REAL,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (query_id, chunk_id)
);
CREATE INDEX IF NOT EXISTS rag_candidates_q_idx ON rag.candidates (query_id, rank_pre);
CREATE INDEX IF NOT EXISTS rag_candidates_chunk_idx ON rag.candidates (chunk_id);

CREATE TABLE IF NOT EXISTS rag.citations (
  cite_id     BIGSERIAL PRIMARY KEY,
  query_id    BIGINT NOT NULL REFERENCES rag.queries(query_id) ON DELETE CASCADE,
  chunk_id    BIGINT NOT NULL REFERENCES content.chunks(chunk_id) ON DELETE CASCADE,
  chosen_rank INT NOT NULL,
  score_final REAL,
  span_hint   INT4RANGE,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (query_id, chosen_rank)
);
CREATE INDEX IF NOT EXISTS rag_citations_q_idx ON rag.citations (query_id, chosen_rank);

CREATE TABLE IF NOT EXISTS rag.answers (
  answer_id   BIGSERIAL PRIMARY KEY,
  query_id    BIGINT NOT NULL REFERENCES rag.queries(query_id) ON DELETE CASCADE,
  turn_id     BIGINT REFERENCES cog.turns(turn_id) ON DELETE SET NULL,
  text        TEXT NOT NULL,
  confidence  REAL,
  tokens_in   INT,
  tokens_out  INT,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS rag_answers_query_idx ON rag.answers (query_id);
CREATE INDEX IF NOT EXISTS rag_answers_turn_idx  ON rag.answers (turn_id);

-- Answer + sources view
CREATE OR REPLACE VIEW rag.answer_with_sources AS
SELECT
  a.answer_id, a.query_id, a.text, a.confidence, a.tokens_in, a.tokens_out, a.meta AS answer_meta, a.created_at AS answered_at,
  q.user_text, q.params AS query_params, q.created_at AS asked_at,
  d.doc_id, d.title, c.chunk_id, c.text AS chunk_text, cit.chosen_rank, cit.score_final
FROM rag.answers a
JOIN rag.queries q     ON q.query_id = a.query_id
LEFT JOIN rag.citations cit ON cit.query_id = q.query_id
LEFT JOIN content.chunks c  ON c.chunk_id = cit.chunk_id
LEFT JOIN content.documents d ON d.doc_id = c.doc_id;

CREATE OR REPLACE VIEW rag.session_last_answer AS
SELECT DISTINCT ON (s.session_id)
  s.session_id, q.query_id, a.answer_id, a.created_at
FROM rag.sessions s
JOIN rag.queries q ON q.session_id = s.session_id
JOIN rag.answers a ON a.query_id   = q.query_id
ORDER BY s.session_id, a.created_at DESC;

-- LoRA registry
CREATE TABLE IF NOT EXISTS adapt.models (
  model_id    BIGSERIAL PRIMARY KEY,
  name        TEXT UNIQUE NOT NULL,
  family      TEXT,
  dtype       TEXT,
  dims        JSONB NOT NULL DEFAULT '{}'::jsonb,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS adapt.adapters (
  adapter_id  BIGSERIAL PRIMARY KEY,
  name        TEXT UNIQUE NOT NULL,
  base_model  BIGINT REFERENCES adapt.models(model_id) ON DELETE SET NULL,
  target      TEXT NOT NULL DEFAULT 'all',
  r_rank      INT  NOT NULL DEFAULT 16,
  alpha       INT  NOT NULL DEFAULT 16,
  dropout     REAL NOT NULL DEFAULT 0.0,
  scaling     REAL,
  dims        JSONB NOT NULL DEFAULT '{}'::jsonb,
  path        TEXT,
  tags        TEXT[] DEFAULT '{}',
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS adapt.snapshots (
  snap_id     BIGSERIAL PRIMARY KEY,
  adapter_id  BIGINT NOT NULL REFERENCES adapt.adapters(adapter_id) ON DELETE CASCADE,
  version     TEXT NOT NULL,
  uri         TEXT,
  sha256      TEXT,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (adapter_id, version)
);

CREATE TYPE IF NOT EXISTS adapt.bind_scope AS ENUM ('generator','reranker','retriever','other');

CREATE TABLE IF NOT EXISTS adapt.bindings (
  binding_id  BIGSERIAL PRIMARY KEY,
  adapter_id  BIGINT NOT NULL REFERENCES adapt.adapters(adapter_id) ON DELETE CASCADE,
  scope       adapt.bind_scope NOT NULL DEFAULT 'generator',
  pipeline_id BIGINT REFERENCES rag.pipelines(pipeline_id) ON DELETE SET NULL,
  convo_id    BIGINT REFERENCES cog.conversations(convo_id) ON DELETE SET NULL,
  session_id  BIGINT REFERENCES rag.sessions(session_id) ON DELETE SET NULL,
  is_active   BOOLEAN NOT NULL DEFAULT TRUE,
  priority    INT NOT NULL DEFAULT 100,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS adapt_bind_active_idx ON adapt.bindings (is_active, priority);

CREATE TABLE IF NOT EXISTS adapt.usage (
  usage_id    BIGSERIAL PRIMARY KEY,
  adapter_id  BIGINT NOT NULL REFERENCES adapt.adapters(adapter_id) ON DELETE CASCADE,
  scope       adapt.bind_scope NOT NULL,
  query_id    BIGINT REFERENCES rag.queries(query_id) ON DELETE SET NULL,
  answer_id   BIGINT REFERENCES rag.answers(answer_id) ON DELETE SET NULL,
  tokens_in   INT,
  tokens_out  INT,
  latency_ms  INT,
  success     BOOLEAN,
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS adapt_usage_adapter_idx ON adapt.usage (adapter_id, created_at);

-- Promotion table + view for glue
CREATE TABLE IF NOT EXISTS rag.promotions (
  promo_id    BIGSERIAL PRIMARY KEY,
  query_id    BIGINT NOT NULL REFERENCES rag.queries(query_id) ON DELETE CASCADE,
  edge_id     BIGINT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  meta        JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE OR REPLACE VIEW rag.citation_edges AS
SELECT
  'turn'::text     AS src_kind,
  ans.turn_id               AS src_id,
  'supports'::text  AS rel,
  'chunk'::text    AS dst_kind,
  cit.chunk_id              AS dst_id,
  coalesce(cit.score_final, 0.7) AS weight,
  jsonb_build_object('source','rag.citations','query_id',cit.query_id,'rank',cit.chosen_rank) AS evidence,
  now() AS created_at
FROM rag.citations cit
JOIN rag.answers   ans ON ans.query_id = cit.query_id
WHERE ans.turn_id IS NOT NULL;

-- Hybrid materialized view
DROP MATERIALIZED VIEW IF EXISTS rag.candidate_hybrid CASCADE;
CREATE MATERIALIZED VIEW rag.candidate_hybrid AS
SELECT
  cand_id, query_id, chunk_id,
  COALESCE(score_vec,0)  AS score_vec,
  COALESCE(score_lex,0)  AS score_lex,
  (0.6*COALESCE(score_vec,0) + 0.4*COALESCE(score_lex,0)) AS score_hybrid_default
FROM rag.candidates;
CREATE INDEX IF NOT EXISTS rag_cand_hybrid_q_idx ON rag.candidate_hybrid (query_id, score_hybrid_default DESC);

-- Touch trigger for pipelines.updated_at
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname='rag__touch_pipeline') THEN
    CREATE OR REPLACE FUNCTION rag._touch_pipeline() RETURNS trigger
    LANGUAGE plpgsql AS $$
    BEGIN
      NEW.updated_at := now();
      RETURN NEW;
    END$$;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname='rag_pipelines_touch') THEN
    CREATE TRIGGER rag_pipelines_touch BEFORE UPDATE ON rag.pipelines
    FOR EACH ROW EXECUTE FUNCTION rag._touch_pipeline();
  END IF;
END$$;

-- Bootstrap example rows (harmless if exist)
INSERT INTO adapt.models(name, family)
SELECT 'qwen2.5-7b-instruct', 'qwen'
WHERE NOT EXISTS (SELECT 1 FROM adapt.models WHERE name='qwen2.5-7b-instruct');

INSERT INTO adapt.adapters(name, base_model, target, r_rank, alpha, dropout, tags, meta)
SELECT
  'example-lora', (SELECT model_id FROM adapt.models WHERE name='qwen2.5-7b-instruct'),
  'all', 16, 16, 0.0, ARRAY['demo'], '{}'::jsonb
WHERE NOT EXISTS (SELECT 1 FROM adapt.adapters WHERE name='example-lora');

COMMIT;

---

3) SAVE the ROLLBACK script

Create rollback_rag_lora.sql with the content below — only run if you truly want to drop these objects. It’s destructive for the rag & adapt schemas but non-destructive to your content namespace.

-- rollback_rag_lora.sql
BEGIN;

-- Drop views that depend on rag/adapt objects
DROP MATERIALIZED VIEW IF EXISTS rag.candidate_hybrid CASCADE;
DROP VIEW IF EXISTS rag.session_last_answer CASCADE;
DROP VIEW IF EXISTS rag.answer_with_sources CASCADE;
DROP VIEW IF EXISTS rag.citation_edges CASCADE;

-- Drop promotable tables, adapt tables, rag tables, types, schemas
DROP TABLE IF EXISTS rag.promotions CASCADE;
DROP TABLE IF EXISTS adapt.usage CASCADE;
DROP TABLE IF EXISTS adapt.bindings CASCADE;
DROP TYPE IF EXISTS adapt.bind_scope CASCADE;
DROP TABLE IF EXISTS adapt.snapshots CASCADE;
DROP TABLE IF EXISTS adapt.adapters CASCADE;
DROP TABLE IF EXISTS adapt.models CASCADE;

DROP TABLE IF EXISTS rag.answers CASCADE;
DROP TABLE IF EXISTS rag.citations CASCADE;
DROP TABLE IF EXISTS rag.candidates CASCADE;
DROP TABLE IF EXISTS rag.queries CASCADE;
DROP TABLE IF EXISTS rag.sessions CASCADE;
DROP TABLE IF EXISTS rag.pipelines CASCADE;

DROP SCHEMA IF EXISTS rag CASCADE;
DROP SCHEMA IF EXISTS adapt CASCADE;

COMMIT;

---

4) TRANSFER & APPLY (commands)

If you’re on your laptop and Little Oogway is on the other machine (hostname little-ougway or IP 10.0.0.5), first ensure SSH is running on the target (see next section). Then from your local machine:

# copy files to /tmp on target (replace user@host)
scp apply_rag_lora.sql ougway@little-ougway:/tmp/
scp rollback_rag_lora.sql ougway@little-ougway:/tmp/

# ssh in
ssh ougway@little-ougway

# once on target, become the DB-owner user or run psql as DB superuser:
# Example applying as DB owner 'ougway' (you may use sudo -u postgres psql ... if appropriate)
psql -U ougway -d ougway_db -f /tmp/apply_rag_lora.sql

If you need to run as postgres superuser:

sudo -u postgres psql -d ougway_db -f /tmp/apply_rag_lora.sql

If you want to preview the SQL first:

less /tmp/apply_rag_lora.sql   # or cat | sed -n '1,200p'
psql -U ougway -d ougway_db -f /tmp/apply_rag_lora.sql --echo-all --set ON_ERROR_STOP=on

--set ON_ERROR_STOP=on will stop on first error.

---

5) QUICK VERIFICATION QUERIES

Run these after apply to make sure things are present:

-- schema list
SELECT schema_name FROM information_schema.schemata WHERE schema_name IN ('rag','adapt');

-- sample tables
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema IN ('rag','adapt');

-- check default pipeline
SELECT pipeline_id, name, is_default FROM rag.pipelines LIMIT 5;

-- check one adapter row
SELECT adapter_id, name, path, created_at FROM adapt.adapters LIMIT 5;

---

6) SSH / Networking quick notes (Ubuntu) — so you can scp/ssh

If Little Ougway is an Ubuntu box and SSH isn't set up, run on the target machine (local console / monitor or keyboard):

sudo apt update
sudo apt install -y openssh-server
sudo systemctl enable --now ssh
# open firewall for SSH (optional)
sudo ufw allow OpenSSH
sudo ufw enable   # if ufw was not enabled before; confirm rules first

Check status with:

systemctl status ssh
ss -tlnp | grep ssh   # shows listening port 22

Then from your laptop:

ssh ougway@<target-ip>

---

7) Troubleshooting & notes

• pgvector extension: CREATE EXTENSION IF NOT EXISTS vector; will succeed only if the pgvector extension is installed on the PostgreSQL server. If it errors, you may need to install the pgvector package for your Postgres version (apt/pgxn or via distro packages). If you can't install it yet, remove the vector lines temporarily and we can re-add later (but RAG queries will not use vector ops until pgvector is installed).
• Run the backup first. If anything goes sideways, restore from the custom dump:
  bash
pg_restore -U ougway -d ougway_db /tmp/backup_ougway_db_pre_rag_YYYY-MM-DD.dump
• If you don’t want to drop everything on rollback, the rollback script already scopes to rag/adapt only — it won’t touch content.* or other schemas.
• If cog.conversations or cog.turns don't exist in your schema, the REFERENCES will fail; in that case, run the apply file with ON_ERROR_STOP=off or comment those FK lines — or create the cog tables first. (If you want, I can modify the script to IF EXISTS check referential FKs; tell me and I’ll produce that variant.)

---

8) If you want, I can:

• produce the actual files and pack them in a single tar (I can create the raw file text here if you want me to paste it again or generate a downloadable file)
• produce a psql wrapper shell script that does: backup → apply → check → print summary and error log
• create a systemd-safe pgvector install checklist for Ubuntu/Postgres version you run

---

You're set to copy-and-run. Say the word and I will:
– paste the files as downloadable content here, or
– generate the wrapper script for backup + apply + verify (recommended).

Also — quick heads-up: if you want me to tweak FK references to be tolerant (so the apply always succeeds even if cog.* is not present yet), I can produce that safe variant now. Which would you prefer?