| name | db-migration |
| description | Write a correct Drizzle schema change plus migration and Row-Level Security policy for MemOS. Use when adding or altering any table, index, or RLS policy. |
MemOS Database Migration
Schema changes are schema-as-code via Drizzle, then a generated SQL migration, then an RLS policy. Multi-tenant isolation is enforced at the DB — every table that holds tenant data MUST have RLS. Never hand-edit the live DB.
Steps
1. Update the Drizzle schema — packages/api/src/db/schema.ts
- Add/alter the table with correct column types, FKs, defaults.
- Every tenant-scoped table carries
project_id (and/or team_id).
- Add indexes for every column you'll filter/join on (
project_id, bd_id, agent_id, created_at, and embedding via an ivfflat/hnsw index for vector columns).
- For semantic search tables (
facts, learnings) include embedding vector(1536).
2. Generate the migration
npx drizzle-kit generate → review the SQL in infra/migrations/. Read it; never apply blind.
3. Write the RLS policy (in the same migration or a paired one)
For each tenant-scoped table:
alter table <t> enable row level security;
create policy <t>_tenant_read on <t> for select
using (project_id = any(current_setting('memos.agent_projects', true)::text[]));
create policy <t>_tenant_write on <t> for insert
with check (project_id = any(current_setting('memos.agent_projects', true)::text[]));
The gateway sets memos.agent_projects per request from the authed token's scopes (via set_config). This is the real isolation boundary.
4. Apply locally and verify
pnpm db:migrate. Then prove isolation with a quick test: an agent scoped to project A cannot select project B's rows.
5. Document
If this is a non-trivial modeling choice (e.g. one table serving two roles, vector index type), write/append an ADR via the write-adr skill, and update docs/DATA_MODEL.md (ER diagram + the new table's fields/indexes).
Invariants to never break
- No tenant-scoped table without RLS.
- No
select * path that bypasses the project filter.
- FKs preserve the provenance chain (fact/learning/artifact/checkin →
bd_id; workflow_run → target_objective_id).
Done when
Schema updated, migration reviewed + applied, RLS policy in place and proven, DATA_MODEL.md current. Commit as feat(db): <change>.
