# Data Integrity Analysis - 20251123-092506
== 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/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)