open source is social v-it.org
1-- Migration: add kind column to caps and vouches tables for cap-requests feature
2-- Run: npx wrangler d1 execute vit-explore --file=migrate-cap-requests.sql
3
4ALTER TABLE caps ADD COLUMN kind TEXT;
5CREATE INDEX IF NOT EXISTS idx_caps_kind ON caps(kind);
6
7-- Backfill kind from record_json for existing cap records
8UPDATE caps SET kind = json_extract(record_json, '$.kind') WHERE kind IS NULL AND json_extract(record_json, '$.kind') IS NOT NULL;
9
10ALTER TABLE vouches ADD COLUMN kind TEXT;
11CREATE INDEX IF NOT EXISTS idx_vouches_kind ON vouches(kind);
12
13-- Backfill vouches: existing vouches without kind treated as 'endorse'
14UPDATE vouches SET kind = COALESCE(json_extract(record_json, '$.kind'), 'endorse') WHERE kind IS NULL;
15
16-- Also relax the NOT NULL constraint on vouches.beacon (want-vouches may lack a project beacon)
17-- SQLite doesn't support DROP NOT NULL via ALTER TABLE, so this is handled at the application layer.
18-- New vouches inserts allow NULL beacon (see jetstream.js update).