f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
612 lines
24 KiB
JavaScript
612 lines
24 KiB
JavaScript
// System Table Service - Sync real database tables to universal_tables interface
|
|
import { dbAll, dbGet } from '../database/connection.js';
|
|
|
|
/**
|
|
* Get data from real system tables and format as universal table rows
|
|
* @param {string} syncTarget - The database table name (users, projects, etc.)
|
|
* @returns {Promise<Array>} Array of rows in universal format
|
|
*/
|
|
export async function getSystemTableData(syncTarget, projectId) {
|
|
if (!syncTarget) {
|
|
throw new Error('sync_target is required for system tables');
|
|
}
|
|
|
|
// Derive space for per-space scoping (projects, tables, files, etc.)
|
|
let spaceId = null;
|
|
if (projectId) {
|
|
const project = await dbGet('SELECT space_id FROM projects WHERE id = ?', [projectId]);
|
|
spaceId = project?.space_id || null;
|
|
}
|
|
|
|
// Admin System Management (space_id=1) should see global stock for projects/tables
|
|
const isAdminSystemSpace = spaceId === 1;
|
|
|
|
switch (syncTarget) {
|
|
case 'users':
|
|
return await getUsersData();
|
|
case 'projects':
|
|
return await getProjectsData(spaceId, isAdminSystemSpace);
|
|
case 'spaces':
|
|
return await getSpacesData();
|
|
case 'data_sources':
|
|
return await getDataSourcesData();
|
|
case 'universal_tables':
|
|
return await getTablesData(spaceId, isAdminSystemSpace);
|
|
case 'table_columns':
|
|
return await getColumnsData();
|
|
case 'table_rows':
|
|
return await getRowsData();
|
|
case 'audit_log':
|
|
return await getAuditLogData();
|
|
case 'system_settings':
|
|
return await getSystemSettingsData();
|
|
case 'files':
|
|
return await getFilesData(spaceId);
|
|
case 'storage_providers':
|
|
return await getStorageProvidersData();
|
|
case 'automations':
|
|
return await getAutomationsData();
|
|
case 'automation_logs':
|
|
return await getAutomationLogsData();
|
|
case 'widgets':
|
|
return await getWidgetsData();
|
|
case '_inflight_runs':
|
|
return await getInflightRunsData(spaceId, isAdminSystemSpace);
|
|
default:
|
|
throw new Error(`Unknown sync_target: ${syncTarget}`);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get columns definition for system table
|
|
* @param {string} syncTarget - The database table name
|
|
* @returns {Array} Array of column definitions
|
|
*/
|
|
export function getSystemTableColumns(syncTarget) {
|
|
const columnsMap = {
|
|
users: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, orderIndex: 0 },
|
|
{ id: 'email', name: 'email', displayName: 'Email', type: 'email', isRequired: true, orderIndex: 1 },
|
|
{ id: 'name', name: 'name', displayName: 'Name', type: 'text', isRequired: true, orderIndex: 2 },
|
|
{ id: 'role', name: 'role', displayName: 'Role', type: 'select', config: { options: [{ label: 'Owner', value: 'owner' }, { label: 'Admin', value: 'admin' }, { label: 'User', value: 'user' }] }, orderIndex: 3 },
|
|
{ id: 'password_hash', name: 'password_hash', displayName: 'Password', type: 'password', isReadonly: true, orderIndex: 4 },
|
|
{ id: 'email_verified', name: 'email_verified', displayName: 'Email Verified', type: 'checkbox', orderIndex: 5 },
|
|
{ id: 'user_type', name: 'user_type', displayName: 'User Type', type: 'select', config: { options: [{ label: 'Human', value: 'human' }, { label: 'Agent', value: 'agent' }, { label: 'Bot', value: 'bot' }, { label: 'Service', value: 'service' }] }, orderIndex: 6 },
|
|
{ id: 'managed_by_agent_table_id', name: 'managed_by_agent_table_id', displayName: 'Agent Table ID', type: 'number', isReadonly: true, orderIndex: 7 },
|
|
{ id: 'managed_by_agent_row_id', name: 'managed_by_agent_row_id', displayName: 'Agent Row ID', type: 'number', isReadonly: true, orderIndex: 8 },
|
|
{ id: 'created_at', name: 'created_at', displayName: 'Created At', type: 'datetime', orderIndex: 9 }
|
|
],
|
|
projects: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, orderIndex: 0 },
|
|
{ id: 'name', name: 'name', displayName: 'Name', type: 'text', isRequired: true, orderIndex: 1 },
|
|
{ id: 'description', name: 'description', displayName: 'Description', type: 'text', orderIndex: 2 },
|
|
{ id: 'icon', name: 'icon', displayName: 'Icon', type: 'text', orderIndex: 3 },
|
|
{ id: 'type', name: 'type', displayName: 'Type', type: 'select', config: { options: [{ label: 'Admin Space', value: 'admin_owner_space' }, { label: 'Personal Space', value: 'personal_space' }, { label: 'Custom', value: 'custom' }] }, orderIndex: 4 },
|
|
{ id: 'owner_id', name: 'owner_id', displayName: 'Owner ID', type: 'number', orderIndex: 5 },
|
|
{ id: 'created_at', name: 'created_at', displayName: 'Created At', type: 'datetime', orderIndex: 6 }
|
|
],
|
|
universal_tables: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, orderIndex: 0 },
|
|
{ id: 'project_id', name: 'project_id', displayName: 'Project ID', type: 'number', isRequired: true, orderIndex: 1 },
|
|
{ id: 'name', name: 'name', displayName: 'Name', type: 'text', isRequired: true, orderIndex: 2 },
|
|
{ id: 'description', name: 'description', displayName: 'Description', type: 'text', orderIndex: 3 },
|
|
{ id: 'icon', name: 'icon', displayName: 'Icon', type: 'text', orderIndex: 4 },
|
|
{ id: 'is_system', name: 'is_system', displayName: 'System Table', type: 'checkbox', orderIndex: 5 },
|
|
{ id: 'sync_target', name: 'sync_target', displayName: 'Sync Target', type: 'text', orderIndex: 6 }
|
|
],
|
|
table_columns: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, orderIndex: 0 },
|
|
{ id: 'table_id', name: 'table_id', displayName: 'Table ID', type: 'number', isRequired: true, orderIndex: 1 },
|
|
{ id: 'column_name', name: 'column_name', displayName: 'Column Name', type: 'text', isRequired: true, orderIndex: 2 },
|
|
{ id: 'display_name', name: 'display_name', displayName: 'Display Name', type: 'text', orderIndex: 3 },
|
|
{ id: 'type', name: 'type', displayName: 'Type', type: 'text', orderIndex: 4 },
|
|
{ id: 'is_required', name: 'is_required', displayName: 'Required', type: 'checkbox', orderIndex: 5 }
|
|
],
|
|
table_rows: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, orderIndex: 0 },
|
|
{ id: 'table_id', name: 'table_id', displayName: 'Table ID', type: 'number', isRequired: true, orderIndex: 1 },
|
|
{ id: 'base_id', name: 'base_id', displayName: 'Base ID', type: 'text', isRequired: true, orderIndex: 2 },
|
|
{ id: 'data', name: 'data', displayName: 'Data (JSON)', type: 'text', orderIndex: 3 },
|
|
{ id: 'created_by', name: 'created_by', displayName: 'Created By', type: 'number', orderIndex: 4 }
|
|
],
|
|
audit_log: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, orderIndex: 0 },
|
|
{ id: 'user_id', name: 'user_id', displayName: 'User ID', type: 'number', orderIndex: 1 },
|
|
{ id: 'action', name: 'action', displayName: 'Action', type: 'text', orderIndex: 2 },
|
|
{ id: 'entity_type', name: 'entity_type', displayName: 'Entity Type', type: 'text', orderIndex: 3 },
|
|
{ id: 'entity_id', name: 'entity_id', displayName: 'Entity ID', type: 'text', orderIndex: 4 },
|
|
{ id: 'created_at', name: 'created_at', displayName: 'Created At', type: 'datetime', orderIndex: 5 }
|
|
],
|
|
system_settings: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, orderIndex: 0 },
|
|
{ id: 'key', name: 'key', displayName: 'Key', type: 'text', isRequired: true, orderIndex: 1 },
|
|
{ id: 'value', name: 'value', displayName: 'Value', type: 'text', orderIndex: 2 },
|
|
{ id: 'category', name: 'category', displayName: 'Category', type: 'text', orderIndex: 3 }
|
|
],
|
|
spaces: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, orderIndex: 0 },
|
|
{ id: 'name', name: 'name', displayName: 'Name', type: 'text', isRequired: true, orderIndex: 1 },
|
|
{ id: 'slug', name: 'slug', displayName: 'Slug', type: 'text', orderIndex: 2 },
|
|
{ id: 'owner_id', name: 'owner_id', displayName: 'Owner', type: 'relation', orderIndex: 3 },
|
|
{ id: 'settings', name: 'settings', displayName: 'Settings', type: 'text', orderIndex: 4 },
|
|
{ id: 'created_at', name: 'created_at', displayName: 'Created At', type: 'datetime', orderIndex: 5 },
|
|
{ id: 'updated_at', name: 'updated_at', displayName: 'Updated At', type: 'datetime', orderIndex: 6 }
|
|
],
|
|
// ADR-0063-A §3-rev — universal pause registry. 10 visible cols (metadata
|
|
// intentionally hidden — JSONB internals). Status is a select for the
|
|
// 4-value lifecycle; reason stays open-text so the taxonomy can grow
|
|
// without a migration (see markPaused.js header).
|
|
_inflight_runs: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, isReadonly: true, orderIndex: 0 },
|
|
{ id: 'ticket_id', name: 'ticket_id', displayName: 'Ticket', type: 'number', isReadonly: true, orderIndex: 1 },
|
|
{ id: 'agent_slug', name: 'agent_slug', displayName: 'Agent', type: 'text', isRequired: true, isReadonly: true, orderIndex: 2 },
|
|
{ id: 'conversation_id', name: 'conversation_id', displayName: 'Conversation', type: 'number', isReadonly: true, orderIndex: 3 },
|
|
{ id: 'started_at', name: 'started_at', displayName: 'Started', type: 'datetime', isReadonly: true, orderIndex: 4 },
|
|
{ id: 'last_step_id', name: 'last_step_id', displayName: 'Last Step', type: 'number', isReadonly: true, orderIndex: 5 },
|
|
{ id: 'status', name: 'status', displayName: 'Status', type: 'select', config: { options: [{ label: 'Running', value: 'running' }, { label: 'Paused', value: 'paused' }, { label: 'Done', value: 'done' }, { label: 'Failed', value: 'failed' }] }, isReadonly: true, orderIndex: 6 },
|
|
{ id: 'reason', name: 'reason', displayName: 'Reason', type: 'text', isReadonly: true, orderIndex: 7 },
|
|
{ id: 'resume_at', name: 'resume_at', displayName: 'Resume At', type: 'datetime', isReadonly: true, orderIndex: 8 },
|
|
{ id: 'resume_attempts', name: 'resume_attempts', displayName: 'Attempts', type: 'number', isReadonly: true, orderIndex: 9 }
|
|
],
|
|
// ADR-0012 Phase 8.2 — global template view (one row per preset_name).
|
|
// Lists template widgets only (is_template=true). Per-document instance
|
|
// widgets (legacy) are deliberately excluded from this view.
|
|
widgets: [
|
|
{ id: 'id', name: 'id', displayName: 'ID', type: 'number', isRequired: true, isReadonly: true, orderIndex: 0 },
|
|
{ id: 'preset_name', name: 'preset_name', displayName: 'Preset', type: 'text', isRequired: true, isReadonly: true, orderIndex: 1 },
|
|
{ id: 'title', name: 'title', displayName: 'Title', type: 'text', orderIndex: 2 },
|
|
{ id: 'icon', name: 'icon', displayName: 'Icon', type: 'text', orderIndex: 3 },
|
|
{ id: 'description', name: 'description', displayName: 'Description', type: 'text', orderIndex: 4 },
|
|
{ id: 'config', name: 'config', displayName: 'Config', type: 'json', orderIndex: 5 },
|
|
{ id: 'widget_type', name: 'widget_type', displayName: 'Type', type: 'text', isReadonly: true, orderIndex: 6 },
|
|
{ id: 'created_at', name: 'created_at', displayName: 'Created At', type: 'datetime', isReadonly: true, orderIndex: 7 },
|
|
{ id: 'updated_at', name: 'updated_at', displayName: 'Updated At', type: 'datetime', isReadonly: true, orderIndex: 8 }
|
|
]
|
|
};
|
|
|
|
return columnsMap[syncTarget] || [];
|
|
}
|
|
|
|
// Data fetchers for each system table
|
|
async function getUsersData() {
|
|
const users = await dbAll('SELECT id, email, name, role, password_hash, email_verified, user_type, managed_by_agent_table_id, managed_by_agent_row_id, created_at FROM users ORDER BY id');
|
|
return users.map(u => ({
|
|
id: `user-${u.id}`,
|
|
base_id: `USER${String(u.id).padStart(4, '0')}`,
|
|
data: {
|
|
id: u.id,
|
|
email: u.email,
|
|
name: u.name,
|
|
role: u.role,
|
|
password_hash: u.password_hash ? '••••••••' : null, // Show indicator, not actual hash
|
|
email_verified: u.email_verified,
|
|
user_type: u.user_type || 'human',
|
|
managed_by_agent_table_id: u.managed_by_agent_table_id || null,
|
|
managed_by_agent_row_id: u.managed_by_agent_row_id || null,
|
|
created_at: u.created_at
|
|
},
|
|
created_at: u.created_at,
|
|
updated_at: u.created_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getProjectsData(spaceId, isAdminSystemSpace = false) {
|
|
const query = spaceId && !isAdminSystemSpace
|
|
? 'SELECT id, name, description, icon, type, owner_id, space_id, theme_primary, created_at FROM projects WHERE space_id = ? ORDER BY id'
|
|
: 'SELECT id, name, description, icon, type, owner_id, space_id, theme_primary, created_at FROM projects ORDER BY id';
|
|
|
|
const params = spaceId && !isAdminSystemSpace ? [spaceId] : [];
|
|
const projects = await dbAll(query, params);
|
|
return projects.map(p => ({
|
|
id: `project-${p.id}`,
|
|
base_id: `PROJ${String(p.id).padStart(4, '0')}`,
|
|
data: {
|
|
id: p.id,
|
|
name: p.name,
|
|
description: p.description,
|
|
icon: p.icon,
|
|
type: p.type,
|
|
owner_id: p.owner_id,
|
|
space_id: p.space_id,
|
|
theme_primary: p.theme_primary,
|
|
created_at: p.created_at
|
|
},
|
|
created_at: p.created_at,
|
|
updated_at: p.created_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getSpacesData() {
|
|
const spaces = await dbAll('SELECT id, name, description, icon, type, owner_id, theme_primary, theme_secondary, created_at, updated_at FROM spaces ORDER BY id');
|
|
return spaces.map(s => ({
|
|
id: `space-${s.id}`,
|
|
base_id: `SPC${String(s.id).padStart(4, '0')}`,
|
|
data: {
|
|
id: s.id,
|
|
name: s.name,
|
|
description: s.description,
|
|
icon: s.icon,
|
|
type: s.type,
|
|
owner_id: s.owner_id,
|
|
theme_primary: s.theme_primary,
|
|
theme_secondary: s.theme_secondary,
|
|
created_at: s.created_at
|
|
},
|
|
created_at: s.created_at,
|
|
updated_at: s.updated_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getDataSourcesData() {
|
|
const sources = await dbAll('SELECT id, name, type, db_host, db_port, db_name, db_username, last_test_status, sync_enabled, last_sync_at, created_at FROM data_sources ORDER BY created_at DESC');
|
|
return sources.map(ds => ({
|
|
id: `ds-${ds.id}`,
|
|
base_id: `DS${String(ds.id).substring(0, 8).toUpperCase()}`,
|
|
data: {
|
|
id: ds.id,
|
|
name: ds.name,
|
|
type: ds.type,
|
|
db_host: ds.db_host,
|
|
db_port: ds.db_port,
|
|
db_name: ds.db_name,
|
|
db_username: ds.db_username,
|
|
last_test_status: ds.last_test_status,
|
|
sync_enabled: ds.sync_enabled,
|
|
last_sync_at: ds.last_sync_at,
|
|
created_at: ds.created_at
|
|
},
|
|
created_at: ds.created_at,
|
|
updated_at: ds.created_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getTablesData(spaceId, isAdminSystemSpace = false) {
|
|
const query = spaceId && !isAdminSystemSpace
|
|
? `
|
|
SELECT ut.*
|
|
FROM universal_tables ut
|
|
JOIN projects p ON ut.project_id = p.id
|
|
WHERE p.space_id = ?
|
|
ORDER BY ut.id
|
|
`
|
|
: 'SELECT * FROM universal_tables ORDER BY id';
|
|
|
|
const params = spaceId && !isAdminSystemSpace ? [spaceId] : [];
|
|
const tables = await dbAll(query, params);
|
|
return tables.map(t => ({
|
|
id: `table-${t.id}`,
|
|
base_id: `TBL${String(t.id).padStart(5, '0')}`,
|
|
data: {
|
|
id: t.id,
|
|
project_id: t.project_id,
|
|
name: t.name,
|
|
description: t.description,
|
|
icon: t.icon,
|
|
is_system: t.is_system,
|
|
sync_target: t.sync_target
|
|
},
|
|
created_at: t.created_at,
|
|
updated_at: t.updated_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getColumnsData() {
|
|
const columns = await dbAll('SELECT * FROM table_columns ORDER BY table_id, order_index');
|
|
return columns.map(c => ({
|
|
id: `column-${c.id}`,
|
|
base_id: `COL${String(c.id).padStart(5, '0')}`,
|
|
data: {
|
|
id: c.id,
|
|
table_id: c.table_id,
|
|
column_name: c.column_name,
|
|
display_name: c.display_name,
|
|
type: c.type,
|
|
is_required: c.is_required
|
|
},
|
|
created_at: c.created_at,
|
|
updated_at: c.updated_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getRowsData() {
|
|
const rows = await dbAll('SELECT id, table_id, base_id, data, created_by, created_at FROM table_rows ORDER BY id LIMIT 100');
|
|
return rows.map(r => ({
|
|
id: `row-${r.id}`,
|
|
base_id: r.base_id,
|
|
data: {
|
|
id: r.id,
|
|
table_id: r.table_id,
|
|
base_id: r.base_id,
|
|
data: r.data,
|
|
created_by: r.created_by
|
|
},
|
|
created_at: r.created_at,
|
|
updated_at: r.created_at,
|
|
created_by: r.created_by
|
|
}));
|
|
}
|
|
|
|
async function getAuditLogData() {
|
|
const logs = await dbAll('SELECT * FROM audit_log ORDER BY created_at DESC LIMIT 100');
|
|
return logs.map(l => ({
|
|
id: `audit-${l.id}`,
|
|
base_id: `AUD${String(l.id).padStart(6, '0')}`,
|
|
data: {
|
|
id: l.id,
|
|
user_id: l.user_id,
|
|
action: l.action,
|
|
entity_type: l.entity_type,
|
|
entity_id: l.entity_id,
|
|
created_at: l.created_at
|
|
},
|
|
created_at: l.created_at,
|
|
updated_at: l.created_at,
|
|
created_by: l.user_id
|
|
}));
|
|
}
|
|
|
|
async function getSystemSettingsData() {
|
|
const settings = await dbAll('SELECT * FROM system_settings ORDER BY category, key');
|
|
return settings.map(s => ({
|
|
id: `setting-${s.id}`,
|
|
base_id: `SET${String(s.id).padStart(5, '0')}`,
|
|
data: {
|
|
id: s.id,
|
|
key: s.key,
|
|
value: s.value,
|
|
category: s.category
|
|
},
|
|
created_at: s.created_at,
|
|
updated_at: s.updated_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getFilesData(spaceId) {
|
|
let query = `
|
|
SELECT f.*, u.name as uploaded_by_name
|
|
FROM files f
|
|
LEFT JOIN users u ON f.uploaded_by = u.id
|
|
`;
|
|
const params = [];
|
|
if (spaceId) {
|
|
query += ' WHERE f.space_id = ?';
|
|
params.push(spaceId);
|
|
}
|
|
query += ' ORDER BY f.created_at DESC';
|
|
|
|
const files = await dbAll(query, params);
|
|
return files.map(f => ({
|
|
id: `file-${f.id}`,
|
|
base_id: f.id,
|
|
data: {
|
|
id: f.id,
|
|
name: f.name,
|
|
original_name: f.original_name,
|
|
mime_type: f.mime_type,
|
|
size: f.size,
|
|
url: f.url,
|
|
space_id: f.space_id,
|
|
project_id: f.project_id,
|
|
uploaded_by: f.uploaded_by_name || f.uploaded_by,
|
|
storage_provider_id: f.storage_provider_id,
|
|
description: f.description,
|
|
created_at: f.created_at,
|
|
updated_at: f.updated_at
|
|
},
|
|
created_at: f.created_at,
|
|
updated_at: f.updated_at,
|
|
created_by: f.uploaded_by
|
|
}));
|
|
}
|
|
|
|
async function getStorageProvidersData() {
|
|
const providers = await dbAll('SELECT * FROM storage_providers ORDER BY is_default DESC, name ASC');
|
|
return providers.map(p => ({
|
|
id: `provider-${p.id}`,
|
|
base_id: p.id,
|
|
data: {
|
|
id: p.id,
|
|
name: p.name,
|
|
type: p.type,
|
|
is_default: p.is_default,
|
|
is_enabled: p.is_enabled,
|
|
config: p.config,
|
|
created_at: p.created_at,
|
|
updated_at: p.updated_at
|
|
},
|
|
created_at: p.created_at,
|
|
updated_at: p.updated_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getAutomationsData() {
|
|
const automations = await dbAll(`
|
|
SELECT a.*, t.name as table_name
|
|
FROM automations a
|
|
LEFT JOIN universal_tables t ON a.table_id = t.id
|
|
ORDER BY a.created_at DESC
|
|
`);
|
|
|
|
// Get last run info for each automation
|
|
const lastRuns = await dbAll(`
|
|
SELECT automation_id, MAX(executed_at) as last_run, COUNT(*) as run_count
|
|
FROM automation_logs
|
|
GROUP BY automation_id
|
|
`);
|
|
const runMap = new Map(lastRuns.map(r => [r.automation_id, r]));
|
|
|
|
return automations.map(a => {
|
|
const runInfo = runMap.get(a.id) || { last_run: null, run_count: 0 };
|
|
return {
|
|
id: `automation-${a.id}`,
|
|
base_id: a.id,
|
|
data: {
|
|
id: a.id,
|
|
name: a.name,
|
|
description: a.description,
|
|
table_id: a.table_id,
|
|
table_name: a.table_name,
|
|
trigger_type: a.trigger_type,
|
|
action_type: a.action_type,
|
|
is_active: Boolean(a.is_active),
|
|
trigger_config: a.trigger_config,
|
|
action_config: a.action_config,
|
|
last_run: runInfo.last_run,
|
|
run_count: runInfo.run_count,
|
|
created_at: a.created_at
|
|
},
|
|
created_at: a.created_at,
|
|
updated_at: a.updated_at,
|
|
created_by: 'system'
|
|
};
|
|
});
|
|
}
|
|
|
|
// ADR-0012 Phase 8.2 — global widget templates (one row per preset).
|
|
// Returns ONLY rows where is_template=true. Doc-owned instance widgets
|
|
// stay invisible to this view (they migrate to per-doc atom rows during
|
|
// Phase 8.4). No per-space scoping: templates are a global registry,
|
|
// every space sees the same 8 rows.
|
|
async function getWidgetsData() {
|
|
const widgets = await dbAll(`
|
|
SELECT id, preset_name, widget_type, title, description, icon,
|
|
config, position, source_widget_id, owner_kind, owner_id,
|
|
created_at, updated_at
|
|
FROM widgets
|
|
WHERE is_template = true
|
|
ORDER BY preset_name
|
|
`);
|
|
return widgets.map(w => {
|
|
let configValue = w.config;
|
|
try { configValue = w.config ? JSON.parse(w.config) : {}; }
|
|
catch { /* keep raw text on parse failure */ }
|
|
|
|
return {
|
|
id: `widget-${w.id}`,
|
|
base_id: `WGT${String(w.id).padStart(5, '0')}`,
|
|
data: {
|
|
id: w.id,
|
|
preset_name: w.preset_name,
|
|
widget_type: w.widget_type,
|
|
title: w.title,
|
|
description: w.description,
|
|
icon: w.icon,
|
|
config: configValue,
|
|
created_at: w.created_at,
|
|
updated_at: w.updated_at
|
|
},
|
|
created_at: w.created_at,
|
|
updated_at: w.updated_at,
|
|
created_by: 'system'
|
|
};
|
|
});
|
|
}
|
|
|
|
// ADR-0063-A §3-rev — surface the universal pause registry through the
|
|
// is_system+sync_target pattern. Space filter: admin (project in space 1)
|
|
// sees global; everyone else sees rows whose conversation belonged to their
|
|
// space, recorded as `metadata.space_id` by markPaused() (Option A — no
|
|
// join through 1784, see [[project_agent_slug_routing]]).
|
|
//
|
|
// ADR-0063-A §P3 — exported as the single source-of-truth for the scope
|
|
// filter so the `query_inflight_paused` MCP tool (used by the watchdog)
|
|
// builds the same WHERE clause. Pass `{ status, agent_slug, conversation_id,
|
|
// limit }` to reuse the predicate from the non-universal-wrapped path.
|
|
export async function queryInflightRuns({
|
|
spaceId = null,
|
|
isAdminSystemSpace = false,
|
|
status = null,
|
|
agent_slug = null,
|
|
conversation_id = null,
|
|
limit = 1000,
|
|
} = {}) {
|
|
const clauses = [];
|
|
const params = [];
|
|
if (spaceId && !isAdminSystemSpace) {
|
|
clauses.push(`(metadata->>'space_id')::int = ?`);
|
|
params.push(spaceId);
|
|
}
|
|
if (status) {
|
|
clauses.push('status = ?');
|
|
params.push(status);
|
|
}
|
|
if (agent_slug) {
|
|
clauses.push('agent_slug = ?');
|
|
params.push(agent_slug);
|
|
}
|
|
if (conversation_id != null) {
|
|
clauses.push('conversation_id = ?');
|
|
params.push(conversation_id);
|
|
}
|
|
const where = clauses.length ? `WHERE ${clauses.join(' AND ')}` : '';
|
|
const cappedLimit = Math.max(1, Math.min(Number(limit) || 1000, 1000));
|
|
params.push(cappedLimit);
|
|
const sql = `
|
|
SELECT id, ticket_id, agent_slug, conversation_id, started_at, last_step_id,
|
|
status, reason, resume_at, resume_attempts, metadata, updated_at
|
|
FROM _inflight_runs
|
|
${where}
|
|
ORDER BY started_at DESC
|
|
LIMIT ?
|
|
`;
|
|
return await dbAll(sql, params);
|
|
}
|
|
|
|
async function getInflightRunsData(spaceId, isAdminSystemSpace = false) {
|
|
const rows = await queryInflightRuns({ spaceId, isAdminSystemSpace, limit: 1000 });
|
|
return rows.map(r => ({
|
|
id: `inflight-${r.id}`,
|
|
base_id: `IFR${String(r.id).padStart(6, '0')}`,
|
|
data: {
|
|
id: r.id,
|
|
ticket_id: r.ticket_id,
|
|
agent_slug: r.agent_slug,
|
|
conversation_id: r.conversation_id,
|
|
started_at: r.started_at,
|
|
last_step_id: r.last_step_id,
|
|
status: r.status,
|
|
reason: r.reason,
|
|
resume_at: r.resume_at,
|
|
resume_attempts: r.resume_attempts
|
|
},
|
|
created_at: r.started_at,
|
|
updated_at: r.updated_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|
|
|
|
async function getAutomationLogsData() {
|
|
const logs = await dbAll(`
|
|
SELECT l.*, a.name as automation_name
|
|
FROM automation_logs l
|
|
LEFT JOIN automations a ON l.automation_id = a.id
|
|
ORDER BY l.executed_at DESC
|
|
LIMIT 1000
|
|
`);
|
|
return logs.map(l => ({
|
|
id: `log-${l.id}`,
|
|
base_id: l.id,
|
|
data: {
|
|
id: l.id,
|
|
automation_id: l.automation_id,
|
|
automation_name: l.automation_name,
|
|
status: l.status,
|
|
trigger_data: l.trigger_data,
|
|
result_data: l.result_data,
|
|
error_message: l.error_message,
|
|
duration_ms: l.duration_ms,
|
|
executed_at: l.executed_at
|
|
},
|
|
created_at: l.executed_at,
|
|
updated_at: l.executed_at,
|
|
created_by: 'system'
|
|
}));
|
|
}
|