81 lines
12 KiB
Plaintext
81 lines
12 KiB
Plaintext
# Data Integrity Analysis - 20251123-124312
|
|
== Soft delete patterns ==
|
|
No soft delete pattern found
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:131: deleted_at TIMESTAMP WITH TIME ZONE, -- Soft delete
|
|
|
|
== Audit trail columns ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:26:-- This is an audit trail for when the initial superuser status is transferred
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.down.sql:29:DROP INDEX IF EXISTS idx_payment_audit_created;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.down.sql:30:DROP INDEX IF EXISTS idx_payment_audit_action;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.down.sql:31:DROP INDEX IF EXISTS idx_payment_audit_invoice;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.down.sql:32:DROP INDEX IF EXISTS idx_payment_audit_payment;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.down.sql:58:DROP TABLE IF EXISTS payment_audit_log;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:212:-- Payment audit log (comprehensive transaction history)
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:213:CREATE TABLE IF NOT EXISTS payment_audit_log (
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:282: created_by INTEGER REFERENCES users(id),
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:374:-- Historical rates for audit
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:462:CREATE INDEX IF NOT EXISTS idx_payment_audit_payment ON payment_audit_log(payment_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:463:CREATE INDEX IF NOT EXISTS idx_payment_audit_invoice ON payment_audit_log(invoice_id);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:464:CREATE INDEX IF NOT EXISTS idx_payment_audit_action ON payment_audit_log(action);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:465:CREATE INDEX IF NOT EXISTS idx_payment_audit_created ON payment_audit_log(created_at DESC);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:599:COMMENT ON TABLE payment_audit_log IS 'Comprehensive audit trail for all payment activities';
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.down.sql:22:-- Drop audit log table
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.down.sql:23:DROP TABLE IF EXISTS audit_log;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.down.sql:26:DROP TYPE IF EXISTS audit_entity;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.down.sql:27:DROP TYPE IF EXISTS audit_action;
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.down.sql:31: DROP COLUMN IF EXISTS created_by,
|
|
/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:65:CREATE INDEX idx_work_orders_created_by ON work_orders(created_by);
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:3:-- plus audit logging for security and compliance.
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:39:-- Add audit columns to invoices
|
|
/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:47:-- Create indexes for invoice audit fields
|
|
/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: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 (
|
|
/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:69: entity_type audit_entity NOT NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:71: action audit_action NOT NULL,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/004_approval_workflow_and_audit.up.sql:82:-- Create indexes for audit log
|
|
/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);
|
|
|
|
== Cascading deletes (potential data loss) ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/007_refresh_tokens.up.sql:6: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/008_csrf_tokens.up.sql:6: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:33: network_id INTEGER NOT NULL REFERENCES payment_networks(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:72: network_id INTEGER NOT NULL REFERENCES payment_networks(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:142: gateway_id INTEGER NOT NULL REFERENCES payment_gateways(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:172: payment_id INTEGER NOT NULL REFERENCES payments(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:338: campaign_id INTEGER NOT NULL REFERENCES donation_campaigns(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql:415: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/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/009_messenger.up.sql:7: user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:21: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:36: owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:37: contact_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:55: owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:69: owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:70: blocked_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:101: conversation_id UUID NOT NULL REFERENCES messenger_conversations(conversation_id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:102: user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:119: conversation_id UUID NOT NULL REFERENCES messenger_conversations(conversation_id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:120: sender_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:143: message_id UUID NOT NULL REFERENCES messenger_messages(message_id) ON DELETE CASCADE,
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:144: 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: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: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: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: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,
|
|
|
|
== Check constraints ==
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/006_superuser_hierarchy.up.sql:34: CONSTRAINT valid_transfer CHECK (from_user_id != to_user_id)
|
|
/home/administrator/projects/coppertone.tech/backend/migrations/009_messenger.up.sql:78: CHECK (blocked_user_id IS NOT NULL OR blocked_peer_id IS NOT NULL OR blocked_messaging_id IS NOT NULL)
|