f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
67 lines
3.8 KiB
PL/PgSQL
67 lines
3.8 KiB
PL/PgSQL
-- ADR-0011 · Phase E2 · Wire two `verification` column instances
|
|
--
|
|
-- Creates:
|
|
-- 1. plan_verification on registry table 2197 (ADRs registry)
|
|
-- 2. criterion_verification on bdd_criteria (table 7256, looked up by name)
|
|
--
|
|
-- Config is the NORMALIZED output of validateVerificationConfig (so that
|
|
-- validateVerificationOverride can use it as `base` directly without
|
|
-- re-running the validator on every read).
|
|
--
|
|
-- Idempotent (WHERE NOT EXISTS).
|
|
-- Run manually on each env:
|
|
-- psql -h localhost -U godcrm -d godcrm_prod -f scripts/migrations/20260423-adr0011-verification-columns.sql
|
|
--
|
|
-- The columns themselves are inert until VERIFICATION_COLUMN_ENABLED=true on
|
|
-- the backend (Phase A flag). Cells are nullable; existing rows unaffected.
|
|
|
|
BEGIN;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────
|
|
-- 1. plan_verification on registry table 2197 (ADRs)
|
|
-- policy=all + 2 methods → TOTP AND CAPTCHA both required
|
|
-- cooldown 5min, TTL 24h, row-update guard active
|
|
-- ─────────────────────────────────────────────────────────────────
|
|
INSERT INTO table_columns (
|
|
table_id, column_name, display_name, type, config,
|
|
order_index, is_visible, is_required, created_at, updated_at
|
|
)
|
|
SELECT 2197, 'plan_verification', 'Plan Verification', 'verification',
|
|
'{"available_methods":["totp","captcha"],"required_methods":2,"method":"totp","cooldown_seconds":300,"cooldown_ms":300000,"ttl_seconds":86400,"ttl_ms":86400000,"locks_on_statuses":[],"unlocks_on_statuses":[],"guards":["row_update_guard"],"policy":"all","rate_limit":null,"method_config":{}}',
|
|
100, 1, 0, NOW(), NOW()
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM table_columns
|
|
WHERE table_id = 2197 AND column_name = 'plan_verification'
|
|
);
|
|
|
|
-- ─────────────────────────────────────────────────────────────────
|
|
-- 2. criterion_verification on bdd_criteria
|
|
-- Single-method TOTP, shorter cooldown (60s), same TTL+guard
|
|
-- table_id resolved by name + space_id=11 for portability across envs
|
|
-- ─────────────────────────────────────────────────────────────────
|
|
INSERT INTO table_columns (
|
|
table_id, column_name, display_name, type, config,
|
|
order_index, is_visible, is_required, created_at, updated_at
|
|
)
|
|
SELECT
|
|
(SELECT ut.id FROM universal_tables ut
|
|
JOIN projects p ON ut.project_id = p.id
|
|
WHERE p.space_id = 11 AND ut.name = 'bdd_criteria' LIMIT 1),
|
|
'criterion_verification', 'Criterion Verification', 'verification',
|
|
'{"available_methods":["totp"],"required_methods":1,"method":"totp","cooldown_seconds":60,"cooldown_ms":60000,"ttl_seconds":86400,"ttl_ms":86400000,"locks_on_statuses":[],"unlocks_on_statuses":[],"guards":["row_update_guard"],"policy":"all","rate_limit":null,"method_config":{}}',
|
|
100, 1, 0, NOW(), NOW()
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM table_columns
|
|
WHERE table_id = (SELECT ut.id FROM universal_tables ut
|
|
JOIN projects p ON ut.project_id = p.id
|
|
WHERE p.space_id = 11 AND ut.name = 'bdd_criteria' LIMIT 1)
|
|
AND column_name = 'criterion_verification'
|
|
);
|
|
|
|
COMMIT;
|
|
|
|
-- ─── Verify ─────────────────────────────────────────────────────────
|
|
SELECT id, table_id, column_name, type
|
|
FROM table_columns
|
|
WHERE column_name IN ('plan_verification', 'criterion_verification')
|
|
ORDER BY column_name;
|