f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
280 lines
11 KiB
JavaScript
280 lines
11 KiB
JavaScript
// system-tables-creator/system-tables.js
|
|
// Core system tables: Users, Projects, Tables, Files, Storage Providers, Bugs
|
|
|
|
import { dbRun, toBool } from '../../database/connection.js';
|
|
import { insertColumns } from './helpers.js';
|
|
|
|
/**
|
|
* Create Users system table
|
|
* @param {number} projectId - Project ID
|
|
*/
|
|
export async function createUsersSystemTable(projectId) {
|
|
const result = await dbRun(`
|
|
INSERT INTO universal_tables (project_id, name, description, icon, is_system, sync_target)
|
|
VALUES (?, ?, ?, ?, 1, ?)
|
|
`, [projectId, 'Users', 'System users management', '👥', 'users']);
|
|
|
|
const tableId = result.lastInsertRowid || result.lastID;
|
|
|
|
await insertColumns(tableId, [
|
|
{ name: 'id', display: 'ID', type: 'number', order: 0, is_system: true },
|
|
{ name: 'created_at', display: 'Created At', type: 'datetime', order: 1, is_system: true },
|
|
{ name: 'updated_at', display: 'Updated At', type: 'datetime', order: 2, is_system: true },
|
|
{ name: 'email', display: 'Email', type: 'email', order: 3, is_required: 1 },
|
|
{ name: 'name', display: 'Name', type: 'text', order: 4, is_required: 1 },
|
|
{
|
|
name: 'role',
|
|
display: 'Role',
|
|
type: 'select',
|
|
order: 5,
|
|
config: {
|
|
options: [
|
|
{ label: 'Owner', value: 'owner' },
|
|
{ label: 'Admin', value: 'admin' },
|
|
{ label: 'User', value: 'user' }
|
|
]
|
|
}
|
|
},
|
|
{ name: 'avatar', display: 'Avatar', type: 'image', order: 6 },
|
|
{ name: 'totp_enabled', display: '2FA Enabled', type: 'checkbox', order: 7 },
|
|
{ name: 'email_verified', display: 'Email Verified', type: 'checkbox', order: 8 }
|
|
]);
|
|
|
|
return tableId;
|
|
}
|
|
|
|
/**
|
|
* Create Projects system table
|
|
* @param {number} projectId - Project ID
|
|
*/
|
|
export async function createProjectsSystemTable(projectId) {
|
|
const result = await dbRun(`
|
|
INSERT INTO universal_tables (project_id, name, description, icon, is_system, sync_target)
|
|
VALUES (?, ?, ?, ?, 1, ?)
|
|
`, [projectId, 'Projects', 'System projects management', '📁', 'projects']);
|
|
|
|
const tableId = result.lastInsertRowid || result.lastID;
|
|
|
|
await insertColumns(tableId, [
|
|
{ name: 'id', display: 'ID', type: 'number', order: 0, is_system: true },
|
|
{ name: 'created_at', display: 'Created At', type: 'datetime', order: 1, is_system: true },
|
|
{ name: 'updated_at', display: 'Updated At', type: 'datetime', order: 2, is_system: true },
|
|
{ name: 'name', display: 'Name', type: 'text', order: 3, is_required: 1 },
|
|
{ name: 'description', display: 'Description', type: 'text', order: 4 },
|
|
{ name: 'icon', display: 'Icon', type: 'text', order: 5 },
|
|
{
|
|
name: 'type',
|
|
display: 'Type',
|
|
type: 'select',
|
|
order: 6,
|
|
config: {
|
|
options: [
|
|
{ label: 'Admin Owner Space', value: 'admin_owner_space' },
|
|
{ label: 'Personal Space', value: 'personal_space' },
|
|
{ label: 'Custom', value: 'custom' }
|
|
]
|
|
}
|
|
},
|
|
{ name: 'owner_id', display: 'Owner', type: 'user', order: 7 },
|
|
{ name: 'theme_primary', display: 'Primary Color', type: 'text', order: 8 },
|
|
{ name: 'theme_secondary', display: 'Secondary Color', type: 'text', order: 9 },
|
|
{ name: 'theme_tertiary', display: 'Tertiary Color', type: 'text', order: 10 }
|
|
]);
|
|
|
|
return tableId;
|
|
}
|
|
|
|
/**
|
|
* Create Tables system table
|
|
* @param {number} projectId - Project ID
|
|
*/
|
|
export async function createTablesSystemTable(projectId) {
|
|
const result = await dbRun(`
|
|
INSERT INTO universal_tables (project_id, name, description, icon, is_system, sync_target)
|
|
VALUES (?, ?, ?, ?, 1, ?)
|
|
`, [projectId, 'Tables', 'System tables management', '📊', 'universal_tables']);
|
|
|
|
const tableId = result.lastInsertRowid || result.lastID;
|
|
|
|
await insertColumns(tableId, [
|
|
{ name: 'id', display: 'ID', type: 'number', order: 0, is_system: true },
|
|
{ name: 'created_at', display: 'Created At', type: 'datetime', order: 1, is_system: true },
|
|
{ name: 'updated_at', display: 'Updated At', type: 'datetime', order: 2, is_system: true },
|
|
{ name: 'name', display: 'Name', type: 'text', order: 3, is_required: 1 },
|
|
{ name: 'display_name', display: 'Display Name', type: 'text', order: 4 },
|
|
{ name: 'icon', display: 'Icon', type: 'text', order: 5 },
|
|
{ name: 'project_id', display: 'Project', type: 'relation', order: 6 },
|
|
{ name: 'is_system', display: 'System Table', type: 'checkbox', order: 7 },
|
|
{ name: 'sync_target', display: 'Sync Target', type: 'text', order: 8 }
|
|
]);
|
|
|
|
return tableId;
|
|
}
|
|
|
|
/**
|
|
* Create Files system table
|
|
* @param {number} projectId - Project ID
|
|
*/
|
|
export async function createFilesSystemTable(projectId) {
|
|
const result = await dbRun(`
|
|
INSERT INTO universal_tables (project_id, name, description, icon, is_system, sync_target)
|
|
VALUES (?, ?, ?, ?, 1, ?)
|
|
`, [projectId, 'Files', 'Uploaded files management', '📎', 'files']);
|
|
|
|
const tableId = result.lastInsertRowid || result.lastID;
|
|
|
|
await insertColumns(tableId, [
|
|
{ name: 'id', display: 'ID', type: 'text', order: 0, is_system: true },
|
|
{ name: 'name', display: 'File Name', type: 'text', order: 1 },
|
|
{ name: 'original_name', display: 'Original Name', type: 'text', order: 2 },
|
|
{ name: 'mime_type', display: 'Type', type: 'text', order: 3 },
|
|
{ name: 'size', display: 'Size', type: 'number', order: 4 },
|
|
{ name: 'url', display: 'URL', type: 'url', order: 5 },
|
|
{ name: 'space_id', display: 'Space', type: 'relation', order: 6 },
|
|
{ name: 'project_id', display: 'Project', type: 'relation', order: 7 },
|
|
{ name: 'table_id', display: 'Table', type: 'relation', order: 8 },
|
|
{ name: 'row_id', display: 'Row', type: 'text', order: 9 },
|
|
{ name: 'uploaded_by', display: 'Uploaded By', type: 'user', order: 10 },
|
|
{
|
|
name: 'storage_provider_id',
|
|
display: 'Storage',
|
|
type: 'select',
|
|
order: 11,
|
|
config: {
|
|
options: [
|
|
{ label: '💾 Local', value: 'local' },
|
|
{ label: '☁️ S3', value: 's3' },
|
|
{ label: '📁 Google Drive', value: 'google_drive' },
|
|
{ label: '📦 Dropbox', value: 'dropbox' }
|
|
]
|
|
}
|
|
},
|
|
{ name: 'description', display: 'Description', type: 'text', order: 12 },
|
|
{ name: 'created_at', display: 'Created At', type: 'datetime', order: 13, is_system: true },
|
|
{ name: 'updated_at', display: 'Updated At', type: 'datetime', order: 14, is_system: true }
|
|
]);
|
|
|
|
return tableId;
|
|
}
|
|
|
|
/**
|
|
* Create Storage Providers system table
|
|
* @param {number} projectId - Project ID
|
|
*/
|
|
export async function createStorageProvidersSystemTable(projectId) {
|
|
const result = await dbRun(`
|
|
INSERT INTO universal_tables (project_id, name, description, icon, is_system, sync_target)
|
|
VALUES (?, ?, ?, ?, 1, ?)
|
|
`, [projectId, 'Storage Providers', 'File storage providers configuration', '☁️', 'storage_providers']);
|
|
|
|
const tableId = result.lastInsertRowid || result.lastID;
|
|
|
|
await insertColumns(tableId, [
|
|
{ name: 'id', display: 'ID', type: 'text', order: 0, is_system: true },
|
|
{ name: 'name', display: 'Name', type: 'text', order: 1, is_required: 1 },
|
|
{
|
|
name: 'type',
|
|
display: 'Type',
|
|
type: 'select',
|
|
order: 2,
|
|
is_required: 1,
|
|
config: {
|
|
options: [
|
|
{ label: '💾 Local Storage', value: 'local' },
|
|
{ label: '☁️ Amazon S3', value: 's3' },
|
|
{ label: '📁 Google Drive', value: 'google_drive' },
|
|
{ label: '📦 Dropbox', value: 'dropbox' }
|
|
]
|
|
}
|
|
},
|
|
{ name: 'is_default', display: 'Default', type: 'checkbox', order: 3 },
|
|
{ name: 'is_enabled', display: 'Enabled', type: 'checkbox', order: 4 },
|
|
{ name: 'config', display: 'Configuration', type: 'json', order: 5 },
|
|
{ name: 'created_at', display: 'Created At', type: 'datetime', order: 6, is_system: true },
|
|
{ name: 'updated_at', display: 'Updated At', type: 'datetime', order: 7, is_system: true }
|
|
]);
|
|
|
|
return tableId;
|
|
}
|
|
|
|
/**
|
|
* Create Widgets system table (ADR-0012 Phase 8.2)
|
|
* Read-only registry view of widget templates (rows where is_template=true).
|
|
* Per-space stub; data is global (not space-scoped).
|
|
* @param {number} projectId - System Data project ID
|
|
*/
|
|
export async function createWidgetsSystemTable(projectId) {
|
|
const result = await dbRun(`
|
|
INSERT INTO universal_tables (project_id, name, display_name, description, icon, is_system, sync_target)
|
|
VALUES (?, ?, ?, ?, ?, 1, ?)
|
|
`, [projectId, 'Widgets', 'Widgets', 'Widget templates (read-only)', '🧩', 'widgets']);
|
|
|
|
const tableId = result.lastInsertRowid || result.lastID;
|
|
|
|
await insertColumns(tableId, [
|
|
{ name: 'id', display: 'ID', type: 'number', order: 0, is_system: true },
|
|
{ name: 'preset_name', display: 'Preset', type: 'text', order: 1 },
|
|
{ name: 'title', display: 'Title', type: 'text', order: 2 },
|
|
{ name: 'icon', display: 'Icon', type: 'text', order: 3 },
|
|
{ name: 'description', display: 'Description', type: 'text', order: 4 },
|
|
{ name: 'config', display: 'Config', type: 'json', order: 5 },
|
|
{ name: 'widget_type', display: 'Type', type: 'text', order: 6 },
|
|
{ name: 'created_at', display: 'Created At', type: 'datetime', order: 7, is_system: true },
|
|
{ name: 'updated_at', display: 'Updated At', type: 'datetime', order: 8, is_system: true }
|
|
]);
|
|
|
|
return tableId;
|
|
}
|
|
|
|
/**
|
|
* Create Bugs table for Admin Owner's Space
|
|
* @param {number} projectId - Project ID
|
|
*/
|
|
export async function createBugsTable(projectId) {
|
|
const result = await dbRun(`
|
|
INSERT INTO universal_tables (project_id, name, description, icon, is_system)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
`, [projectId, 'Bugs', 'Bug reports and issues', '🐛', toBool(false)]);
|
|
|
|
const tableId = result.lastInsertRowid || result.lastID;
|
|
|
|
await insertColumns(tableId, [
|
|
{ name: 'title', display: 'Title', type: 'text', order: 0, is_required: 1 },
|
|
{ name: 'description', display: 'Description', type: 'textarea', order: 1 },
|
|
{ name: 'steps', display: 'Steps to Reproduce', type: 'textarea', order: 2 },
|
|
{
|
|
name: 'severity',
|
|
display: 'Severity',
|
|
type: 'select',
|
|
order: 3,
|
|
config: {
|
|
options: [
|
|
{ label: 'Low', value: 'low' },
|
|
{ label: 'Medium', value: 'medium' },
|
|
{ label: 'High', value: 'high' },
|
|
{ label: 'Critical', value: 'critical' }
|
|
]
|
|
}
|
|
},
|
|
{
|
|
name: 'status',
|
|
display: 'Status',
|
|
type: 'select',
|
|
order: 4,
|
|
config: {
|
|
options: [
|
|
{ label: 'New', value: 'new' },
|
|
{ label: 'In Progress', value: 'in_progress' },
|
|
{ label: 'Blocked', value: 'blocked' },
|
|
{ label: 'Resolved', value: 'resolved' }
|
|
]
|
|
}
|
|
},
|
|
{ name: 'page_url', display: 'Page URL', type: 'url', order: 5 },
|
|
{ name: 'attachments', display: 'Files', type: 'file', order: 6 },
|
|
{ name: 'created_at', display: 'Created At', type: 'datetime', order: 7, is_system: true },
|
|
{ name: 'updated_at', display: 'Updated At', type: 'datetime', order: 8, is_system: true }
|
|
]);
|
|
|
|
return tableId;
|
|
}
|