f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
84 lines
2.7 KiB
JavaScript
84 lines
2.7 KiB
JavaScript
// backend/database/migrations/knex/010_create_system_tables.js
|
|
// System Tables: Audit Log, System Settings, API Keys, Schema Layouts
|
|
export async function up(knex) {
|
|
// Audit Log
|
|
await knex.schema.createTable('audit_log', (table) => {
|
|
table.increments('id').primary();
|
|
table.integer('user_id').unsigned()
|
|
.references('id').inTable('users').onDelete('SET NULL');
|
|
table.string('action', 100).notNullable();
|
|
table.string('entity_type', 100);
|
|
table.string('entity_id', 255);
|
|
table.text('details');
|
|
table.string('ip_address', 45);
|
|
table.text('user_agent');
|
|
|
|
table.timestamp('created_at').defaultTo(knex.fn.now());
|
|
|
|
table.index('user_id');
|
|
table.index('action');
|
|
table.index('entity_type');
|
|
table.index('created_at');
|
|
});
|
|
|
|
// System Settings
|
|
await knex.schema.createTable('system_settings', (table) => {
|
|
table.string('key', 255).primary();
|
|
table.text('value');
|
|
table.string('type', 50).defaultTo('string');
|
|
table.text('description');
|
|
|
|
table.timestamp('updated_at').defaultTo(knex.fn.now());
|
|
});
|
|
|
|
// API Keys
|
|
await knex.schema.createTable('api_keys', (table) => {
|
|
table.increments('id').primary();
|
|
table.integer('user_id').unsigned().notNullable()
|
|
.references('id').inTable('users').onDelete('CASCADE');
|
|
|
|
// Key identification
|
|
table.string('key_prefix', 20).notNullable();
|
|
table.string('key_hash', 255).notNullable();
|
|
table.string('name', 255).notNullable();
|
|
|
|
// Permissions (JSON array)
|
|
table.text('scopes').defaultTo('["*"]');
|
|
|
|
// Rate limiting
|
|
table.integer('rate_limit').defaultTo(1000);
|
|
|
|
// Usage tracking
|
|
table.timestamp('last_used_at');
|
|
table.integer('request_count').defaultTo(0);
|
|
|
|
// Expiration
|
|
table.timestamp('expires_at');
|
|
table.boolean('is_active').defaultTo(true);
|
|
|
|
table.timestamp('created_at').defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at').defaultTo(knex.fn.now());
|
|
|
|
table.index('key_prefix');
|
|
table.index('user_id');
|
|
table.index('is_active');
|
|
});
|
|
|
|
// Schema Layouts
|
|
await knex.schema.createTable('schema_layouts', (table) => {
|
|
table.increments('id').primary();
|
|
table.integer('space_id').unsigned().notNullable().unique()
|
|
.references('id').inTable('spaces').onDelete('CASCADE');
|
|
table.text('layout').notNullable();
|
|
|
|
table.timestamp('created_at').defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at').defaultTo(knex.fn.now());
|
|
});
|
|
}
|
|
|
|
export async function down(knex) {
|
|
await knex.schema.dropTableIfExists('schema_layouts');
|
|
await knex.schema.dropTableIfExists('api_keys');
|
|
await knex.schema.dropTableIfExists('system_settings');
|
|
await knex.schema.dropTableIfExists('audit_log');
|
|
}
|