f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
522 lines
21 KiB
JavaScript
522 lines
21 KiB
JavaScript
// Business Pack Service
|
|
// Creates default business infrastructure: Users table, Roles table, Permissions
|
|
import { dbRun, dbGet, dbAll } from '../database/connection.js';
|
|
import { apiLogger } from '../utils/logger.js';
|
|
|
|
/**
|
|
* Generate unique base_id for rows
|
|
*/
|
|
function generateBaseId(prefix = 'row') {
|
|
return `${prefix}-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`;
|
|
}
|
|
|
|
/**
|
|
* Create a project within a space
|
|
*/
|
|
async function createProject(spaceId, name, type, icon, description, ownerId) {
|
|
const result = await dbRun(`
|
|
INSERT INTO projects (name, type, icon, description, owner_id, space_id, settings, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, '{}', datetime('now'), datetime('now'))
|
|
`, [name, type, icon, description, ownerId, spaceId]);
|
|
|
|
return result.lastInsertRowid;
|
|
}
|
|
|
|
/**
|
|
* Create a table within a project
|
|
*/
|
|
async function createTable(projectId, name, icon, description) {
|
|
const result = await dbRun(`
|
|
INSERT INTO universal_tables (project_id, name, icon, description, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, datetime('now'), datetime('now'))
|
|
`, [projectId, name, icon, description]);
|
|
|
|
return result.lastInsertRowid;
|
|
}
|
|
|
|
/**
|
|
* Create Business Pack for a space
|
|
* Creates: Users table, Roles table, Access control structures
|
|
*/
|
|
export async function createBusinessPack(spaceId, userId) {
|
|
apiLogger.info({ spaceId }, 'Creating Business Pack');
|
|
|
|
try {
|
|
// 1. Create "System Data" project (system_data type for reliable identification)
|
|
const projectId = await createProject(
|
|
spaceId,
|
|
'System Data',
|
|
'system_data',
|
|
'⚙️',
|
|
'System tables: users, roles, dictionaries',
|
|
userId
|
|
);
|
|
|
|
apiLogger.debug({ projectId }, 'Created project System Data');
|
|
|
|
// 2. Create Users table
|
|
const usersTableId = await createTable(
|
|
projectId,
|
|
'Users',
|
|
'👥',
|
|
'Space users with access control'
|
|
);
|
|
|
|
apiLogger.debug({ usersTableId }, 'Created Users table');
|
|
|
|
// 3. Create columns for Users table (with granular access control per level)
|
|
const userColumns = [
|
|
{ name: 'system_user_id', display_name: 'System ID', type: 'number', width: 100 },
|
|
{ name: 'email', display_name: 'Email', type: 'email', width: 200 },
|
|
{ name: 'name', display_name: 'Name', type: 'text', width: 150 },
|
|
{ name: 'role', display_name: 'Role', type: 'select', width: 120 },
|
|
{ name: 'active', display_name: 'Active', type: 'checkbox', width: 80 },
|
|
// Space level access
|
|
{ name: 'space_owner', display_name: 'Space Owner', type: 'multi-select', width: 150 },
|
|
{ name: 'space_admin', display_name: 'Space Admin', type: 'multi-select', width: 150 },
|
|
{ name: 'space_editor', display_name: 'Space Editor', type: 'multi-select', width: 150 },
|
|
{ name: 'space_viewer', display_name: 'Space Viewer', type: 'multi-select', width: 150 },
|
|
{ name: 'space_denied', display_name: 'Space Denied', type: 'multi-select', width: 150 },
|
|
// Project level access
|
|
{ name: 'project_owner', display_name: 'Project Owner', type: 'multi-select', width: 150 },
|
|
{ name: 'project_admin', display_name: 'Project Admin', type: 'multi-select', width: 150 },
|
|
{ name: 'project_editor', display_name: 'Project Editor', type: 'multi-select', width: 150 },
|
|
{ name: 'project_viewer', display_name: 'Project Viewer', type: 'multi-select', width: 150 },
|
|
{ name: 'project_denied', display_name: 'Project Denied', type: 'multi-select', width: 150 },
|
|
// Table level access
|
|
{ name: 'table_owner', display_name: 'Table Owner', type: 'multi-select', width: 150 },
|
|
{ name: 'table_admin', display_name: 'Table Admin', type: 'multi-select', width: 150 },
|
|
{ name: 'table_editor', display_name: 'Table Editor', type: 'multi-select', width: 150 },
|
|
{ name: 'table_viewer', display_name: 'Table Viewer', type: 'multi-select', width: 150 },
|
|
{ name: 'table_denied', display_name: 'Table Denied', type: 'multi-select', width: 150 },
|
|
// Column level access (format: "tableId:columnKey")
|
|
{ name: 'column_owner', display_name: 'Column Owner', type: 'multi-select', width: 150 },
|
|
{ name: 'column_admin', display_name: 'Column Admin', type: 'multi-select', width: 150 },
|
|
{ name: 'column_editor', display_name: 'Column Editor', type: 'multi-select', width: 150 },
|
|
{ name: 'column_viewer', display_name: 'Column Viewer', type: 'multi-select', width: 150 },
|
|
{ name: 'column_denied', display_name: 'Column Denied', type: 'multi-select', width: 150 }
|
|
];
|
|
|
|
for (let i = 0; i < userColumns.length; i++) {
|
|
const col = userColumns[i];
|
|
let config = '{}';
|
|
|
|
if (col.name === 'role') {
|
|
// Role hierarchy: owner > admin > editor > viewer > denied
|
|
config = JSON.stringify({ options: ['owner', 'admin', 'editor', 'viewer', 'denied'] });
|
|
} else if (col.type === 'multi-select') {
|
|
// Empty options - will be filled dynamically with project/table IDs
|
|
config = JSON.stringify({ options: [] });
|
|
}
|
|
|
|
await dbRun(
|
|
`INSERT INTO table_columns (table_id, column_name, display_name, type, config, width, order_index, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[usersTableId, col.name, col.display_name, col.type, config, col.width, i + 1]
|
|
);
|
|
}
|
|
|
|
apiLogger.debug({ count: userColumns.length }, 'Created columns for Users table');
|
|
|
|
// 4. Add owner as first user (with full owner access to this space)
|
|
const owner = await dbGet('SELECT email, name FROM users WHERE id = ?', [userId]);
|
|
const ownerName = owner?.name || owner?.email?.split('@')[0] || 'Owner';
|
|
await dbRun(
|
|
`INSERT INTO table_rows (table_id, base_id, data, created_by, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[
|
|
usersTableId,
|
|
generateBaseId('user'),
|
|
JSON.stringify({
|
|
system_user_id: userId,
|
|
email: owner?.email || '',
|
|
name: ownerName,
|
|
role: 'owner',
|
|
active: true,
|
|
// Space level - owner of this space
|
|
space_owner: [String(spaceId)],
|
|
space_admin: [],
|
|
space_editor: [],
|
|
space_viewer: [],
|
|
space_denied: [],
|
|
// Project level - empty means access all via space
|
|
project_owner: [],
|
|
project_admin: [],
|
|
project_editor: [],
|
|
project_viewer: [],
|
|
project_denied: [],
|
|
// Table level
|
|
table_owner: [],
|
|
table_admin: [],
|
|
table_editor: [],
|
|
table_viewer: [],
|
|
table_denied: [],
|
|
// Column level
|
|
column_owner: [],
|
|
column_admin: [],
|
|
column_editor: [],
|
|
column_viewer: [],
|
|
column_denied: []
|
|
}),
|
|
userId
|
|
]
|
|
);
|
|
|
|
apiLogger.debug('Added owner as first user in Users table');
|
|
|
|
// 5. Create Roles table
|
|
const rolesTableId = await createTable(
|
|
projectId,
|
|
'Roles',
|
|
'🎭',
|
|
'Roles and permissions'
|
|
);
|
|
|
|
apiLogger.debug({ rolesTableId }, 'Created Roles table');
|
|
|
|
// Create columns for Roles table
|
|
const roleColumns = [
|
|
{ name: 'role_name', display_name: 'Role Name', type: 'text', width: 150 },
|
|
{ name: 'permissions', display_name: 'Permissions', type: 'multi-select', width: 200 },
|
|
{ name: 'description', display_name: 'Description', type: 'text', width: 250 }
|
|
];
|
|
|
|
for (let i = 0; i < roleColumns.length; i++) {
|
|
const col = roleColumns[i];
|
|
const config = col.name === 'permissions'
|
|
? JSON.stringify({ options: ['read', 'write', 'delete', 'admin'] })
|
|
: JSON.stringify({});
|
|
|
|
await dbRun(
|
|
`INSERT INTO table_columns (table_id, column_name, display_name, type, config, width, order_index, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[rolesTableId, col.name, col.display_name, col.type, config, col.width, i + 1]
|
|
);
|
|
}
|
|
|
|
// Add default roles
|
|
const defaultRoles = [
|
|
{ role_name: 'admin', permissions: ['read', 'write', 'delete', 'admin'], description: 'Full access' },
|
|
{ role_name: 'editor', permissions: ['read', 'write'], description: 'Read and write' },
|
|
{ role_name: 'viewer', permissions: ['read'], description: 'Read only' }
|
|
];
|
|
|
|
for (const role of defaultRoles) {
|
|
await dbRun(
|
|
`INSERT INTO table_rows (table_id, base_id, data, created_by, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[rolesTableId, generateBaseId('role'), JSON.stringify(role), userId]
|
|
);
|
|
}
|
|
|
|
apiLogger.debug({ count: defaultRoles.length }, 'Created default roles');
|
|
|
|
return {
|
|
project_id: projectId,
|
|
project_name: 'System Data',
|
|
users_table_id: usersTableId,
|
|
roles_table_id: rolesTableId,
|
|
status: 'created'
|
|
};
|
|
} catch (error) {
|
|
apiLogger.error({ err: error, spaceId }, 'Error creating Business Pack');
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get or create "System Data" project for a space
|
|
* Uses type='system_data' for reliable identification regardless of name
|
|
*/
|
|
export async function getOrCreateDataSourcesProject(spaceId, userId) {
|
|
// Check if system project already exists by type (most reliable)
|
|
let project = await dbGet(
|
|
`SELECT id, name, type FROM projects WHERE space_id = ? AND type = 'system_data'`,
|
|
[spaceId]
|
|
);
|
|
|
|
// Fallback: check old type names for migration
|
|
if (!project) {
|
|
project = await dbGet(
|
|
`SELECT id, name, type FROM projects WHERE space_id = ? AND (type = 'data_sources' OR type = 'access_management')`,
|
|
[spaceId]
|
|
);
|
|
}
|
|
|
|
if (project) {
|
|
// Migrate old project to new standard (type='system_data', name='System Data')
|
|
if (project.type !== 'system_data' || project.name !== 'System Data') {
|
|
await dbRun(
|
|
`UPDATE projects SET name = 'System Data', type = 'system_data', icon = '⚙️' WHERE id = ?`,
|
|
[project.id]
|
|
);
|
|
apiLogger.info({ projectId: project.id }, 'Migrated project to System Data');
|
|
}
|
|
return { project_id: project.id, status: 'existing' };
|
|
}
|
|
|
|
// Create new system project
|
|
const projectId = await createProject(
|
|
spaceId,
|
|
'System Data',
|
|
'system_data',
|
|
'⚙️',
|
|
'System tables: users, roles, dictionaries',
|
|
userId
|
|
);
|
|
|
|
return { project_id: projectId, status: 'created' };
|
|
}
|
|
|
|
/**
|
|
* Get or create Users table in System Data project
|
|
*/
|
|
export async function getOrCreateUsersTable(spaceId, userId) {
|
|
// First ensure we have the project
|
|
const projectResult = await getOrCreateDataSourcesProject(spaceId, userId);
|
|
const projectId = projectResult.project_id;
|
|
|
|
// Check if Users table already exists (case-insensitive)
|
|
let usersTable = await dbGet(
|
|
`SELECT id FROM universal_tables WHERE project_id = ? AND (LOWER(name) = 'users' OR LOWER(name) = 'пользователи')`,
|
|
[projectId]
|
|
);
|
|
|
|
if (usersTable) {
|
|
return { table_id: usersTable.id, project_id: projectId, status: 'existing' };
|
|
}
|
|
|
|
// Create Users table
|
|
const usersTableId = await createTable(
|
|
projectId,
|
|
'Users',
|
|
'👥',
|
|
'Space users with access control'
|
|
);
|
|
|
|
// Create columns for Users table (with granular access control per level)
|
|
const userColumns = [
|
|
{ name: 'system_user_id', display_name: 'System ID', type: 'number', width: 100 },
|
|
{ name: 'email', display_name: 'Email', type: 'email', width: 200 },
|
|
{ name: 'name', display_name: 'Name', type: 'text', width: 150 },
|
|
{ name: 'role', display_name: 'Role', type: 'select', width: 120 },
|
|
{ name: 'active', display_name: 'Active', type: 'checkbox', width: 80 },
|
|
// Space level access
|
|
{ name: 'space_owner', display_name: 'Space Owner', type: 'multi-select', width: 150 },
|
|
{ name: 'space_admin', display_name: 'Space Admin', type: 'multi-select', width: 150 },
|
|
{ name: 'space_editor', display_name: 'Space Editor', type: 'multi-select', width: 150 },
|
|
{ name: 'space_viewer', display_name: 'Space Viewer', type: 'multi-select', width: 150 },
|
|
{ name: 'space_denied', display_name: 'Space Denied', type: 'multi-select', width: 150 },
|
|
// Project level access
|
|
{ name: 'project_owner', display_name: 'Project Owner', type: 'multi-select', width: 150 },
|
|
{ name: 'project_admin', display_name: 'Project Admin', type: 'multi-select', width: 150 },
|
|
{ name: 'project_editor', display_name: 'Project Editor', type: 'multi-select', width: 150 },
|
|
{ name: 'project_viewer', display_name: 'Project Viewer', type: 'multi-select', width: 150 },
|
|
{ name: 'project_denied', display_name: 'Project Denied', type: 'multi-select', width: 150 },
|
|
// Table level access
|
|
{ name: 'table_owner', display_name: 'Table Owner', type: 'multi-select', width: 150 },
|
|
{ name: 'table_admin', display_name: 'Table Admin', type: 'multi-select', width: 150 },
|
|
{ name: 'table_editor', display_name: 'Table Editor', type: 'multi-select', width: 150 },
|
|
{ name: 'table_viewer', display_name: 'Table Viewer', type: 'multi-select', width: 150 },
|
|
{ name: 'table_denied', display_name: 'Table Denied', type: 'multi-select', width: 150 },
|
|
// Column level access (format: "tableId:columnKey")
|
|
{ name: 'column_owner', display_name: 'Column Owner', type: 'multi-select', width: 150 },
|
|
{ name: 'column_admin', display_name: 'Column Admin', type: 'multi-select', width: 150 },
|
|
{ name: 'column_editor', display_name: 'Column Editor', type: 'multi-select', width: 150 },
|
|
{ name: 'column_viewer', display_name: 'Column Viewer', type: 'multi-select', width: 150 },
|
|
{ name: 'column_denied', display_name: 'Column Denied', type: 'multi-select', width: 150 }
|
|
];
|
|
|
|
for (let i = 0; i < userColumns.length; i++) {
|
|
const col = userColumns[i];
|
|
let config = '{}';
|
|
|
|
if (col.name === 'role') {
|
|
// Role hierarchy: owner > admin > editor > viewer > denied
|
|
config = JSON.stringify({ options: ['owner', 'admin', 'editor', 'viewer', 'denied'] });
|
|
} else if (col.type === 'multi-select') {
|
|
config = JSON.stringify({ options: [] });
|
|
}
|
|
|
|
await dbRun(
|
|
`INSERT INTO table_columns (table_id, column_name, display_name, type, config, width, order_index, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[usersTableId, col.name, col.display_name, col.type, config, col.width, i + 1]
|
|
);
|
|
}
|
|
|
|
// Add owner as first user (with full owner access to this space)
|
|
const owner = await dbGet('SELECT email, name FROM users WHERE id = ?', [userId]);
|
|
const ownerName = owner?.name || owner?.email?.split('@')[0] || 'Owner';
|
|
await dbRun(
|
|
`INSERT INTO table_rows (table_id, base_id, data, created_by, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[
|
|
usersTableId,
|
|
generateBaseId('user'),
|
|
JSON.stringify({
|
|
system_user_id: userId,
|
|
email: owner?.email || '',
|
|
name: ownerName,
|
|
role: 'owner',
|
|
active: true,
|
|
// Space level - owner of this space
|
|
space_owner: [String(spaceId)],
|
|
space_admin: [],
|
|
space_editor: [],
|
|
space_viewer: [],
|
|
space_denied: [],
|
|
// Project level - empty means access all via space
|
|
project_owner: [],
|
|
project_admin: [],
|
|
project_editor: [],
|
|
project_viewer: [],
|
|
project_denied: [],
|
|
// Table level
|
|
table_owner: [],
|
|
table_admin: [],
|
|
table_editor: [],
|
|
table_viewer: [],
|
|
table_denied: [],
|
|
// Column level
|
|
column_owner: [],
|
|
column_admin: [],
|
|
column_editor: [],
|
|
column_viewer: [],
|
|
column_denied: []
|
|
}),
|
|
userId
|
|
]
|
|
);
|
|
|
|
return { table_id: usersTableId, project_id: projectId, status: 'created' };
|
|
}
|
|
|
|
/**
|
|
* Get or create Roles table in System Data project
|
|
*/
|
|
export async function getOrCreateRolesTable(spaceId, userId) {
|
|
// First ensure we have the project
|
|
const projectResult = await getOrCreateDataSourcesProject(spaceId, userId);
|
|
const projectId = projectResult.project_id;
|
|
|
|
// Check if Roles table already exists (check both old and new names)
|
|
let rolesTable = await dbGet(
|
|
`SELECT id FROM universal_tables WHERE project_id = ? AND (name = 'Roles' OR name = 'Роли')`,
|
|
[projectId]
|
|
);
|
|
|
|
if (rolesTable) {
|
|
// Migrate to English name if needed
|
|
await dbRun(`UPDATE universal_tables SET name = 'Roles' WHERE id = ?`, [rolesTable.id]);
|
|
return { table_id: rolesTable.id, project_id: projectId, status: 'existing' };
|
|
}
|
|
|
|
// Create Roles table
|
|
const rolesTableId = await createTable(
|
|
projectId,
|
|
'Roles',
|
|
'🎭',
|
|
'Roles and permissions'
|
|
);
|
|
|
|
// Create columns for Roles table
|
|
const roleColumns = [
|
|
{ name: 'role_name', display_name: 'Role Name', type: 'text', width: 150 },
|
|
{ name: 'permissions', display_name: 'Permissions', type: 'multi-select', width: 200 },
|
|
{ name: 'description', display_name: 'Description', type: 'text', width: 250 }
|
|
];
|
|
|
|
for (let i = 0; i < roleColumns.length; i++) {
|
|
const col = roleColumns[i];
|
|
const config = col.name === 'permissions'
|
|
? JSON.stringify({ options: ['read', 'write', 'delete', 'admin'] })
|
|
: JSON.stringify({});
|
|
|
|
await dbRun(
|
|
`INSERT INTO table_columns (table_id, column_name, display_name, type, config, width, order_index, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[rolesTableId, col.name, col.display_name, col.type, config, col.width, i + 1]
|
|
);
|
|
}
|
|
|
|
// Add default roles
|
|
const defaultRoles = [
|
|
{ role_name: 'admin', permissions: ['read', 'write', 'delete', 'admin'], description: 'Full access' },
|
|
{ role_name: 'editor', permissions: ['read', 'write'], description: 'Read and write' },
|
|
{ role_name: 'viewer', permissions: ['read'], description: 'Read only' }
|
|
];
|
|
|
|
for (const role of defaultRoles) {
|
|
await dbRun(
|
|
`INSERT INTO table_rows (table_id, base_id, data, created_by, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[rolesTableId, generateBaseId('role'), JSON.stringify(role), userId]
|
|
);
|
|
}
|
|
|
|
return { table_id: rolesTableId, project_id: projectId, status: 'created' };
|
|
}
|
|
|
|
/**
|
|
* Get or create Variables table in System Data project (ADR-026)
|
|
* @param {number} spaceId - Space ID
|
|
* @param {number} userId - User ID creating the table
|
|
* @returns {Promise<{table_id: number, project_id: number, status: 'existing'|'created'}>}
|
|
*/
|
|
export async function getOrCreateVariablesTable(spaceId, userId) {
|
|
// First ensure we have the System Data project
|
|
const projectResult = await getOrCreateDataSourcesProject(spaceId, userId);
|
|
const projectId = projectResult.project_id;
|
|
|
|
// Check if Variables table already exists
|
|
let variablesTable = await dbGet(
|
|
`SELECT id FROM universal_tables WHERE project_id = ? AND LOWER(name) = 'variables'`,
|
|
[projectId]
|
|
);
|
|
|
|
if (variablesTable) {
|
|
return { table_id: variablesTable.id, project_id: projectId, status: 'existing' };
|
|
}
|
|
|
|
// Create Variables table
|
|
const variablesTableId = await createTable(
|
|
projectId,
|
|
'Variables',
|
|
'🧮',
|
|
'Space variables for formulas and calculations (ADR-026)'
|
|
);
|
|
|
|
// Create columns for Variables table
|
|
const variableColumns = [
|
|
{ name: 'name', display_name: 'Name', type: 'text', width: 150 },
|
|
{ name: 'scope_type', display_name: 'Scope', type: 'select', width: 120,
|
|
config: { options: [
|
|
{ label: '🌍 Space', value: 'space', color: '#3b82f6' },
|
|
{ label: '📊 Table', value: 'table', color: '#10b981' },
|
|
{ label: '📈 Dashboard', value: 'dashboard', color: '#8b5cf6' }
|
|
]}
|
|
},
|
|
{ name: 'scope_ref', display_name: 'Applies To', type: 'number', width: 100 },
|
|
{ name: 'formula', display_name: 'Formula', type: 'textarea', width: 250 },
|
|
{ name: 'description', display_name: 'Description', type: 'text', width: 200 },
|
|
{ name: 'stream_id', display_name: 'Stream', type: 'number', width: 80, config: { default: 1 } },
|
|
{ name: 'order_index', display_name: 'Order', type: 'number', width: 80 },
|
|
{ name: 'cached_value', display_name: 'Current Value', type: 'text', width: 150 },
|
|
{ name: 'cached_at', display_name: 'Cached At', type: 'datetime', width: 150 },
|
|
{ name: 'dependencies', display_name: 'Dependencies', type: 'textarea', width: 200 }
|
|
];
|
|
|
|
for (let i = 0; i < variableColumns.length; i++) {
|
|
const col = variableColumns[i];
|
|
const config = col.config ? JSON.stringify(col.config) : '{}';
|
|
|
|
await dbRun(
|
|
`INSERT INTO table_columns (table_id, column_name, display_name, type, config, width, order_index, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))`,
|
|
[variablesTableId, col.name, col.display_name, col.type, config, col.width, i + 1]
|
|
);
|
|
}
|
|
|
|
return { table_id: variablesTableId, project_id: projectId, status: 'created' };
|
|
}
|