409fd78a35
- Rename namespace/kind to folder/type on entries, audit_log, and history tables; add notes. Unique key is (user_id, folder, name). - Service layer and MCP tools support name-first lookup with optional folder when multiple entries share the same name. - secrets_delete dry_run uses the same disambiguation as real deletes. - Add scripts/migrate-v0.3.0.sql for manual DB migration. Refresh README and AGENTS.md. Made-with: Cursor
195 lines
7.1 KiB
PL/PgSQL
195 lines
7.1 KiB
PL/PgSQL
-- ============================================================================
|
|
-- migrate-v0.3.0.sql
|
|
-- Schema migration from v0.2.x → v0.3.0
|
|
--
|
|
-- Changes:
|
|
-- • entries: namespace → folder, kind → type; add notes column
|
|
-- • audit_log: namespace → folder, kind → type
|
|
-- • entries_history: namespace → folder, kind → type; add user_id column
|
|
-- • Unique index: (user_id, name) → (user_id, folder, name)
|
|
-- Same name in different folders is now allowed; no rename needed.
|
|
--
|
|
-- Safe to run multiple times (fully idempotent).
|
|
-- Preserves all data in users, entries, secrets.
|
|
-- ============================================================================
|
|
|
|
BEGIN;
|
|
|
|
-- ── entries: rename namespace→folder, kind→type ──────────────────────────────
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'entries' AND column_name = 'namespace'
|
|
) THEN
|
|
ALTER TABLE entries RENAME COLUMN namespace TO folder;
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'entries' AND column_name = 'kind'
|
|
) THEN
|
|
ALTER TABLE entries RENAME COLUMN kind TO type;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Set NOT NULL + default for folder/type in entries
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'entries' AND column_name = 'folder'
|
|
) THEN
|
|
UPDATE entries SET folder = '' WHERE folder IS NULL;
|
|
ALTER TABLE entries ALTER COLUMN folder SET NOT NULL;
|
|
ALTER TABLE entries ALTER COLUMN folder SET DEFAULT '';
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'entries' AND column_name = 'type'
|
|
) THEN
|
|
UPDATE entries SET type = '' WHERE type IS NULL;
|
|
ALTER TABLE entries ALTER COLUMN type SET NOT NULL;
|
|
ALTER TABLE entries ALTER COLUMN type SET DEFAULT '';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Add notes column to entries if missing
|
|
ALTER TABLE entries ADD COLUMN IF NOT EXISTS notes TEXT NOT NULL DEFAULT '';
|
|
|
|
-- ── audit_log: rename namespace→folder, kind→type ────────────────────────────
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'audit_log' AND column_name = 'namespace'
|
|
) THEN
|
|
ALTER TABLE audit_log RENAME COLUMN namespace TO folder;
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'audit_log' AND column_name = 'kind'
|
|
) THEN
|
|
ALTER TABLE audit_log RENAME COLUMN kind TO type;
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'audit_log' AND column_name = 'folder'
|
|
) THEN
|
|
UPDATE audit_log SET folder = '' WHERE folder IS NULL;
|
|
ALTER TABLE audit_log ALTER COLUMN folder SET NOT NULL;
|
|
ALTER TABLE audit_log ALTER COLUMN folder SET DEFAULT '';
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'audit_log' AND column_name = 'type'
|
|
) THEN
|
|
UPDATE audit_log SET type = '' WHERE type IS NULL;
|
|
ALTER TABLE audit_log ALTER COLUMN type SET NOT NULL;
|
|
ALTER TABLE audit_log ALTER COLUMN type SET DEFAULT '';
|
|
END IF;
|
|
END $$;
|
|
|
|
ALTER TABLE audit_log DROP COLUMN IF EXISTS actor;
|
|
|
|
-- ── entries_history: rename namespace→folder, kind→type; add user_id ─────────
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'entries_history' AND column_name = 'namespace'
|
|
) THEN
|
|
ALTER TABLE entries_history RENAME COLUMN namespace TO folder;
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'entries_history' AND column_name = 'kind'
|
|
) THEN
|
|
ALTER TABLE entries_history RENAME COLUMN kind TO type;
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'entries_history' AND column_name = 'folder'
|
|
) THEN
|
|
UPDATE entries_history SET folder = '' WHERE folder IS NULL;
|
|
ALTER TABLE entries_history ALTER COLUMN folder SET NOT NULL;
|
|
ALTER TABLE entries_history ALTER COLUMN folder SET DEFAULT '';
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'entries_history' AND column_name = 'type'
|
|
) THEN
|
|
UPDATE entries_history SET type = '' WHERE type IS NULL;
|
|
ALTER TABLE entries_history ALTER COLUMN type SET NOT NULL;
|
|
ALTER TABLE entries_history ALTER COLUMN type SET DEFAULT '';
|
|
END IF;
|
|
END $$;
|
|
|
|
ALTER TABLE entries_history ADD COLUMN IF NOT EXISTS user_id UUID;
|
|
ALTER TABLE entries_history DROP COLUMN IF EXISTS actor;
|
|
|
|
-- ── secrets_history: drop actor column ───────────────────────────────────────
|
|
ALTER TABLE secrets_history DROP COLUMN IF EXISTS actor;
|
|
|
|
-- ── Rebuild unique indexes: (user_id, folder, name) ──────────────────────────
|
|
-- Note: folder is now part of the key, so same name in different folders is
|
|
-- naturally distinct — no rename of existing rows needed.
|
|
DROP INDEX IF EXISTS idx_entries_unique_legacy;
|
|
DROP INDEX IF EXISTS idx_entries_unique_user;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_entries_unique_legacy
|
|
ON entries(folder, name)
|
|
WHERE user_id IS NULL;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_entries_unique_user
|
|
ON entries(user_id, folder, name)
|
|
WHERE user_id IS NOT NULL;
|
|
|
|
-- ── Replace old namespace/kind indexes with folder/type ──────────────────────
|
|
DROP INDEX IF EXISTS idx_entries_namespace;
|
|
DROP INDEX IF EXISTS idx_entries_kind;
|
|
DROP INDEX IF EXISTS idx_audit_log_ns_kind;
|
|
DROP INDEX IF EXISTS idx_entries_history_ns_kind_name;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_entries_folder
|
|
ON entries(folder) WHERE folder <> '';
|
|
CREATE INDEX IF NOT EXISTS idx_entries_type
|
|
ON entries(type) WHERE type <> '';
|
|
CREATE INDEX IF NOT EXISTS idx_entries_user_id
|
|
ON entries(user_id) WHERE user_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_folder_type
|
|
ON audit_log(folder, type);
|
|
CREATE INDEX IF NOT EXISTS idx_entries_history_folder_type_name
|
|
ON entries_history(folder, type, name, version DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_entries_history_user_id
|
|
ON entries_history(user_id) WHERE user_id IS NOT NULL;
|
|
|
|
COMMIT;
|
|
|
|
-- ── Verification queries (run these manually to confirm) ─────────────────────
|
|
-- SELECT column_name, data_type FROM information_schema.columns
|
|
-- WHERE table_name = 'entries' ORDER BY ordinal_position;
|
|
-- SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'entries';
|
|
-- SELECT COUNT(*) FROM entries;
|
|
-- SELECT COUNT(*) FROM users;
|
|
-- SELECT COUNT(*) FROM secrets;
|