Spaces:
Running
Running
File size: 3,406 Bytes
28a08e7 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | -- GAP-SUP1: Supabase Skill Tracker persistence
-- Eseguire nel SQL Editor di Supabase:
-- https://app.supabase.com/project/_/sql/new
--
-- Tabella skill_stats: statistiche tool per sessione (backend skill_tracker.py)
-- Tabella skill_patterns: pattern tool sequenze (frontend skillPersistence.ts)
-- ─── skill_stats ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS skill_stats (
session_id text NOT NULL,
tool_name text NOT NULL,
success_count integer NOT NULL DEFAULT 0,
fail_count integer NOT NULL DEFAULT 0,
last_used double precision NOT NULL DEFAULT 0,
total_latency_ms double precision NOT NULL DEFAULT 0,
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (session_id, tool_name)
);
-- Index per query per sessione (GET ?session_id=eq.xxx)
CREATE INDEX IF NOT EXISTS idx_skill_stats_session ON skill_stats (session_id);
-- Row Level Security: anon può leggere e scrivere (chiave anon pubblica)
ALTER TABLE skill_stats ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_policies
WHERE tablename = 'skill_stats' AND policyname = 'anon full access'
) THEN
CREATE POLICY "anon full access" ON skill_stats
FOR ALL TO anon
USING (true)
WITH CHECK (true);
END IF;
END $$;
-- Trigger: aggiorna updated_at ad ogni upsert
CREATE OR REPLACE FUNCTION _touch_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;
DROP TRIGGER IF EXISTS trg_skill_stats_updated_at ON skill_stats;
CREATE TRIGGER trg_skill_stats_updated_at
BEFORE UPDATE ON skill_stats
FOR EACH ROW EXECUTE FUNCTION _touch_updated_at();
-- ─── skill_patterns ────────────────────────────────────────────────────────────
-- Persistenza cross-device per skillPersistence.ts (frontend Dexie → Supabase cloud)
CREATE TABLE IF NOT EXISTS skill_patterns (
id text PRIMARY KEY, -- djb2(taskSignature)
task_signature text NOT NULL,
tool_sequence text[] NOT NULL,
success_count integer NOT NULL DEFAULT 0,
total_count integer NOT NULL DEFAULT 0,
last_used bigint NOT NULL DEFAULT 0,
confidence double precision NOT NULL DEFAULT 0,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_skill_patterns_confidence ON skill_patterns (confidence DESC);
CREATE INDEX IF NOT EXISTS idx_skill_patterns_last_used ON skill_patterns (last_used DESC);
ALTER TABLE skill_patterns ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_policies
WHERE tablename = 'skill_patterns' AND policyname = 'anon full access'
) THEN
CREATE POLICY "anon full access" ON skill_patterns
FOR ALL TO anon
USING (true)
WITH CHECK (true);
END IF;
END $$;
DROP TRIGGER IF EXISTS trg_skill_patterns_updated_at ON skill_patterns;
CREATE TRIGGER trg_skill_patterns_updated_at
BEFORE UPDATE ON skill_patterns
FOR EACH ROW EXECUTE FUNCTION _touch_updated_at();
|