Soft Deletes and Audit Trails

---

Why Not Just DELETE?

Hard deletion (removing rows) has consequences:

• Data is gone: No recovery without backups
• References break: Foreign key constraints or dangling references
• No history: Can't see what was deleted or when
• Compliance issues: Regulations may require retention

Soft deletes and audit trails address these concerns.

---

Soft Delete Pattern

Instead of deleting rows, mark them as deleted:

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id),
  title text NOT NULL,
  content text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  deleted_at timestamptz  -- NULL = not deleted
);

Soft Delete RLS

-- Only show non-deleted posts
CREATE POLICY "View active posts"
  ON posts
  AS RESTRICTIVE
  FOR SELECT
  USING (deleted_at IS NULL);

-- Update policy (for soft delete)
CREATE POLICY "Owner can update/delete own posts"
  ON posts FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

Soft Delete Operation

// Instead of delete
await supabase
  .from('posts')
  .update({ deleted_at: new Date().toISOString() })
  .eq('id', postId)

// Or with RPC for cleaner API
await supabase.rpc('soft_delete_post', { post_id: postId })

CREATE FUNCTION soft_delete_post(post_id uuid)
RETURNS void AS $$
  UPDATE posts SET deleted_at = now() WHERE id = post_id AND user_id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER;

---

Restoring Soft-Deleted Data

// Restore a post
await supabase
  .from('posts')
  .update({ deleted_at: null })
  .eq('id', postId)

Admin View of Deleted Items

-- Admin policy to see all including deleted
CREATE POLICY "Admins see everything"
  ON posts FOR SELECT
  USING (is_admin());  -- Bypasses the restrictive policy

---

Automatic Cleanup (Optional)

Permanently delete old soft-deleted records:

-- Function to purge old deleted records
CREATE FUNCTION purge_old_deleted_records()
RETURNS void AS $$
BEGIN
  DELETE FROM posts
  WHERE deleted_at IS NOT NULL
  AND deleted_at < now() - interval '90 days';
END;
$$ LANGUAGE plpgsql;

-- Schedule with pg_cron (if available)
SELECT cron.schedule('purge-deleted', '0 0 * * *', 'SELECT purge_old_deleted_records()');

---

Audit Trail Pattern

Track all changes to important tables:

CREATE TABLE audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name text NOT NULL,
  record_id uuid NOT NULL,
  action text NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  old_data jsonb,
  new_data jsonb,
  changed_by uuid REFERENCES auth.users(id),
  changed_at timestamptz DEFAULT now() NOT NULL,
  ip_address inet,
  user_agent text
);

-- Index for querying
CREATE INDEX idx_audit_log_table_record ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_log_user ON audit_log(changed_by);
CREATE INDEX idx_audit_log_time ON audit_log(changed_at);

Generic Audit Trigger

CREATE FUNCTION audit_trigger_func()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (
    table_name,
    record_id,
    action,
    old_data,
    new_data,
    changed_by
  )
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END,
    auth.uid()
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply to tables
CREATE TRIGGER posts_audit
  AFTER INSERT OR UPDATE OR DELETE ON posts
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

CREATE TRIGGER profiles_audit
  AFTER INSERT OR UPDATE OR DELETE ON profiles
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

---

Querying Audit Logs

History of a Record

const { data: history } = await supabase
  .from('audit_log')
  .select('*')
  .eq('table_name', 'posts')
  .eq('record_id', postId)
  .order('changed_at', { ascending: false })

Changes by User

const { data: userActivity } = await supabase
  .from('audit_log')
  .select('*')
  .eq('changed_by', userId)
  .order('changed_at', { ascending: false })
  .limit(50)

Changes in Time Range

const { data: recentChanges } = await supabase
  .from('audit_log')
  .select('*')
  .gte('changed_at', startDate)
  .lte('changed_at', endDate)

---

Audit Log RLS

Audit logs need careful access control:

ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

-- Users can see audit for their own records
CREATE POLICY "Users see own record audits"
  ON audit_log FOR SELECT
  USING (changed_by = auth.uid());

-- Admins see all
CREATE POLICY "Admins see all audits"
  ON audit_log FOR SELECT
  USING (is_admin());

-- No one can modify audit logs (append-only)
-- No INSERT/UPDATE/DELETE policies for regular users

---

Change Tracking Columns

Simpler alternative for basic tracking:

CREATE TABLE posts (
  id uuid PRIMARY KEY,
  -- Content
  title text,
  content text,

  -- Tracking
  created_at timestamptz DEFAULT now() NOT NULL,
  created_by uuid REFERENCES auth.users(id),
  updated_at timestamptz DEFAULT now() NOT NULL,
  updated_by uuid,
  deleted_at timestamptz,
  deleted_by uuid
);

-- Auto-update tracking columns
CREATE FUNCTION update_tracking_columns()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  NEW.updated_by = auth.uid();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_tracking
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_tracking_columns();

---

Combining Patterns

Full-featured data management:

CREATE TABLE documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid REFERENCES organizations(id) NOT NULL,

  -- Content
  title text NOT NULL,
  content text,
  status text DEFAULT 'draft',

  -- Soft delete
  deleted_at timestamptz,
  deleted_by uuid,

  -- Versioning (simple)
  version integer DEFAULT 1,

  -- Tracking
  created_at timestamptz DEFAULT now(),
  created_by uuid REFERENCES auth.users(id),
  updated_at timestamptz DEFAULT now(),
  updated_by uuid
);

-- RLS combines tenant isolation and soft delete
CREATE POLICY "Org members see active documents"
  ON documents FOR SELECT
  USING (
    is_org_member(organization_id)
    AND deleted_at IS NULL
  );

---

Key Takeaways

1. Soft delete preserves data: Mark as deleted instead of removing
2. RESTRICTIVE policies filter deleted: Automatic exclusion from queries
3. Audit logs track changes: Who changed what, when
4. Triggers automate logging: Consistent, reliable capture
5. Immutable audit logs: Append-only for integrity
6. Combine patterns: Soft delete + audit + tracking

---

Module Summary

In this module, you've learned:

• Schema design principles for Supabase
• The user profiles pattern for extending auth
• Multi-tenancy approaches for SaaS applications
• Soft deletes and audit trails for data integrity

Good schema design pays dividends throughout your application's lifecycle. Take the time to design well upfront.

---

Data deletion is often a business process, not just a technical operation. Soft deletes give you the flexibility to handle undelete requests, compliance requirements, and data recovery without restoring from backups.