45 lines
5.7 KiB
Plaintext
45 lines
5.7 KiB
Plaintext
# Data Integrity Analysis - 20251123-104210
|
|
== Soft delete patterns ==
|
|
No soft delete pattern found
|
|
|
|
== 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/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/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: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)
|