f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
207 lines
7.3 KiB
JavaScript
207 lines
7.3 KiB
JavaScript
// 🟢 GREEN Phase: Migration for Multi-Source Tables
|
|
// Migration 001: Add data_sources, user_settings, sync_logs, and extend workspace_tables & table_columns
|
|
|
|
/**
|
|
* Run migration to create/extend tables for Multi-Source Tables architecture
|
|
* @param {import('better-sqlite3').Database} db - Database instance
|
|
*/
|
|
export async function runMigration(db) {
|
|
console.log('📦 Running Migration 001: Multi-Source Tables...');
|
|
|
|
// ========================================
|
|
// 1. CREATE: user_settings (encrypted storage)
|
|
// ========================================
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS user_settings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
|
|
setting_key TEXT NOT NULL,
|
|
setting_value_encrypted TEXT NOT NULL,
|
|
setting_type TEXT NOT NULL,
|
|
|
|
description TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
last_used_at DATETIME,
|
|
|
|
UNIQUE(user_id, setting_key),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
db.exec(`
|
|
CREATE INDEX IF NOT EXISTS idx_user_settings_user_type
|
|
ON user_settings(user_id, setting_type)
|
|
`);
|
|
|
|
console.log(' ✅ Created table: user_settings');
|
|
|
|
// ========================================
|
|
// 2. CREATE: data_sources
|
|
// ========================================
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS data_sources (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
type TEXT NOT NULL,
|
|
|
|
ssh_host TEXT,
|
|
ssh_port INTEGER DEFAULT 22,
|
|
ssh_username TEXT,
|
|
ssh_key_name TEXT,
|
|
|
|
db_host TEXT,
|
|
db_port INTEGER,
|
|
db_name TEXT,
|
|
db_username TEXT,
|
|
db_password_key TEXT,
|
|
|
|
last_test_at DATETIME,
|
|
last_test_status TEXT,
|
|
last_test_error TEXT,
|
|
|
|
sync_enabled INTEGER DEFAULT 0,
|
|
sync_interval_minutes INTEGER DEFAULT 15,
|
|
last_sync_at DATETIME,
|
|
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
)
|
|
`);
|
|
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_data_sources_workspace ON data_sources(workspace_id)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_data_sources_type ON data_sources(type)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_data_sources_sync_enabled ON data_sources(sync_enabled)`);
|
|
|
|
console.log(' ✅ Created table: data_sources');
|
|
|
|
// ========================================
|
|
// 3. EXTEND: workspace_tables (if exists)
|
|
// ========================================
|
|
const tableExists = db.prepare(`
|
|
SELECT name FROM sqlite_master
|
|
WHERE type='table' AND name='workspace_tables'
|
|
`).get();
|
|
|
|
if (tableExists) {
|
|
// Check which columns exist
|
|
const columns = db.prepare('PRAGMA table_info(workspace_tables)').all();
|
|
const columnNames = columns.map(col => col.name);
|
|
|
|
// Add columns if they don't exist
|
|
if (!columnNames.includes('data_source_id')) {
|
|
db.exec('ALTER TABLE workspace_tables ADD COLUMN data_source_id TEXT');
|
|
}
|
|
if (!columnNames.includes('source_table_name')) {
|
|
db.exec('ALTER TABLE workspace_tables ADD COLUMN source_table_name TEXT');
|
|
}
|
|
if (!columnNames.includes('source_id_column')) {
|
|
db.exec('ALTER TABLE workspace_tables ADD COLUMN source_id_column TEXT');
|
|
}
|
|
if (!columnNames.includes('id_validation_pattern')) {
|
|
db.exec('ALTER TABLE workspace_tables ADD COLUMN id_validation_pattern TEXT');
|
|
}
|
|
if (!columnNames.includes('table_type')) {
|
|
db.exec("ALTER TABLE workspace_tables ADD COLUMN table_type TEXT DEFAULT 'own'");
|
|
}
|
|
if (!columnNames.includes('is_locked')) {
|
|
db.exec('ALTER TABLE workspace_tables ADD COLUMN is_locked INTEGER DEFAULT 0');
|
|
}
|
|
if (!columnNames.includes('row_count')) {
|
|
db.exec('ALTER TABLE workspace_tables ADD COLUMN row_count INTEGER DEFAULT 0');
|
|
}
|
|
|
|
// Add indexes
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_workspace_tables_data_source ON workspace_tables(data_source_id)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_workspace_tables_type ON workspace_tables(table_type)`);
|
|
|
|
console.log(' ✅ Extended table: workspace_tables');
|
|
} else {
|
|
console.log(' ⚠️ Table workspace_tables does not exist, skipping extension');
|
|
}
|
|
|
|
// ========================================
|
|
// 4. EXTEND: table_columns (if exists)
|
|
// ========================================
|
|
const columnsTableExists = db.prepare(`
|
|
SELECT name FROM sqlite_master
|
|
WHERE type='table' AND name='table_columns'
|
|
`).get();
|
|
|
|
if (columnsTableExists) {
|
|
const columns = db.prepare('PRAGMA table_info(table_columns)').all();
|
|
const columnNames = columns.map(col => col.name);
|
|
|
|
// Add columns if they don't exist
|
|
if (!columnNames.includes('is_from_source')) {
|
|
db.exec('ALTER TABLE table_columns ADD COLUMN is_from_source INTEGER DEFAULT 0');
|
|
}
|
|
if (!columnNames.includes('is_primary_key')) {
|
|
db.exec('ALTER TABLE table_columns ADD COLUMN is_primary_key INTEGER DEFAULT 0');
|
|
}
|
|
if (!columnNames.includes('is_locked')) {
|
|
db.exec('ALTER TABLE table_columns ADD COLUMN is_locked INTEGER DEFAULT 0');
|
|
}
|
|
if (!columnNames.includes('formula')) {
|
|
db.exec('ALTER TABLE table_columns ADD COLUMN formula TEXT');
|
|
}
|
|
if (!columnNames.includes('options')) {
|
|
db.exec('ALTER TABLE table_columns ADD COLUMN options TEXT');
|
|
}
|
|
if (!columnNames.includes('required')) {
|
|
db.exec('ALTER TABLE table_columns ADD COLUMN required INTEGER DEFAULT 0');
|
|
}
|
|
if (!columnNames.includes('unique_constraint')) {
|
|
db.exec('ALTER TABLE table_columns ADD COLUMN unique_constraint INTEGER DEFAULT 0');
|
|
}
|
|
if (!columnNames.includes('default_value')) {
|
|
db.exec('ALTER TABLE table_columns ADD COLUMN default_value TEXT');
|
|
}
|
|
|
|
console.log(' ✅ Extended table: table_columns');
|
|
} else {
|
|
console.log(' ⚠️ Table table_columns does not exist, skipping extension');
|
|
}
|
|
|
|
// ========================================
|
|
// 5. CREATE: sync_logs
|
|
// ========================================
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS sync_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
data_source_id TEXT,
|
|
table_id TEXT NOT NULL,
|
|
|
|
status TEXT NOT NULL,
|
|
|
|
new_records INTEGER DEFAULT 0,
|
|
updated_records INTEGER DEFAULT 0,
|
|
archived_records INTEGER DEFAULT 0,
|
|
total_active_records INTEGER DEFAULT 0,
|
|
|
|
error_message TEXT,
|
|
error_details TEXT,
|
|
|
|
duration_ms INTEGER,
|
|
synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (data_source_id) REFERENCES data_sources(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (table_id) REFERENCES workspace_tables(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_sync_logs_data_source ON sync_logs(data_source_id)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_sync_logs_table ON sync_logs(table_id)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_sync_logs_status ON sync_logs(status)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_sync_logs_synced_at ON sync_logs(synced_at)`);
|
|
|
|
console.log(' ✅ Created table: sync_logs');
|
|
|
|
console.log('✅ Migration 001 completed successfully!');
|
|
}
|