aaronrene / knowtation public
supabase-memory-migration.sql
78 lines 2.5 KB
Raw
sha256:b5f647cb9c409f563d4671fe3fc05ddea01fabfed9b41fc11cb923588e1c1baf mirror: GitHub Phase A durable MCP OAuth (#270) Human minor ⚠ breaking 10 days ago
1 -- Knowtation Memory — Supabase Migration
2 -- Run this SQL in your Supabase SQL Editor to set up the memory events table.
3 -- Requires: pgvector extension (enabled by default on Supabase).
4 --
5 -- Usage:
6 -- 1. Go to Supabase Dashboard → SQL Editor
7 -- 2. Paste this file and run
8 -- 3. Set in config/local.yaml:
9 -- memory:
10 -- enabled: true
11 -- provider: supabase
12 -- supabase_url: https://your-project.supabase.co
13 -- supabase_key: your-anon-key
14 -- Or env: KNOWTATION_SUPABASE_URL, KNOWTATION_SUPABASE_KEY
15
16 -- Enable pgvector if not already enabled
17 create extension if not exists vector;
18
19 -- Memory events table
20 create table if not exists knowtation_memory_events (
21 id text primary key,
22 type text not null,
23 ts timestamptz not null default now(),
24 vault_id text not null default 'default',
25 data jsonb not null default '{}'::jsonb,
26 ttl text,
27 air_id text,
28 embedding vector(1536)
29 );
30
31 -- Indexes for common queries
32 create index if not exists idx_kme_type on knowtation_memory_events (type);
33 create index if not exists idx_kme_vault_id on knowtation_memory_events (vault_id);
34 create index if not exists idx_kme_ts on knowtation_memory_events (ts desc);
35 create index if not exists idx_kme_vault_type_ts on knowtation_memory_events (vault_id, type, ts desc);
36
37 -- pgvector index for semantic search (IVFFlat; adjust lists for scale)
38 create index if not exists idx_kme_embedding on knowtation_memory_events
39 using ivfflat (embedding vector_cosine_ops) with (lists = 100);
40
41 -- RPC function for vector similarity search (used by the Supabase provider)
42 create or replace function match_memory_events(
43 query_embedding vector(1536),
44 match_count int default 10,
45 filter_vault_id text default 'default'
46 )
47 returns table (
48 id text,
49 type text,
50 ts timestamptz,
51 vault_id text,
52 data jsonb,
53 similarity float
54 )
55 language sql stable
56 as $$
57 select
58 id,
59 type,
60 ts,
61 vault_id,
62 data,
63 1 - (embedding <=> query_embedding) as similarity
64 from knowtation_memory_events
65 where vault_id = filter_vault_id
66 and embedding is not null
67 order by embedding <=> query_embedding
68 limit match_count;
69 $$;
70
71 -- Row-level security (optional but recommended for multi-user hosted)
72 -- Uncomment and adapt if using Supabase Auth:
73 --
74 -- alter table knowtation_memory_events enable row level security;
75 --
76 -- create policy "Users can only access their own vault memories"
77 -- on knowtation_memory_events for all
78 -- using (vault_id = auth.jwt() ->> 'vault_id');
File History 4 commits
sha256:b5f647cb9c409f563d4671fe3fc05ddea01fabfed9b41fc11cb923588e1c1baf mirror: GitHub Phase A durable MCP OAuth (#270) Human minor ⚠ 10 days ago
sha256:d8c648b20a4d53b2673c5c082ee7edfa7b2fc9b11080832da1f38807b6bf940b fix(7C-L1b): route hosted delegation proposals through cani… Human minor ⚠ 30 days ago
sha256:2827ba9e7632a4b141c50caf1e8f7d77abbc3515be20e7465f2bccb0ac4edf91 fix: repair endpoint now sets has_active_subscription when … Human minor ⚠ 49 days ago
sha256:6a102aafafdfe7e70a24f4e59740200f0ee713ce7915f1b53e9d4ba5ee8b4410 Initial Muse snapshot Human 82 days ago