f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
356 lines
15 KiB
JavaScript
356 lines
15 KiB
JavaScript
#!/usr/bin/env node
|
|
/**
|
|
* ADR-156 Phase 5A — Bootstrap BDD logical tables.
|
|
*
|
|
* Creates the 5 CRM logical tables (bdd_specs, bdd_criteria, bdd_links,
|
|
* bdd_tests, bdd_test_runs) inside space_id=11.
|
|
*
|
|
* This script is IDEMPOTENT: if a table with the same `name` already exists
|
|
* in space 11, it is skipped. Column adds are not back-filled — existing
|
|
* tables are NOT altered. If you change the schema, drop the table first or
|
|
* add columns manually via the CRM admin UI.
|
|
*
|
|
* Usage:
|
|
* node scripts/bootstrap-bdd-tables.js # actually creates
|
|
* node scripts/bootstrap-bdd-tables.js --dry-run # just prints plan
|
|
*
|
|
* It uses direct SQL (pg driver) because this is a one-shot provisioning
|
|
* script, not a hot path. Matches the pattern used in
|
|
* scripts/clone-dev-to-holetron.mjs.
|
|
*/
|
|
|
|
import pg from 'pg';
|
|
const { Pool } = pg;
|
|
|
|
// --- config ---------------------------------------------------------------
|
|
const SPACE_ID = 11;
|
|
// Single parent project to host all 5 BDD tables. You can override with
|
|
// --project-id <id>. If not provided, will pick (or create) a project named
|
|
// "BDD / ADR-156" inside space 11.
|
|
const DEFAULT_PROJECT_NAME = 'BDD / ADR-156';
|
|
const DRY_RUN = process.argv.includes('--dry-run');
|
|
const projectFlagIdx = process.argv.indexOf('--project-id');
|
|
const PROJECT_ID_OVERRIDE = projectFlagIdx >= 0 ? parseInt(process.argv[projectFlagIdx + 1], 10) : null;
|
|
|
|
const pool = new Pool({
|
|
host: process.env.POSTGRES_HOST || 'localhost',
|
|
port: parseInt(process.env.POSTGRES_PORT || '5432', 10),
|
|
database: process.env.POSTGRES_DB || 'godcrm_prod',
|
|
user: process.env.POSTGRES_USER || 'godcrm',
|
|
password: process.env.POSTGRES_PASSWORD || 'godcrm_dev_2026',
|
|
});
|
|
|
|
// --- schema definitions ---------------------------------------------------
|
|
// Each column: { name, type, required?, config? }
|
|
// `type` values map to CRM internal types: text, number, date, checkbox,
|
|
// json, relation, select. (See backend/services/ColumnService.js for the
|
|
// full list.) The runtime uses these as hints only — the underlying
|
|
// storage is always a JSONB blob in `table_rows.data`.
|
|
|
|
const TABLES = [
|
|
{
|
|
name: 'bdd_specs',
|
|
icon: 'BookOpen',
|
|
description: 'BDD feature specifications (Given/When/Then). One row per spec.',
|
|
columns: [
|
|
{ name: 'title', type: 'text', required: true },
|
|
{ name: 'feature_file', type: 'text' },
|
|
{ name: 'source_doc_id', type: 'number' }, // FK → documents.id (logical)
|
|
{ name: 'author', type: 'text' },
|
|
{ name: 'status', type: 'select', config: { options: ['draft','active','retired'] } },
|
|
{ name: 'body_md', type: 'text' }, // raw Gherkin
|
|
{ name: 'tags', type: 'json' }, // [str, ...]
|
|
{ name: 'created_at', type: 'date' },
|
|
{ name: 'updated_at', type: 'date' },
|
|
],
|
|
},
|
|
{
|
|
name: 'bdd_criteria',
|
|
icon: 'CheckSquare',
|
|
description: 'Acceptance criteria. Each row belongs to one bdd_specs row.',
|
|
columns: [
|
|
// ADR-156 Appendix C §1.5: `code` is the stable identity for a criterion
|
|
// (e.g. ADR-156-AC1). Appendix C §1.6 introduces `previous_codes TEXT[]`
|
|
// to track rename history so old references still resolve.
|
|
{ name: 'code', type: 'text' },
|
|
{ name: 'previous_codes', type: 'json' }, // logical TEXT[] — stored as JSON array
|
|
{ name: 'spec_id', type: 'number', required: true },
|
|
{ name: 'source_doc_id', type: 'number' },
|
|
{ name: 'title', type: 'text', required: true },
|
|
{ name: 'description', type: 'text' },
|
|
{ name: 'status', type: 'select', config: { options: ['pending','in_progress','agent_claimed','failed','verified','orphaned'] } },
|
|
{ name: 'claimed_at', type: 'date' },
|
|
{ name: 'claimed_by_agent',type: 'text' },
|
|
{ name: 'failed_at', type: 'date' },
|
|
{ name: 'failed_test_id', type: 'number' },
|
|
{ name: 'priority', type: 'select', config: { options: ['must','should','could','low','normal','high','blocker'] } },
|
|
{ name: 'order_index', type: 'number' },
|
|
{ name: 'orphaned_at', type: 'date' },
|
|
],
|
|
},
|
|
{
|
|
name: 'bdd_links',
|
|
icon: 'Link',
|
|
description: 'Generic link table: (from_kind, from_id) ↔ (to_kind, to_id).',
|
|
columns: [
|
|
{ name: 'from_kind', type: 'text', required: true }, // 'spec' | 'criterion' | 'test' | 'doc' | 'ticket'
|
|
{ name: 'from_id', type: 'number', required: true },
|
|
{ name: 'to_kind', type: 'text', required: true },
|
|
{ name: 'to_id', type: 'number', required: true },
|
|
{ name: 'relation', type: 'text' }, // 'verifies' | 'covers' | 'derived_from' | ...
|
|
{ name: 'metadata', type: 'json' },
|
|
],
|
|
},
|
|
{
|
|
name: 'bdd_tests',
|
|
icon: 'FlaskConical',
|
|
description: 'Executable tests attached to a criterion.',
|
|
columns: [
|
|
// ADR-156 Appendix C §1.5: derived test code `{criterion_code}-T{m}`.
|
|
{ name: 'code', type: 'text' },
|
|
{ name: 'criterion_id', type: 'number', required: true },
|
|
// ADR-156 Appendix C §1.4: parent criterion code (redundant with
|
|
// criterion_id but used by the parser for direct code-based resolution).
|
|
{ name: 'criterion_code', type: 'text' },
|
|
{ name: 'title', type: 'text', required: true },
|
|
// ADR-156 Appendix C §1.4: runner kind — one of bash|http|sql|mcp.
|
|
{ name: 'runner', type: 'select', config: { options: ['bash','http','sql','mcp'] } },
|
|
// Legacy shape from iteration 1; kept for back-compat with existing rows.
|
|
{ name: 'kind', type: 'select', config: { options: ['npm','pytest','curl','mcp','claude','other'] } },
|
|
// ADR-156 Appendix C §1.4: test `type` (smoke|integration|contract|regression).
|
|
{ name: 'type', type: 'select', config: { options: ['smoke','integration','contract','regression'] } },
|
|
// Per-runner allow patterns / tool allowlist.
|
|
{ name: 'runner_config', type: 'json' },
|
|
{ name: 'command', type: 'text', required: true }, // executed by worker
|
|
// ADR-156 Appendix C §2.6: first-run review gate.
|
|
{ name: 'review_status', type: 'select', config: { options: ['pending','approved','rejected','revoked'] } },
|
|
{ name: 'created_by', type: 'text' },
|
|
{ name: 'is_blocking', type: 'checkbox' },
|
|
{ name: 'disabled', type: 'checkbox' },
|
|
{ name: 'disabled_reason', type: 'text' },
|
|
{ name: 'disabled_at', type: 'date' },
|
|
{ name: 'last_status', type: 'text' },
|
|
{ name: 'last_run_at', type: 'date' },
|
|
{ name: 'timeout_ms', type: 'number' },
|
|
],
|
|
},
|
|
{
|
|
name: 'bdd_test_runs',
|
|
icon: 'Activity',
|
|
description: 'Individual execution records for bdd_tests.',
|
|
columns: [
|
|
{ name: 'test_id', type: 'number', required: true },
|
|
{ name: 'status', type: 'select', config: { options: ['queued','running','passed','failed','timeout','error'] } },
|
|
{ name: 'exit_code', type: 'number' },
|
|
{ name: 'duration_ms', type: 'number' },
|
|
{ name: 'stdout_tail', type: 'text' },
|
|
{ name: 'stderr_tail', type: 'text' },
|
|
{ name: 'assertion_result', type: 'json' },
|
|
{ name: 'score', type: 'number' },
|
|
{ name: 'triggered_by', type: 'text' }, // 'worker' | 'agent' | 'human' | 'cron'
|
|
{ name: 'triggered_by_id', type: 'text' },
|
|
{ name: 'run_hash', type: 'text' },
|
|
{ name: 'started_at', type: 'date' },
|
|
{ name: 'finished_at', type: 'date' },
|
|
{ name: 'claimed_at', type: 'date' },
|
|
],
|
|
},
|
|
{
|
|
// ADR-0003 §C-4: every BDD state transition and TOTP-signed ownership act
|
|
// is recorded here. Append-only; never mutate rows after insert.
|
|
name: 'bdd_audit_log',
|
|
icon: 'ScrollText',
|
|
description: 'Append-only audit trail for BDD criterion state transitions and TOTP-signed ownership acts (ADR-0003 §C-4).',
|
|
columns: [
|
|
{ name: 'criterion_id', type: 'number', required: true },
|
|
{ name: 'spec_id', type: 'number' },
|
|
{ name: 'doc_id', type: 'number' },
|
|
{ name: 'action', type: 'select', config: { options: ['verify','waive','regress','unverify','lock','unlock'] } },
|
|
{ name: 'from_status', type: 'text' },
|
|
{ name: 'to_status', type: 'text' },
|
|
{ name: 'user_id', type: 'number' },
|
|
{ name: 'actor_kind', type: 'select', config: { options: ['user','agent','system'] } },
|
|
{ name: 'totp_hash', type: 'text' },
|
|
{ name: 'reason', type: 'text' },
|
|
{ name: 'caused_by', type: 'text' }, // e.g. 'ticket:125586:reopen'
|
|
{ name: 'ip', type: 'text' },
|
|
{ name: 'ts', type: 'date' },
|
|
],
|
|
},
|
|
];
|
|
|
|
// --- helpers --------------------------------------------------------------
|
|
async function findOrCreateProject(client) {
|
|
if (PROJECT_ID_OVERRIDE) {
|
|
const r = await client.query(
|
|
'SELECT id, name, space_id FROM projects WHERE id = $1',
|
|
[PROJECT_ID_OVERRIDE]
|
|
);
|
|
if (r.rows.length === 0) throw new Error(`--project-id ${PROJECT_ID_OVERRIDE} not found`);
|
|
if (r.rows[0].space_id !== SPACE_ID) {
|
|
throw new Error(`project ${PROJECT_ID_OVERRIDE} is in space ${r.rows[0].space_id}, not ${SPACE_ID}`);
|
|
}
|
|
return r.rows[0];
|
|
}
|
|
|
|
const existing = await client.query(
|
|
'SELECT id, name FROM projects WHERE space_id = $1 AND name = $2 LIMIT 1',
|
|
[SPACE_ID, DEFAULT_PROJECT_NAME]
|
|
);
|
|
if (existing.rows.length) return existing.rows[0];
|
|
|
|
if (DRY_RUN) {
|
|
console.log(`[dry-run] would create project "${DEFAULT_PROJECT_NAME}" in space ${SPACE_ID}`);
|
|
return { id: '<new>', name: DEFAULT_PROJECT_NAME };
|
|
}
|
|
|
|
// Best-effort default owner: prefer space owner, else user id 1.
|
|
const space = await client.query('SELECT owner_id FROM spaces WHERE id = $1', [SPACE_ID]);
|
|
const ownerId = space.rows[0]?.owner_id || 1;
|
|
|
|
const created = await client.query(`
|
|
INSERT INTO projects (space_id, name, type, owner_id, icon, description, created_at, updated_at)
|
|
VALUES ($1, $2, 'custom', $3, '🧪', 'ADR-156 Phase 5A — BDD/test-runner tables', NOW(), NOW())
|
|
RETURNING id, name
|
|
`, [SPACE_ID, DEFAULT_PROJECT_NAME, ownerId]);
|
|
console.log(` created project id=${created.rows[0].id} name="${DEFAULT_PROJECT_NAME}"`);
|
|
return created.rows[0];
|
|
}
|
|
|
|
async function tableExists(client, projectId, tableName) {
|
|
const r = await client.query(`
|
|
SELECT ut.id
|
|
FROM universal_tables ut
|
|
JOIN projects p ON ut.project_id = p.id
|
|
WHERE p.space_id = $1 AND ut.name = $2
|
|
LIMIT 1
|
|
`, [SPACE_ID, tableName]);
|
|
return r.rows[0] || null;
|
|
}
|
|
|
|
async function createTable(client, projectId, spec) {
|
|
// NOTE: PostgreSQL schema inherits SQLite heritage — is_system / is_required /
|
|
// is_visible are INTEGER (0|1), not BOOLEAN. Pass 0/1 to avoid type errors.
|
|
const r = await client.query(`
|
|
INSERT INTO universal_tables (project_id, name, description, icon, is_system, created_at, updated_at)
|
|
VALUES ($1, $2, $3, $4, 0, NOW(), NOW())
|
|
RETURNING id
|
|
`, [projectId, spec.name, spec.description, spec.icon || '📊']);
|
|
const tableId = r.rows[0].id;
|
|
|
|
for (let i = 0; i < spec.columns.length; i++) {
|
|
const col = spec.columns[i];
|
|
await client.query(`
|
|
INSERT INTO table_columns
|
|
(table_id, column_name, display_name, type, is_required, order_index, is_visible, config, created_at, updated_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, 1, $7, NOW(), NOW())
|
|
`, [
|
|
tableId,
|
|
col.name,
|
|
col.displayName || col.name,
|
|
col.type,
|
|
col.required ? 1 : 0,
|
|
i,
|
|
JSON.stringify(col.config || {}),
|
|
]);
|
|
}
|
|
return tableId;
|
|
}
|
|
|
|
/**
|
|
* Idempotent column add — inserts a table_columns row for each column in
|
|
* `spec.columns` that does NOT already exist for `tableId`. Safe to run on a
|
|
* table that was bootstrapped by an earlier version of this script.
|
|
*
|
|
* ADR-156 Appendix C §1.6 specifically requires a `previous_codes TEXT[]`
|
|
* column on `bdd_criteria` — this function is the migration path.
|
|
*/
|
|
async function ensureColumns(client, tableId, spec) {
|
|
const existing = await client.query(
|
|
'SELECT column_name FROM table_columns WHERE table_id = $1',
|
|
[tableId]
|
|
);
|
|
const have = new Set(existing.rows.map(r => r.column_name));
|
|
|
|
const maxOrder = await client.query(
|
|
'SELECT COALESCE(MAX(order_index), -1) AS m FROM table_columns WHERE table_id = $1',
|
|
[tableId]
|
|
);
|
|
let nextOrder = (maxOrder.rows[0]?.m ?? -1) + 1;
|
|
|
|
const added = [];
|
|
for (const col of spec.columns) {
|
|
if (have.has(col.name)) continue;
|
|
if (DRY_RUN) {
|
|
console.log(` [dry-run] would add column "${col.name}" (${col.type}) to ${spec.name}`);
|
|
added.push(col.name);
|
|
continue;
|
|
}
|
|
await client.query(`
|
|
INSERT INTO table_columns
|
|
(table_id, column_name, display_name, type, is_required, order_index, is_visible, config, created_at, updated_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, 1, $7, NOW(), NOW())
|
|
`, [
|
|
tableId,
|
|
col.name,
|
|
col.displayName || col.name,
|
|
col.type,
|
|
col.required ? 1 : 0,
|
|
nextOrder++,
|
|
JSON.stringify(col.config || {}),
|
|
]);
|
|
added.push(col.name);
|
|
}
|
|
return added;
|
|
}
|
|
|
|
// --- main -----------------------------------------------------------------
|
|
async function main() {
|
|
console.log(`ADR-156 Phase 5A — bootstrap BDD tables (space_id=${SPACE_ID}${DRY_RUN ? ', DRY RUN' : ''})`);
|
|
|
|
const client = await pool.connect();
|
|
try {
|
|
const project = await findOrCreateProject(client);
|
|
console.log(` project: id=${project.id} name="${project.name}"`);
|
|
|
|
const results = [];
|
|
for (const spec of TABLES) {
|
|
const existing = await tableExists(client, project.id, spec.name);
|
|
if (existing) {
|
|
// ADR-156 Appendix C §1.6 migration: idempotently add any new columns
|
|
// introduced by this version of the script (e.g. previous_codes,
|
|
// runner_config, review_status).
|
|
const added = await ensureColumns(client, existing.id, spec);
|
|
if (added.length) {
|
|
console.log(` [migrate] table "${spec.name}" (id=${existing.id}) — added columns: ${added.join(', ')}`);
|
|
results.push({ name: spec.name, status: 'migrated', id: existing.id, added });
|
|
} else {
|
|
console.log(` [skip] table "${spec.name}" already exists (id=${existing.id}) — no new columns`);
|
|
results.push({ name: spec.name, status: 'skipped', id: existing.id });
|
|
}
|
|
continue;
|
|
}
|
|
if (DRY_RUN) {
|
|
console.log(` [dry-run] would create table "${spec.name}" (${spec.columns.length} columns)`);
|
|
results.push({ name: spec.name, status: 'would_create' });
|
|
continue;
|
|
}
|
|
const tableId = await createTable(client, project.id, spec);
|
|
console.log(` [created] table "${spec.name}" id=${tableId} (${spec.columns.length} columns)`);
|
|
results.push({ name: spec.name, status: 'created', id: tableId });
|
|
}
|
|
|
|
console.log('\nSummary:');
|
|
for (const r of results) console.log(` - ${r.name}: ${r.status}${r.id ? ` (id=${r.id})` : ''}`);
|
|
console.log('\nDone.');
|
|
} finally {
|
|
client.release();
|
|
await pool.end();
|
|
}
|
|
}
|
|
|
|
main().catch((e) => {
|
|
console.error('bootstrap-bdd-tables: FAILED');
|
|
console.error(e);
|
|
process.exit(1);
|
|
});
|