f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
55 lines
2 KiB
JavaScript
55 lines
2 KiB
JavaScript
// backend/database/migrations/knex/009_create_chat_system.js
|
|
// Chat System: Threads, Participants, Messages
|
|
export async function up(knex) {
|
|
// Chat Threads
|
|
await knex.schema.createTable('chat_threads', (table) => {
|
|
table.increments('id').primary();
|
|
table.string('thread_id', 255).unique().notNullable();
|
|
table.string('type', 50).defaultTo('direct');
|
|
table.string('name', 255);
|
|
table.text('encrypted_with_keys').notNullable();
|
|
|
|
table.timestamp('created_at').defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at').defaultTo(knex.fn.now());
|
|
|
|
table.index('thread_id');
|
|
table.index('type');
|
|
});
|
|
|
|
// Chat Participants
|
|
await knex.schema.createTable('chat_participants', (table) => {
|
|
table.increments('id').primary();
|
|
table.integer('thread_id').unsigned().notNullable()
|
|
.references('id').inTable('chat_threads').onDelete('CASCADE');
|
|
table.integer('user_id').unsigned().notNullable()
|
|
.references('id').inTable('users').onDelete('CASCADE');
|
|
table.timestamp('joined_at').defaultTo(knex.fn.now());
|
|
|
|
table.unique(['thread_id', 'user_id']);
|
|
table.index('thread_id');
|
|
table.index('user_id');
|
|
});
|
|
|
|
// Chat Messages
|
|
await knex.schema.createTable('chat_messages', (table) => {
|
|
table.increments('id').primary();
|
|
table.integer('thread_id').unsigned().notNullable()
|
|
.references('id').inTable('chat_threads').onDelete('CASCADE');
|
|
table.integer('sender_id').unsigned().notNullable()
|
|
.references('id').inTable('users').onDelete('CASCADE');
|
|
table.text('content_encrypted').notNullable();
|
|
table.string('encryption_method', 50).defaultTo('user_key');
|
|
|
|
table.timestamp('created_at').defaultTo(knex.fn.now());
|
|
|
|
table.index('thread_id');
|
|
table.index('sender_id');
|
|
table.index('created_at');
|
|
});
|
|
}
|
|
|
|
export async function down(knex) {
|
|
await knex.schema.dropTableIfExists('chat_messages');
|
|
await knex.schema.dropTableIfExists('chat_participants');
|
|
await knex.schema.dropTableIfExists('chat_threads');
|
|
}
|