225 lines
35 KiB
Plaintext
225 lines
35 KiB
Plaintext
# Schema Design Analysis - 20251123-092506
|
|
== Tables Created ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:27:CREATE TABLE IF NOT EXISTS superuser_transfers (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/005_schema_separation.up.sql:55: 'CREATE TABLE IF NOT EXISTS %I.%I (LIKE public.%I INCLUDING ALL)',
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:5:CREATE TABLE IF NOT EXISTS invoices (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:38:CREATE TABLE IF NOT EXISTS payments (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:64:CREATE TABLE IF NOT EXISTS invoice_items (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:2:CREATE TABLE IF NOT EXISTS users (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:17:CREATE TABLE IF NOT EXISTS identities (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:39:CREATE TABLE IF NOT EXISTS user_roles (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:5:CREATE TABLE IF NOT EXISTS projects (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:27:CREATE TABLE IF NOT EXISTS tasks (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:50:CREATE TABLE IF NOT EXISTS work_orders (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:68:CREATE TABLE IF NOT EXISTS task_comments (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:67:CREATE TABLE IF NOT EXISTS audit_log (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:92:CREATE TABLE IF NOT EXISTS project_approval_comments (
|
|
|
|
== Foreign Key Constraints ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:29: from_user_id INTEGER NOT NULL REFERENCES users(id),
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:30: to_user_id INTEGER NOT NULL REFERENCES users(id),
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:8: project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:9: client_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:40: invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:66: invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:19: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:41: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:10: client_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:29: project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:33: assignee_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:52: project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:57: created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:70: task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:71: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:17: ADD COLUMN IF NOT EXISTS requested_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:18: ADD COLUMN IF NOT EXISTS approved_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:41: ADD COLUMN IF NOT EXISTS created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:42: ADD COLUMN IF NOT EXISTS approved_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:72: user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:94: project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:95: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
|
|
== Indexes Created ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:23:CREATE INDEX IF NOT EXISTS idx_users_initial_superuser ON users(is_initial_superuser) WHERE is_initial_superuser = true;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:38:CREATE INDEX IF NOT EXISTS idx_superuser_transfers_from ON superuser_transfers(from_user_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:39:CREATE INDEX IF NOT EXISTS idx_superuser_transfers_to ON superuser_transfers(to_user_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:24:CREATE INDEX idx_invoices_invoice_number ON invoices(invoice_number);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:25:CREATE INDEX idx_invoices_project_id ON invoices(project_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:26:CREATE INDEX idx_invoices_client_id ON invoices(client_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:27:CREATE INDEX idx_invoices_status ON invoices(status);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:28:CREATE INDEX idx_invoices_due_date ON invoices(due_date) WHERE due_date IS NOT NULL;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:29:CREATE INDEX idx_invoices_blockchain_tx ON invoices(blockchain_tx_hash) WHERE blockchain_tx_hash IS NOT NULL;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:57:CREATE INDEX idx_payments_invoice_id ON payments(invoice_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:58:CREATE INDEX idx_payments_transaction_id ON payments(transaction_id) WHERE transaction_id IS NOT NULL;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:59:CREATE INDEX idx_payments_blockchain_tx ON payments(blockchain_tx_hash) WHERE blockchain_tx_hash IS NOT NULL;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:60:CREATE INDEX idx_payments_status ON payments(status);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:61:CREATE INDEX idx_payments_payment_method ON payments(payment_method);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:77:CREATE INDEX idx_invoice_items_invoice_id ON invoice_items(invoice_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:11:CREATE INDEX idx_users_email ON users(email) WHERE email IS NOT NULL;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:31:CREATE INDEX idx_identities_user_id ON identities(user_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:32:CREATE INDEX idx_identities_type_identifier ON identities(type, identifier);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:33:CREATE INDEX idx_identities_primary_login ON identities(user_id, is_primary_login) WHERE is_primary_login = true;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:48:CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:19:CREATE INDEX idx_projects_client_id ON projects(client_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:20:CREATE INDEX idx_projects_status ON projects(status);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:21:CREATE INDEX idx_projects_ipfs_cid ON projects(ipfs_metadata_cid) WHERE ipfs_metadata_cid IS NOT NULL;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:44:CREATE INDEX idx_tasks_project_id ON tasks(project_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:45:CREATE INDEX idx_tasks_assignee_id ON tasks(assignee_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:46:CREATE INDEX idx_tasks_status ON tasks(status);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:47:CREATE INDEX idx_tasks_due_date ON tasks(due_date) WHERE due_date IS NOT NULL;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:63:CREATE INDEX idx_work_orders_project_id ON work_orders(project_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:64:CREATE INDEX idx_work_orders_order_number ON work_orders(order_number);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:65:CREATE INDEX idx_work_orders_created_by ON work_orders(created_by);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:78:CREATE INDEX idx_task_comments_task_id ON task_comments(task_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:79:CREATE INDEX idx_task_comments_user_id ON task_comments(user_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:28:CREATE INDEX IF NOT EXISTS idx_projects_requested_by ON projects(requested_by);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:29:CREATE INDEX IF NOT EXISTS idx_projects_approved_by ON projects(approved_by);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:30:CREATE INDEX IF NOT EXISTS idx_projects_approval_status ON projects(approval_status);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:48:CREATE INDEX IF NOT EXISTS idx_invoices_created_by ON invoices(created_by);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:49:CREATE INDEX IF NOT EXISTS idx_invoices_approved_by ON invoices(approved_by);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:50:CREATE INDEX IF NOT EXISTS idx_invoices_approval_status ON invoices(approval_status);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:83:CREATE INDEX idx_audit_log_entity ON audit_log(entity_type, entity_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:84:CREATE INDEX idx_audit_log_user ON audit_log(user_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:85:CREATE INDEX idx_audit_log_action ON audit_log(action);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:86:CREATE INDEX idx_audit_log_created_at ON audit_log(created_at);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:101:CREATE INDEX idx_project_approval_comments_project ON project_approval_comments(project_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:102:CREATE INDEX idx_project_approval_comments_user ON project_approval_comments(user_id);
|
|
|
|
== Missing NOT NULL constraints (nullable columns) ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:17:ALTER TABLE users ADD COLUMN IF NOT EXISTS is_initial_superuser BOOLEAN DEFAULT false;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:20:ALTER TABLE users ADD COLUMN IF NOT EXISTS is_protected BOOLEAN DEFAULT false;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:31: transferred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:32: reason TEXT,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:87: is_initial BOOLEAN;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:108: current_su_id INTEGER,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:109: new_su_id INTEGER,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:110: transfer_reason TEXT DEFAULT NULL
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:112:RETURNS BOOLEAN AS $$
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:114: is_current_initial BOOLEAN;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:115: new_user_exists BOOLEAN;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/005_schema_separation.up.sql:40:CREATE OR REPLACE FUNCTION clone_tables_to_schema(target_schema TEXT)
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/005_schema_separation.up.sql:44: sql_stmt TEXT;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/005_schema_separation.up.sql:89: sql_stmt TEXT;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/005_schema_separation.up.sql:116:DROP FUNCTION IF EXISTS clone_tables_to_schema(TEXT);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:8: project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:16: blockchain_tx_hash VARCHAR(255), -- For on-chain payment proof
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:17: ipfs_document_cid VARCHAR(255), -- CID for invoice PDF on IPFS
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:18: notes TEXT,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:19: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:20: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:45: transaction_id VARCHAR(255), -- Stripe payment intent ID, BTCPay invoice ID, or blockchain tx hash
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:46: blockchain_tx_hash VARCHAR(255), -- For crypto/blockchain payments
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:47: blockchain_network VARCHAR(50), -- e.g., 'ethereum', 'bitcoin', 'polygon'
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:48: payment_processor VARCHAR(50), -- e.g., 'stripe', 'btcpay', 'direct_blockchain'
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:51: processed_at TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:52: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:53: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:72: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:73: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:108: UPDATE invoices SET status = 'PAID', paid_date = CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:5: email VARCHAR(255) UNIQUE, -- Nullable for blockchain-only users
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:6: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:7: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:22: credential TEXT, -- Password hash, public key, or other credential data
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:23: is_primary_login BOOLEAN DEFAULT false,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:25: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:26: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:43: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:54: NEW.updated_at = CURRENT_TIMESTAMP;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.down.sql:8:DROP FUNCTION IF EXISTS can_approve_projects(INTEGER);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.down.sql:9:DROP FUNCTION IF EXISTS user_has_role(INTEGER, user_role);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.down.sql:11:DROP FUNCTION IF EXISTS transfer_initial_superuser(INTEGER, INTEGER, TEXT);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:8: description TEXT,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:10: client_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:11: ipfs_metadata_cid VARCHAR(255), -- CID for project metadata stored on IPFS
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:14: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:15: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:31: description TEXT,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:33: assignee_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:35: completed_at TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:36: priority INTEGER DEFAULT 0, -- 0 = normal, higher = more urgent
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:39: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:40: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:54: description TEXT,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:56: ipfs_document_cid VARCHAR(255), -- CID for work order document on IPFS
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:57: created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:58: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:59: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:73: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:74: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:17: ADD COLUMN IF NOT EXISTS requested_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:18: ADD COLUMN IF NOT EXISTS approved_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:20: ADD COLUMN IF NOT EXISTS approval_date TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:21: ADD COLUMN IF NOT EXISTS rejection_reason TEXT;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:41: ADD COLUMN IF NOT EXISTS created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:42: ADD COLUMN IF NOT EXISTS approved_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:44: ADD COLUMN IF NOT EXISTS approval_date TIMESTAMP,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:45: ADD COLUMN IF NOT EXISTS rejection_reason TEXT;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:72: user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:73: user_email VARCHAR(255), -- Denormalized for when user is deleted
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:74: user_roles TEXT[], -- Roles at time of action
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:78: user_agent TEXT,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:79: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:97: is_internal BOOLEAN DEFAULT false, -- Internal comments only visible to STAFF/ADMIN
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:98: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:143: NEW.approval_date := CURRENT_TIMESTAMP;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:145: NEW.approval_date := CURRENT_TIMESTAMP;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:165:CREATE OR REPLACE FUNCTION user_has_role(p_user_id INTEGER, p_role user_role)
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:166:RETURNS BOOLEAN AS $$
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:176:CREATE OR REPLACE FUNCTION can_approve_projects(p_user_id INTEGER)
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:177:RETURNS BOOLEAN AS $$
|
|
|
|
== Missing DEFAULT values ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:144: INSERT INTO user_roles (user_id, role, created_at)
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.down.sql:3:DROP TRIGGER IF EXISTS update_invoice_items_updated_at ON invoice_items;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.down.sql:4:DROP TRIGGER IF EXISTS update_payments_updated_at ON payments;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.down.sql:5:DROP TRIGGER IF EXISTS update_invoices_updated_at ON invoices;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:79:-- Create triggers for updated_at
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:80:CREATE TRIGGER update_invoices_updated_at BEFORE UPDATE ON invoices
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:81: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:83:CREATE TRIGGER update_payments_updated_at BEFORE UPDATE ON payments
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:84: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:86:CREATE TRIGGER update_invoice_items_updated_at BEFORE UPDATE ON invoice_items
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:87: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:50:-- Create function to update updated_at timestamp
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:51:CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:54: NEW.updated_at = CURRENT_TIMESTAMP;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:59:-- Create triggers for updated_at
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:60:CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:61: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:63:CREATE TRIGGER update_identities_updated_at BEFORE UPDATE ON identities
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:64: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.down.sql:2:DROP TRIGGER IF EXISTS update_identities_updated_at ON identities;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.down.sql:3:DROP TRIGGER IF EXISTS update_users_updated_at ON users;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.down.sql:6:DROP FUNCTION IF EXISTS update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.down.sql:2:DROP TRIGGER IF EXISTS update_task_comments_updated_at ON task_comments;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.down.sql:3:DROP TRIGGER IF EXISTS update_work_orders_updated_at ON work_orders;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.down.sql:4:DROP TRIGGER IF EXISTS update_tasks_updated_at ON tasks;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.down.sql:5:DROP TRIGGER IF EXISTS update_projects_updated_at ON projects;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:81:-- Create triggers for updated_at
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:82:CREATE TRIGGER update_projects_updated_at BEFORE UPDATE ON projects
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:83: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:85:CREATE TRIGGER update_tasks_updated_at BEFORE UPDATE ON tasks
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:86: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:88:CREATE TRIGGER update_work_orders_updated_at BEFORE UPDATE ON work_orders
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:89: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:91:CREATE TRIGGER update_task_comments_updated_at BEFORE UPDATE ON task_comments
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:92: FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:86:CREATE INDEX idx_audit_log_created_at ON audit_log(created_at);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:196: p.created_at,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:207:ORDER BY p.created_at ASC;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:217: p.created_at,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:221:ORDER BY p.created_at DESC;
|
|
|
|
== Enum Types ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:2:CREATE TYPE invoice_status AS ENUM ('DRAFT', 'SENT', 'PAID', 'PARTIALLY_PAID', 'OVERDUE', 'VOID', 'CANCELLED');
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:32:CREATE TYPE payment_method AS ENUM ('CREDIT_CARD', 'DEBIT_CARD', 'CRYPTO_BTC', 'CRYPTO_ETH', 'CRYPTO_OTHER', 'BLOCKCHAIN_TOKEN', 'BANK_TRANSFER', 'CASH', 'OTHER');
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/003_create_invoices_and_payments.up.sql:35:CREATE TYPE payment_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED');
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:14:CREATE TYPE identity_type AS ENUM ('email_password', 'blockchain_address', 'did');
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/001_create_users_and_identities.up.sql:36:CREATE TYPE user_role AS ENUM ('ADMIN', 'STAFF', 'CLIENT');
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:2:CREATE TYPE project_status AS ENUM ('PLANNING', 'IN_PROGRESS', 'COMPLETED', 'ON_HOLD', 'CANCELLED');
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/002_create_projects_and_tasks.up.sql:24:CREATE TYPE task_status AS ENUM ('TODO', 'IN_PROGRESS', 'BLOCKED', 'COMPLETED', 'CANCELLED');
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:9:CREATE TYPE approval_status AS ENUM ('PENDING', 'APPROVED', 'REJECTED');
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:56:CREATE TYPE audit_action AS ENUM (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:63:CREATE TYPE audit_entity AS ENUM (
|