aaronrene / knowtation public
supabase-memory-migration.sql
78 lines 2.5 KB
Raw
sha256:65ccb454656ea5acdea0a10e559b78bcde1eb6ff753ecc2911bc99d1c3d7cadd feat(calendar): enforce agent context tiers in retrieval AP… Human minor ⚠ breaking 1 day 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 2 commits
sha256:65ccb454656ea5acdea0a10e559b78bcde1eb6ff753ecc2911bc99d1c3d7cadd feat(calendar): enforce agent context tiers in retrieval AP… Human minor ⚠ 1 day ago
sha256:9103f98c89257ed2b01c237cea895dabb3e85ea337dccb1161c175e4422355b6 docs: accept Calendar Events v0 spec with Phase 0 security … Human 2 days ago