Skip to main content

Retention Automation: Rules and SQL Procedures

SQL triggers and procedures for automatic retention management and record destruction.

Retention Update Helper​

Whenever metadata changes effective_date or retention_category, recompute retain_until.

Trigger Function​

CREATE OR REPLACE FUNCTION recompute_retain_until()
RETURNS TRIGGER AS $$
DECLARE
  pol retention_policies;
  years INT;
BEGIN
  -- Lookup retention policy
  SELECT * INTO pol
  FROM retention_policies
  WHERE retention_category = NEW.retention_category;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Unknown retention_category: %', NEW.retention_category;
  END IF;

  -- Use the greater of default and minimum years
  years := GREATEST(pol.period_years_default, pol.min_years);

  -- Require effective_date for retention computation
  IF NEW.effective_date IS NULL THEN
    RAISE EXCEPTION 'effective_date required for retention computation';
  END IF;

  -- Set retention fields
  NEW.retention_period_y := years;
  NEW.retain_until := (NEW.effective_date + (years || ' years')::INTERVAL)::DATE;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Create Trigger​

CREATE TRIGGER trg_recompute_retain_until
BEFORE INSERT OR UPDATE OF effective_date, retention_category
ON document_metadata
FOR EACH ROW
EXECUTE FUNCTION recompute_retain_until();

Scheduled Expiry Job​

A daily job to identify and queue documents for destruction.

Destruction Queue Table​

CREATE TABLE destruction_queue (
  id              BIGSERIAL PRIMARY KEY,
  doc_id          UUID NOT NULL REFERENCES documents(doc_id),
  queued_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at    TIMESTAMPTZ,
  status          TEXT NOT NULL DEFAULT 'queued',   -- queued|processing|completed|failed
  reason          TEXT NOT NULL,                    -- "retention_expired"
  error_message   TEXT,
  processed_by    TEXT
);

CREATE INDEX idx_destruction_status ON destruction_queue(status);
CREATE INDEX idx_destruction_doc ON destruction_queue(doc_id);

Queue Eligible Documents​

-- Daily job: enqueue documents eligible for destruction
INSERT INTO destruction_queue (doc_id, reason)
SELECT dm.doc_id, 'retention_expired'
FROM document_metadata dm
LEFT JOIN destruction_queue dq
  ON dq.doc_id = dm.doc_id
  AND dq.status IN ('queued', 'processing', 'completed')
WHERE dq.doc_id IS NULL
  AND dm.legal_hold = FALSE
  AND dm.retain_until <= CURRENT_DATE;

Process Destruction Queue​

-- Function to process destruction queue
CREATE OR REPLACE FUNCTION process_destruction_queue(batch_size INT DEFAULT 100)
RETURNS TABLE(doc_id UUID, status TEXT) AS $$
DECLARE
  item RECORD;
  result_status TEXT;
BEGIN
  -- Get batch of queued items
  FOR item IN
    SELECT dq.id, dq.doc_id
    FROM destruction_queue dq
    WHERE dq.status = 'queued'
    ORDER BY dq.queued_at
    LIMIT batch_size
    FOR UPDATE SKIP LOCKED
  LOOP
    -- Mark as processing
    UPDATE destruction_queue
    SET status = 'processing', processed_by = current_user
    WHERE id = item.id;

    BEGIN
      -- Verify still eligible (no hold added since queuing)
      PERFORM 1 FROM document_metadata
      WHERE doc_id = item.doc_id
        AND legal_hold = FALSE
        AND retain_until <= CURRENT_DATE;

      IF FOUND THEN
        -- Mark document as deleted (logical delete)
        UPDATE document_metadata
        SET status = 'destroyed'
        WHERE doc_id = item.doc_id;

        -- Update destruction record
        UPDATE destruction_queue
        SET status = 'completed', processed_at = now()
        WHERE id = item.id;

        result_status := 'completed';
      ELSE
        -- Document no longer eligible (hold added or date changed)
        UPDATE destruction_queue
        SET status = 'skipped', processed_at = now(),
            error_message = 'No longer eligible for destruction'
        WHERE id = item.id;

        result_status := 'skipped';
      END IF;

    EXCEPTION WHEN OTHERS THEN
      -- Log error and mark as failed
      UPDATE destruction_queue
      SET status = 'failed', processed_at = now(),
          error_message = SQLERRM
      WHERE id = item.id;

      result_status := 'failed';
    END;

    doc_id := item.doc_id;
    status := result_status;
    RETURN NEXT;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION apply_legal_hold(
  p_doc_id UUID,
  p_reason TEXT,
  p_applied_by TEXT
)
RETURNS VOID AS $$
BEGIN
  UPDATE document_metadata
  SET legal_hold = TRUE,
      legal_hold_reason = p_reason,
      last_modified_at = now()
  WHERE doc_id = p_doc_id;

  -- Log the action
  INSERT INTO audit_events (user_id, action, doc_id, new_value)
  VALUES (p_applied_by, 'legal_hold_applied', p_doc_id,
          jsonb_build_object('reason', p_reason));

  -- Remove from destruction queue if present
  DELETE FROM destruction_queue
  WHERE doc_id = p_doc_id AND status = 'queued';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION release_legal_hold(
  p_doc_id UUID,
  p_released_by TEXT
)
RETURNS VOID AS $$
DECLARE
  old_reason TEXT;
