284313f908
Some checks are pending
Bidi Control Character Guard / bidi-control-guard (push) Waiting to run
Circular Dependency Check / Check for new circular dependencies (push) Waiting to run
Citus Migration Smoke / Combined migrations on single-node Citus (push) Waiting to run
E2E Fresh Install Tests / fresh-install-e2e (push) Waiting to run
ext-v2 guardrails / Run ext-v2 guard and ESLint (push) Waiting to run
Integration Tests / Check for relevant changes (push) Waiting to run
Integration Tests / ${{ (github.event_name == 'schedule' || github.event.inputs.suite == 'full') && 'Full integration suite' || 'Tier-1 integration subset' }} (push) Blocked by required conditions
Mobile checks / Mobile lint + typecheck (push) Waiting to run
Mobile checks / Mobile unit tests (push) Waiting to run
Mobile checks / Mobile dependency audit (report) (push) Waiting to run
Mobile checks / Mobile reproducibility checks (push) Waiting to run
Secrets guard (env backups) / Ensure no tracked env backup files (push) Waiting to run
Temporal Readiness / fast-readiness (push) Waiting to run
Temporal Readiness / docker-parity (push) Waiting to run
TypeScript Type Check / Nx affected typecheck (push) Waiting to run
Unit Tests / Skipped-test budget (push) Waiting to run
Unit Tests / Nx affected unit tests (push) Waiting to run
Unit Tests / Server unit coverage (informational) (push) Waiting to run
Validate Tenant Management Schema / Check for relevant changes (push) Waiting to run
Validate Tenant Management Schema / Validate Tenant Management Schema (push) Blocked by required conditions
EE Workflows Build Guard / ee-workflows-build-guard (push) Waiting to run
Excluded: .git, node_modules, secrets/, compose.env, assemblyscript tgz Source: /opt/alga-psa on psa.joliet.tech
1055 lines
53 KiB
JavaScript
1055 lines
53 KiB
JavaScript
/**
|
|
* ⚠️ SCHEMA RENAME NOTICE: Many tables in this file have been renamed.
|
|
* DO NOT use names from this file directly - check the rename migrations:
|
|
*
|
|
* - companies → clients (see 20251003000001_company_to_client_migration.cjs)
|
|
* - channels → boards (see 20250930000001_rename_channels_to_boards.cjs)
|
|
* - billing_plans → contract_lines (see 20251008000001_rename_billing_to_contracts.cjs)
|
|
*/
|
|
|
|
exports.up = async function(knex) {
|
|
await knex.schema.createTable('tenants', (table) => {
|
|
table.uuid('tenant').primary().defaultTo(knex.raw('gen_random_uuid()'));
|
|
table.text('company_name').notNullable();
|
|
table.text('phone_number');
|
|
table.text('email').notNullable();
|
|
table.text('industry');
|
|
table.timestamp('created_at', { useTz: true });
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.text('payment_platform_id');
|
|
table.text('payment_method_id');
|
|
table.text('auth_service_id');
|
|
table.text('plan');
|
|
});
|
|
|
|
await knex.schema.createTable('users', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('user_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('username').notNullable();
|
|
table.text('hashed_password').notNullable();
|
|
table.text('first_name');
|
|
table.text('last_name');
|
|
table.timestamp('date_of_birth');
|
|
table.text('role').notNullable();
|
|
table.text('email');
|
|
table.text('icon');
|
|
table.text('auth_method');
|
|
table.timestamp('created_at',{ useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.boolean('two_factor_enabled').defaultTo(false);
|
|
table.text('two_factor_secret');
|
|
table.boolean('is_google_user').defaultTo(false);
|
|
table.bigInteger('rate');
|
|
table.jsonb('roles').defaultTo('[]');
|
|
table.primary(['tenant', 'user_id', 'email']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.unique(['tenant', 'user_id']);
|
|
});
|
|
|
|
await knex.schema.createTable('sessions', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('session_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.text('token').notNullable();
|
|
table.timestamp('created_at', { useTz: true });
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'session_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.raw('CREATE INDEX "session_user_id_index" ON "sessions"("user_id")');
|
|
|
|
await knex.schema.createTable('companies', (table) => { // ⚠️ RENAMED to 'clients' (see 20251003000001_company_to_client_migration.cjs)
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('company_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('company_name').notNullable();
|
|
table.text('phone_no');
|
|
table.text('url');
|
|
table.text('address');
|
|
table.text('status');
|
|
table.text('type');
|
|
table.jsonb('properties');
|
|
table.text('billing_type');
|
|
table.text('payment_terms');
|
|
table.text('billing_cycle');
|
|
table.bigInteger('credit_limit');
|
|
table.text('preferred_payment_method');
|
|
table.boolean('auto_invoice').defaultTo(false);
|
|
table.text('invoice_delivery_method');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'company_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
// ⚠️ RENAMED: 'companies' → 'clients' (see 20251003000001_company_to_client_migration.cjs)
|
|
|
|
await knex.schema.createTable('contacts', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('contact_name_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('full_name');
|
|
table.uuid('company_id');
|
|
table.text('phone_number');
|
|
table.text('email');
|
|
table.text('role');
|
|
table.boolean('approver');
|
|
table.timestamp('date_of_birth');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'contact_name_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
});
|
|
|
|
await knex.schema.createTable('statuses', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('status_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('status_name').notNullable();
|
|
table.text('status_type').notNullable().checkIn(['project', 'ticket', 'project_task']);
|
|
table.integer('order_number').notNullable();
|
|
table.uuid('created_by').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'status_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'created_by']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('channels', (table) => { // ⚠️ RENAMED to 'boards' (see 20250930000001_rename_channels_to_boards.cjs)
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('channel_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('channel_name').notNullable();
|
|
table.boolean('display_contact_name_id').defaultTo(true);
|
|
table.boolean('display_priority').defaultTo(true);
|
|
table.boolean('display_severity').defaultTo(true);
|
|
table.boolean('display_urgency').defaultTo(true);
|
|
table.boolean('display_impact').defaultTo(true);
|
|
table.boolean('display_category').defaultTo(true);
|
|
table.boolean('display_subcategory').defaultTo(true);
|
|
table.boolean('display_assigned_to').defaultTo(true);
|
|
table.boolean('display_status').defaultTo(true);
|
|
table.boolean('display_due_date').defaultTo(true);
|
|
table.primary(['tenant', 'channel_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
// ⚠️ RENAMED: 'channels' → 'boards' (see 20250930000001_rename_channels_to_boards.cjs)
|
|
|
|
// `categories` is the TICKET categories table (NOT `ticket_categories`, which
|
|
// does not exist). Note: no description/is_active/updated_* columns, and
|
|
// display_order is NOT NULL — inserts must supply it.
|
|
await knex.schema.createTable('categories', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('category_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('category_name').notNullable();
|
|
table.uuid('parent_category');
|
|
table.uuid('channel_id').notNullable();
|
|
table.uuid('created_by').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'category_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'created_by']).references(['tenant', 'user_id']).inTable('users');
|
|
table.foreign(['tenant', 'channel_id']).references(['tenant', 'channel_id']).inTable('channels');
|
|
table.foreign(['tenant', 'parent_category']).references(['tenant', 'category_id']).inTable('categories');
|
|
});
|
|
|
|
await knex.schema.createTable('priorities', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('priority_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('priority_name').notNullable();
|
|
table.uuid('created_by').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'priority_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'created_by']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('severities', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('severity_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('severity_name').notNullable();
|
|
table.uuid('created_by').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'severity_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'created_by']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('urgencies', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('urgency_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('urgency_name').notNullable();
|
|
table.uuid('created_by').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'urgency_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'created_by']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('impacts', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('impact_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('impact_name').notNullable();
|
|
table.uuid('created_by').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'impact_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'created_by']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('next_number', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.text('entity_type').notNullable();
|
|
table.bigInteger('last_number').notNullable().defaultTo(0);
|
|
table.bigInteger('initial_value').notNullable().defaultTo(1000);
|
|
table.text('prefix').defaultTo('TIC');
|
|
table.primary(['tenant', 'entity_type']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.raw('CREATE INDEX idx_next_number_tenant_entity ON next_number (tenant, entity_type)');
|
|
|
|
await knex.schema.createTable('attribute_definitions', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.text('attribute_name').notNullable();
|
|
table.text('description');
|
|
table.text('type').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'attribute_name']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.raw(`
|
|
CREATE OR REPLACE FUNCTION user_has_role(user_roles JSONB, role_name TEXT)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (SELECT 1 FROM jsonb_array_elements(user_roles) AS role WHERE role->>'role_name' = role_name);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
`);
|
|
|
|
await knex.schema.raw(`
|
|
CREATE OR REPLACE FUNCTION user_has_permission(user_roles JSONB, resource TEXT, action TEXT)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_array_elements(user_roles) AS role,
|
|
jsonb_array_elements(role->'permissions') AS permission
|
|
WHERE permission->>'resource' = resource AND permission->>'action' = action
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
`);
|
|
|
|
await knex.schema.createTable('tickets', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('ticket_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('ticket_number').notNullable();
|
|
table.text('title');
|
|
table.text('url');
|
|
table.uuid('company_id');
|
|
table.uuid('contact_name_id');
|
|
table.uuid('status_id');
|
|
table.uuid('channel_id');
|
|
table.uuid('category_id');
|
|
table.uuid('subcategory_id');
|
|
table.uuid('priority_id');
|
|
table.uuid('severity_id');
|
|
table.uuid('urgency_id');
|
|
table.uuid('impact_id');
|
|
table.uuid('entered_by');
|
|
table.uuid('updated_by');
|
|
table.uuid('assigned_to');
|
|
table.uuid('closed_by');
|
|
table.timestamp('entered_at', { useTz: true });
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('closed_at', { useTz: true });
|
|
table.boolean('is_closed');
|
|
table.jsonb('attributes');
|
|
table.primary(['tenant', 'ticket_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
table.foreign(['tenant', 'contact_name_id']).references(['tenant', 'contact_name_id']).inTable('contacts');
|
|
table.foreign(['tenant', 'channel_id']).references(['tenant', 'channel_id']).inTable('channels');
|
|
table.foreign(['tenant', 'status_id']).references(['tenant', 'status_id']).inTable('statuses');
|
|
table.foreign(['tenant', 'category_id']).references(['tenant', 'category_id']).inTable('categories');
|
|
table.foreign(['tenant', 'subcategory_id']).references(['tenant', 'category_id']).inTable('categories');
|
|
table.foreign(['tenant', 'priority_id']).references(['tenant', 'priority_id']).inTable('priorities');
|
|
table.foreign(['tenant', 'severity_id']).references(['tenant', 'severity_id']).inTable('severities');
|
|
table.foreign(['tenant', 'urgency_id']).references(['tenant', 'urgency_id']).inTable('urgencies');
|
|
table.foreign(['tenant', 'impact_id']).references(['tenant', 'impact_id']).inTable('impacts');
|
|
table.unique(['tenant', 'ticket_id', 'assigned_to']);
|
|
table.unique(['tenant', 'ticket_number']);
|
|
});
|
|
|
|
await knex.schema.createTable('comments', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('comment_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('ticket_id').notNullable();
|
|
table.uuid('user_id');
|
|
table.uuid('contact_name_id');
|
|
table.text('note').notNullable();
|
|
table.boolean('is_internal').notNullable();
|
|
table.boolean('is_resolution').notNullable();
|
|
table.boolean('is_initial_description').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'comment_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'ticket_id']).references(['tenant', 'ticket_id']).inTable('tickets');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('schedules', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('schedule_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('ticket_id').notNullable();
|
|
table.uuid('user_id');
|
|
table.uuid('contact_name_id');
|
|
table.uuid('company_id');
|
|
table.text('status').notNullable();
|
|
table.timestamp('scheduled_start', { useTz: true });
|
|
table.timestamp('scheduled_end', { useTz: true });
|
|
table.timestamp('actual_start', { useTz: true });
|
|
table.timestamp('actual_end', { useTz: true });
|
|
table.integer('duration_minutes');
|
|
table.text('description');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'schedule_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'ticket_id']).references(['tenant', 'ticket_id']).inTable('tickets');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
table.foreign(['tenant', 'contact_name_id']).references(['tenant', 'contact_name_id']).inTable('contacts');
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
});
|
|
|
|
await knex.schema.createTable('document_types', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('type_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('type_name').notNullable();
|
|
table.text('icon');
|
|
table.primary(['tenant', 'type_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.createTable('documents', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('document_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('document_name').notNullable();
|
|
table.uuid('type_id').notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.uuid('contact_name_id');
|
|
table.uuid('company_id');
|
|
table.uuid('ticket_id');
|
|
table.uuid('schedule_id');
|
|
table.increments('order_number').notNullable();
|
|
table.uuid('created_by').notNullable();
|
|
table.uuid('edited_by');
|
|
table.timestamp('entered_at', { useTz: true });
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.text('content');
|
|
table.primary(['tenant', 'document_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'type_id']).references(['tenant', 'type_id']).inTable('document_types');
|
|
table.foreign(['tenant', 'created_by']).references(['tenant', 'user_id']).inTable('users');
|
|
table.foreign(['tenant', 'contact_name_id']).references(['tenant', 'contact_name_id']).inTable('contacts');
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
});
|
|
|
|
await knex.schema.createTable('tags', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('tag_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('channel_id');
|
|
table.text('tag_text').notNullable();
|
|
table.uuid('tagged_id').notNullable();
|
|
table.text('tagged_type').notNullable();
|
|
table.primary(['tenant', 'tag_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'channel_id']).references(['tenant', 'channel_id']).inTable('channels');
|
|
});
|
|
|
|
await knex.schema.createTable('interaction_types', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('type_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('type_name').notNullable();
|
|
table.text('icon');
|
|
table.primary(['tenant', 'type_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.createTable('interactions', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('interaction_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('type_id').notNullable();
|
|
table.uuid('contact_name_id');
|
|
table.uuid('company_id');
|
|
table.uuid('user_id').notNullable();
|
|
table.uuid('ticket_id');
|
|
table.text('description');
|
|
table.timestamp('interaction_date', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.integer('duration');
|
|
table.primary(['tenant', 'interaction_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'type_id']).references(['tenant', 'type_id']).inTable('interaction_types');
|
|
table.foreign(['tenant', 'contact_name_id']).references(['tenant', 'contact_name_id']).inTable('contacts');
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
table.foreign(['tenant', 'ticket_id']).references(['tenant', 'ticket_id']).inTable('tickets');
|
|
});
|
|
|
|
await knex.schema.createTable('service_categories', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('category_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('category_name').notNullable();
|
|
table.text('description');
|
|
table.primary(['tenant', 'category_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.createTable('service_catalog', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('service_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('service_name').notNullable();
|
|
table.text('description');
|
|
table.text('service_type').notNullable();
|
|
table.bigInteger('default_rate');
|
|
table.text('unit_of_measure');
|
|
table.uuid('category_id');
|
|
table.primary(['tenant', 'service_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'category_id']).references(['tenant', 'category_id']).inTable('service_categories');
|
|
});
|
|
|
|
await knex.schema.createTable('billing_plans', (table) => { // ⚠️ RENAMED to 'contract_lines' (see 20251008000001_rename_billing_to_contracts.cjs)
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('plan_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('plan_name').notNullable();
|
|
table.text('description');
|
|
table.text('billing_frequency').notNullable();
|
|
table.boolean('is_custom').defaultTo(false);
|
|
table.text('plan_type').checkIn(['Fixed', 'Hourly', 'Usage', 'Bucket']);
|
|
table.primary(['tenant', 'plan_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
// ⚠️ RENAMED: 'billing_plans' → 'contract_lines' (see 20251008000001_rename_billing_to_contracts.cjs)
|
|
|
|
await knex.schema.createTable('bucket_plans', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('bucket_plan_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('plan_id').notNullable();
|
|
table.integer('total_hours').notNullable();
|
|
table.text('billing_period').notNullable();
|
|
table.bigInteger('overage_rate').notNullable();
|
|
table.primary(['tenant', 'bucket_plan_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'plan_id']).references(['tenant', 'plan_id']).inTable('billing_plans');
|
|
});
|
|
|
|
await knex.schema.createTable('bucket_usage', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('usage_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('bucket_plan_id').notNullable();
|
|
table.uuid('company_id').notNullable();
|
|
table.timestamp('period_start').notNullable();
|
|
table.timestamp('period_end').notNullable();
|
|
table.bigInteger('hours_used').notNullable();
|
|
table.bigInteger('overage_hours').notNullable();
|
|
table.primary(['tenant', 'usage_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'bucket_plan_id']).references(['tenant', 'bucket_plan_id']).inTable('bucket_plans');
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
});
|
|
|
|
await knex.schema.createTable('plan_services', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('plan_id').notNullable();
|
|
table.uuid('service_id').notNullable();
|
|
table.integer('quantity');
|
|
table.bigInteger('custom_rate');
|
|
table.primary(['tenant', 'plan_id', 'service_id']);
|
|
table.foreign(['tenant', 'plan_id']).references(['tenant', 'plan_id']).inTable('billing_plans');
|
|
table.foreign(['tenant', 'service_id']).references(['tenant', 'service_id']).inTable('service_catalog');
|
|
});
|
|
|
|
await knex.schema.createTable('client_billing', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('billing_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('company_id').notNullable();
|
|
table.uuid('plan_id').notNullable();
|
|
table.uuid('service_category');
|
|
table.boolean('is_active').defaultTo(true);
|
|
table.timestamp('start_date').notNullable();
|
|
table.timestamp('end_date');
|
|
table.primary(['tenant', 'billing_id']);
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
table.foreign(['tenant', 'plan_id']).references(['tenant', 'plan_id']).inTable('billing_plans');
|
|
table.foreign(['tenant', 'service_category']).references(['tenant', 'category_id']).inTable('service_categories');
|
|
});
|
|
|
|
await knex.schema.createTable('projects', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('project_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('company_id').notNullable();
|
|
table.text('project_name').notNullable();
|
|
table.text('description');
|
|
table.timestamp('start_date');
|
|
table.timestamp('end_date');
|
|
table.uuid('status').notNullable();
|
|
table.text('wbs_code').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'project_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
table.unique(['tenant', 'wbs_code']);
|
|
});
|
|
|
|
await knex.schema.createTable('time_period_types', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('type_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('type_name').notNullable();
|
|
table.text('description');
|
|
table.primary(['tenant', 'type_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.createTable('time_periods', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('period_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('type_id').notNullable();
|
|
table.timestamp('start_date', { useTz: true }).notNullable();
|
|
table.timestamp('end_date', { useTz: true }).notNullable();
|
|
table.boolean('is_closed').defaultTo(false);
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'period_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'type_id']).references(['tenant', 'type_id']).inTable('time_period_types');
|
|
});
|
|
|
|
await knex.schema.createTable('tenant_time_period_settings', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('type_id').notNullable();
|
|
table.integer('start_day');
|
|
table.integer('start_month');
|
|
table.integer('frequency');
|
|
table.text('frequency_unit').checkIn(['day', 'week', 'month', 'year']);
|
|
table.boolean('is_active').defaultTo(true);
|
|
table.timestamp('effective_from', { useTz: true }).notNullable();
|
|
table.timestamp('effective_to', { useTz: true });
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'type_id', 'effective_from']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'type_id']).references(['tenant', 'type_id']).inTable('time_period_types');
|
|
});
|
|
|
|
await knex.schema.raw(`CREATE TYPE ApprovalStatus AS ENUM ('DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED', 'CHANGES_REQUESTED')`);
|
|
|
|
await knex.schema.createTable('time_sheets', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.uuid('period_id').notNullable();
|
|
table.specificType('approval_status', 'ApprovalStatus').defaultTo('DRAFT');
|
|
table.timestamp('submitted_at', { useTz: true });
|
|
table.timestamp('approved_at', { useTz: true });
|
|
table.uuid('approved_by');
|
|
table.primary(['tenant', 'id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
table.foreign(['tenant', 'period_id']).references(['tenant', 'period_id']).inTable('time_periods');
|
|
table.foreign(['tenant', 'approved_by']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('time_entries', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('entry_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.timestamp('start_time', { useTz: true }).notNullable();
|
|
table.timestamp('end_time', { useTz: true }).notNullable();
|
|
table.text('notes');
|
|
table.uuid('work_item_id');
|
|
table.integer('billable_duration');
|
|
table.text('work_item_type');
|
|
table.specificType('approval_status', 'ApprovalStatus').defaultTo('DRAFT');
|
|
table.uuid('time_sheet_id');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'entry_id']);
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
table.foreign(['tenant', 'time_sheet_id']).references(['tenant', 'id']).inTable('time_sheets');
|
|
});
|
|
|
|
await knex.schema.createTable('usage_tracking', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('usage_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('company_id').notNullable();
|
|
table.uuid('service_id').notNullable();
|
|
table.timestamp('usage_date', { useTz: true }).notNullable();
|
|
table.bigInteger('quantity').notNullable();
|
|
table.primary(['tenant', 'usage_id']);
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
table.foreign(['tenant', 'service_id']).references(['tenant', 'service_id']).inTable('service_catalog');
|
|
});
|
|
|
|
await knex.schema.createTable('invoices', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('invoice_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('company_id').notNullable();
|
|
table.text('invoice_number').notNullable();
|
|
table.timestamp('invoice_date', { useTz: true }).notNullable();
|
|
table.timestamp('due_date', { useTz: true }).notNullable();
|
|
table.bigInteger('total_amount').notNullable();
|
|
table.text('status').notNullable();
|
|
table.uuid('template_id');
|
|
table.jsonb('custom_fields');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'invoice_id']);
|
|
table.foreign(['tenant', 'company_id']).references(['tenant', 'company_id']).inTable('companies');
|
|
});
|
|
|
|
await knex.schema.createTable('invoice_items', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('item_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('invoice_id').notNullable();
|
|
table.uuid('service_id').notNullable();
|
|
table.text('description').notNullable();
|
|
table.bigInteger('quantity').notNullable();
|
|
table.bigInteger('unit_price').notNullable();
|
|
table.bigInteger('total_price').notNullable();
|
|
table.primary(['tenant', 'item_id']);
|
|
table.foreign(['tenant', 'invoice_id']).references(['tenant', 'invoice_id']).inTable('invoices');
|
|
table.foreign(['tenant', 'service_id']).references(['tenant', 'service_id']).inTable('service_catalog');
|
|
});
|
|
|
|
await knex.schema.createTable('project_phases', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('phase_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('project_id').notNullable();
|
|
table.text('phase_name').notNullable();
|
|
table.text('description');
|
|
table.timestamp('start_date', { useTz: true });
|
|
table.timestamp('end_date', { useTz: true });
|
|
table.text('status').notNullable();
|
|
table.integer('order_number').notNullable();
|
|
table.text('wbs_code').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'phase_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'project_id']).references(['tenant', 'project_id']).inTable('projects');
|
|
table.unique(['tenant', 'project_id', 'wbs_code']);
|
|
});
|
|
|
|
await knex.schema.createTable('project_tasks', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('task_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('phase_id').notNullable();
|
|
table.text('task_name').notNullable();
|
|
table.text('description');
|
|
table.uuid('assigned_to');
|
|
table.bigInteger('estimated_hours');
|
|
table.timestamp('due_date', { useTz: true });
|
|
table.bigInteger('actual_hours');
|
|
table.uuid('status_id');
|
|
table.text('wbs_code').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'task_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'phase_id']).references(['tenant', 'phase_id']).inTable('project_phases');
|
|
table.foreign(['tenant', 'assigned_to']).references(['tenant', 'user_id']).inTable('users');
|
|
table.unique(['tenant', 'phase_id', 'wbs_code']);
|
|
table.foreign(['tenant', 'status_id']).references(['tenant', 'status_id']).inTable('statuses');
|
|
});
|
|
|
|
await knex.schema.createTable('project_ticket_links', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('link_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('project_id').notNullable();
|
|
table.uuid('phase_id');
|
|
table.uuid('task_id');
|
|
table.uuid('ticket_id').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'link_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'project_id']).references(['tenant', 'project_id']).inTable('projects');
|
|
table.foreign(['tenant', 'phase_id']).references(['tenant', 'phase_id']).inTable('project_phases');
|
|
table.foreign(['tenant', 'task_id']).references(['tenant', 'task_id']).inTable('project_tasks');
|
|
table.foreign(['tenant', 'ticket_id']).references(['tenant', 'ticket_id']).inTable('tickets');
|
|
});
|
|
await knex.schema.createTable('schedule_entries', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('entry_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('title').notNullable();
|
|
table.uuid('work_item_id').notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.timestamp('scheduled_start', { useTz: true }).notNullable();
|
|
table.timestamp('scheduled_end', { useTz: true }).notNullable();
|
|
table.text('status').notNullable();
|
|
table.text('notes');
|
|
table.jsonb('recurrence_pattern');
|
|
table.text('work_item_type').notNullable().checkIn(['project_task', 'ticket']);
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'entry_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('resources', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('resource_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.jsonb('availability');
|
|
table.specificType('skills', 'TEXT[]');
|
|
table.integer('max_daily_capacity');
|
|
table.integer('max_weekly_capacity');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'resource_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('schedule_conflicts', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('conflict_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('entry_id_1').notNullable();
|
|
table.uuid('entry_id_2').notNullable();
|
|
table.text('conflict_type').notNullable();
|
|
table.boolean('resolved').defaultTo(false);
|
|
table.text('resolution_notes');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'conflict_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'entry_id_1']).references(['tenant', 'entry_id']).inTable('schedule_entries');
|
|
table.foreign(['tenant', 'entry_id_2']).references(['tenant', 'entry_id']).inTable('schedule_entries');
|
|
});
|
|
|
|
await knex.schema.createTable('teams', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('team_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('team_name').notNullable();
|
|
table.uuid('manager_id').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'team_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'manager_id']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('team_members', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('team_id').notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'team_id', 'user_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'team_id']).references(['tenant', 'team_id']).inTable('teams');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('approval_levels', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('name').notNullable();
|
|
table.integer('order_num').notNullable();
|
|
table.primary(['tenant', 'id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.createTable('approval_thresholds', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('type').checkIn(['OVERTIME', 'HIGH_VALUE']);
|
|
table.bigInteger('threshold').notNullable();
|
|
table.uuid('approval_level_id').notNullable();
|
|
table.primary(['tenant', 'id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'approval_level_id']).references(['tenant', 'id']).inTable('approval_levels');
|
|
});
|
|
|
|
await knex.raw(`
|
|
CREATE OR REPLACE FUNCTION generate_next_number(p_tenant_id UUID, p_entity_type TEXT)
|
|
RETURNS TEXT AS $$
|
|
DECLARE
|
|
new_number BIGINT;
|
|
number_prefix TEXT;
|
|
initial_val BIGINT;
|
|
formatted_number TEXT;
|
|
BEGIN
|
|
PERFORM pg_advisory_xact_lock(hashtext(p_tenant_id::text || p_entity_type));
|
|
|
|
INSERT INTO next_number (tenant, entity_type)
|
|
VALUES (p_tenant_id, p_entity_type)
|
|
ON CONFLICT (tenant, entity_type) DO UPDATE
|
|
SET last_number =
|
|
CASE
|
|
WHEN next_number.last_number = 0 THEN next_number.initial_value
|
|
ELSE next_number.last_number + 1
|
|
END
|
|
RETURNING last_number, initial_value, prefix INTO new_number, initial_val, number_prefix;
|
|
|
|
IF new_number = initial_val AND new_number != 1 THEN
|
|
ELSE
|
|
UPDATE next_number
|
|
SET last_number = new_number
|
|
WHERE tenant = p_tenant_id AND entity_type = p_entity_type;
|
|
END IF;
|
|
|
|
IF number_prefix IS NOT NULL THEN
|
|
formatted_number := number_prefix || new_number::TEXT;
|
|
ELSE
|
|
formatted_number := new_number::TEXT;
|
|
END IF;
|
|
|
|
RETURN formatted_number;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
`);
|
|
|
|
await knex.raw(`
|
|
CREATE OR REPLACE FUNCTION set_ticket_number()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.ticket_number IS NULL OR NEW.ticket_number = '' THEN
|
|
NEW.ticket_number := generate_next_number(NEW.tenant, 'TICKET');
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
`);
|
|
|
|
await knex.raw(`
|
|
CREATE TRIGGER trigger_set_ticket_number
|
|
BEFORE INSERT ON tickets
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_ticket_number();
|
|
`);
|
|
|
|
await knex.raw(`
|
|
CREATE OR REPLACE FUNCTION get_current_tenant_id() RETURNS UUID AS $$
|
|
DECLARE
|
|
tenant_id UUID;
|
|
BEGIN
|
|
BEGIN
|
|
tenant_id := current_setting('app.current_tenant')::uuid;
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RAISE EXCEPTION 'Invalid tenant ID';
|
|
END;
|
|
RETURN tenant_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
`);
|
|
|
|
await knex.schema.createTable('roles', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('role_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('role_name').notNullable();
|
|
table.text('description');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'role_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.createTable('user_roles', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.uuid('role_id').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'user_id', 'role_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
table.foreign(['tenant', 'role_id']).references(['tenant', 'role_id']).inTable('roles');
|
|
});
|
|
|
|
await knex.schema.createTable('permissions', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('permission_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('resource').notNullable();
|
|
table.text('action').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'permission_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.createTable('role_permissions', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('role_id').notNullable();
|
|
table.uuid('permission_id').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'role_id', 'permission_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'role_id']).references(['tenant', 'role_id']).inTable('roles');
|
|
table.foreign(['tenant', 'permission_id']).references(['tenant', 'permission_id']).inTable('permissions');
|
|
});
|
|
|
|
await knex.schema.createTable('policies', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('policy_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('policy_name').notNullable();
|
|
table.text('resource').notNullable();
|
|
table.text('action').notNullable();
|
|
table.jsonb('conditions').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'policy_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
});
|
|
|
|
await knex.schema.createTable('ticket_resources', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('assignment_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('ticket_id').notNullable();
|
|
table.uuid('assigned_to').notNullable();
|
|
table.uuid('additional_user_id');
|
|
table.text('role');
|
|
table.timestamp('assigned_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'assignment_id']);
|
|
table.foreign('tenant').references('tenants.tenant');
|
|
table.foreign(['tenant', 'ticket_id', 'assigned_to']).references(['tenant', 'ticket_id', 'assigned_to']).inTable('tickets');
|
|
table.foreign(['tenant', 'assigned_to']).references(['tenant', 'user_id']).inTable('users');
|
|
table.foreign(['tenant', 'additional_user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
table.check('assigned_to != additional_user_id');
|
|
});
|
|
|
|
await knex.schema.createTable('invoice_templates', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('template_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('name').notNullable();
|
|
table.integer('version').notNullable();
|
|
table.text('dsl').notNullable();
|
|
table.boolean('is_default').defaultTo(false);
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'template_id']);
|
|
});
|
|
|
|
await knex.schema.createTable('template_sections', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('section_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('template_id').notNullable();
|
|
table.text('section_type').notNullable().checkIn(['header', 'list', 'footer']);
|
|
table.text('name').notNullable();
|
|
table.integer('grid_rows').notNullable();
|
|
table.integer('grid_columns').notNullable();
|
|
table.integer('order_number').notNullable();
|
|
table.primary(['tenant', 'section_id']);
|
|
table.foreign(['tenant', 'template_id']).references(['tenant', 'template_id']).inTable('invoice_templates');
|
|
});
|
|
|
|
await knex.schema.createTable('layout_blocks', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('block_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('section_id').notNullable();
|
|
table.text('type').notNullable();
|
|
table.text('content');
|
|
table.integer('grid_column').notNullable();
|
|
table.integer('grid_row').notNullable();
|
|
table.integer('grid_column_span').notNullable();
|
|
table.integer('grid_row_span').notNullable();
|
|
table.jsonb('styles');
|
|
table.primary(['tenant', 'block_id']);
|
|
table.foreign(['tenant', 'section_id']).references(['tenant', 'section_id']).inTable('template_sections');
|
|
});
|
|
|
|
await knex.schema.createTable('custom_fields', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('field_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.text('name').notNullable();
|
|
table.text('type').notNullable();
|
|
table.jsonb('default_value');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'field_id']);
|
|
});
|
|
|
|
await knex.schema.createTable('invoice_annotations', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('annotation_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('invoice_id').notNullable();
|
|
table.uuid('user_id').notNullable();
|
|
table.text('content').notNullable();
|
|
table.boolean('is_internal').notNullable();
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'annotation_id']);
|
|
table.foreign(['tenant', 'invoice_id']).references(['tenant', 'invoice_id']).inTable('invoices');
|
|
table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users');
|
|
});
|
|
|
|
await knex.schema.createTable('conditional_display_rules', (table) => {
|
|
table.uuid('tenant').notNullable();
|
|
table.uuid('rule_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable();
|
|
table.uuid('template_id').notNullable();
|
|
table.jsonb('condition').notNullable();
|
|
table.text('action').notNullable();
|
|
table.text('target').notNullable();
|
|
table.jsonb('format');
|
|
table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
|
|
table.primary(['tenant', 'rule_id']);
|
|
table.foreign(['tenant', 'template_id']).references(['tenant', 'template_id']).inTable('invoice_templates');
|
|
});
|
|
|
|
await knex.schema.raw('CREATE INDEX idx_ticket_resources_ticket_user ON ticket_resources (tenant, ticket_id, additional_user_id)');
|
|
|
|
|
|
const dbUserServer = process.env.DB_USER_SERVER;
|
|
const envDbNameServer = process.env.DB_NAME_SERVER;
|
|
|
|
if (dbUserServer) {
|
|
const currentDatabaseResult = await knex.raw('SELECT current_database()');
|
|
const currentDatabaseRow = Array.isArray(currentDatabaseResult.rows)
|
|
? currentDatabaseResult.rows[0]
|
|
: currentDatabaseResult[0];
|
|
const currentDatabaseName = currentDatabaseRow?.current_database;
|
|
if (!currentDatabaseName) {
|
|
throw new Error('Failed to resolve current database name when applying permissions');
|
|
}
|
|
let targetDbName = envDbNameServer && envDbNameServer !== 'default_db_name'
|
|
? envDbNameServer
|
|
: currentDatabaseName;
|
|
|
|
try {
|
|
const databaseCheck = await knex.raw('SELECT 1 FROM pg_database WHERE datname = ?', [targetDbName]);
|
|
const hasDatabase = Array.isArray(databaseCheck.rows)
|
|
? databaseCheck.rows.length > 0
|
|
: Array.isArray(databaseCheck) && databaseCheck.length > 0;
|
|
if (!hasDatabase) {
|
|
targetDbName = currentDatabaseName;
|
|
}
|
|
} catch (error) {
|
|
targetDbName = currentDatabaseName;
|
|
}
|
|
|
|
const escapedUser = dbUserServer.replace(/"/g, '""');
|
|
const escapedDbName = targetDbName.replace(/"/g, '""');
|
|
|
|
await knex.schema.raw(`
|
|
GRANT USAGE ON SCHEMA public TO "${escapedUser}";
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "${escapedUser}";
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "${escapedUser}";
|
|
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO "${escapedUser}";
|
|
GRANT ALL PRIVILEGES ON DATABASE "${escapedDbName}" TO "${escapedUser}";
|
|
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT ALL PRIVILEGES ON TABLES TO "${escapedUser}";
|
|
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT ALL PRIVILEGES ON SEQUENCES TO "${escapedUser}";
|
|
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT ALL PRIVILEGES ON FUNCTIONS TO "${escapedUser}";
|
|
`);
|
|
}
|
|
}
|
|
|
|
exports.down = async function(knex) {
|
|
throw new Error('Cannot rollback the initial migration.');
|
|
};
|