BEGIN
  -- Get current reason for audit
  SELECT legal_hold_reason INTO old_reason
  FROM document_metadata
  WHERE doc_id = p_doc_id;

  UPDATE document_metadata
  SET legal_hold = FALSE,
      legal_hold_reason = NULL,
      last_modified_at = now()
  WHERE doc_id = p_doc_id;

  -- Log the action
  INSERT INTO audit_events (user_id, action, doc_id, old_value, new_value)
  VALUES (p_released_by, 'legal_hold_released', p_doc_id,
          jsonb_build_object('reason', old_reason),
          jsonb_build_object('reason', NULL));
END;
$$ LANGUAGE plpgsql;

Review Due Date Alerts​

Find Documents Approaching Review​

CREATE OR REPLACE VIEW v_documents_approaching_review AS
SELECT
  dm.doc_id,
  dm.title,
  dm.review_due_date,
  dm.owner_user_id,
  dm.domain,
  dm.document_type,
  (dm.review_due_date - CURRENT_DATE) as days_until_due,
  CASE
    WHEN dm.review_due_date <= CURRENT_DATE THEN 'overdue'
    WHEN dm.review_due_date <= CURRENT_DATE + 30 THEN 'due_soon'
    WHEN dm.review_due_date <= CURRENT_DATE + 90 THEN 'upcoming'
    ELSE 'scheduled'
  END as urgency
FROM document_metadata dm
WHERE dm.status = 'effective'
  AND dm.review_due_date IS NOT NULL
ORDER BY dm.review_due_date;

Generate Review Tasks​

-- Weekly job: create review tasks
INSERT INTO tasks (task_type, doc_id, assigned_to, due_date, status)
SELECT
  'periodic_review',
  doc_id,
  owner_user_id,
  review_due_date,
  'pending'
FROM v_documents_approaching_review
WHERE urgency IN ('overdue', 'due_soon')
  AND NOT EXISTS (
    SELECT 1 FROM tasks t
    WHERE t.doc_id = v_documents_approaching_review.doc_id
      AND t.task_type = 'periodic_review'
      AND t.status = 'pending'
  );

Retention Reports​

Documents by Retention Status​

CREATE OR REPLACE VIEW v_retention_summary AS
SELECT
  retention_category,
  COUNT(*) as total_documents,
  SUM(CASE WHEN retain_until <= CURRENT_DATE AND legal_hold = FALSE THEN 1 ELSE 0 END) as eligible_for_destruction,
  SUM(CASE WHEN legal_hold = TRUE THEN 1 ELSE 0 END) as on_hold,
  SUM(CASE WHEN retain_until BETWEEN CURRENT_DATE AND CURRENT_DATE + 90 THEN 1 ELSE 0 END) as expiring_90_days,
  MIN(retain_until) as earliest_expiry,
  MAX(retain_until) as latest_expiry
FROM document_metadata
WHERE status != 'destroyed'
GROUP BY retention_category
ORDER BY retention_category;

Destruction History​

CREATE OR REPLACE VIEW v_destruction_history AS
SELECT
  date_trunc('month', dq.processed_at) as month,
  dq.reason,
  COUNT(*) as documents_destroyed,
  COUNT(CASE WHEN dq.status = 'completed' THEN 1 END) as successful,
  COUNT(CASE WHEN dq.status = 'failed' THEN 1 END) as failed
FROM destruction_queue dq
WHERE dq.processed_at IS NOT NULL
GROUP BY date_trunc('month', dq.processed_at), dq.reason
ORDER BY month DESC;

References​