Data Retention Policies and Automation: Keep What You Need, Delete What You Must

Companies retain an average of 33% more data than they legally need, increasing both storage costs and breach exposure. Data retention is where legal compliance, cost optimization, and security converge. Retain too much and you increase your attack surface. Retain too little and you violate legal hold requirements.

This guide covers retention policy design, legal requirements by data type, and automated enforcement that keeps your organization compliant without manual intervention.

Key Takeaways

• Retention periods are determined by the intersection of legal requirements, business needs, and privacy obligations
• Automated enforcement is essential --- manual deletion processes have a 40% failure rate
• Different data types within the same system may have different retention periods
• Legal holds must override standard retention policies when litigation is anticipated

---

Retention Period Requirements by Regulation

Minimum Retention Requirements

Country-Specific Tax Retention

---

Designing a Retention Schedule

Step 1: Inventory Data Categories

Step 2: Assign Retention Periods

For each category, determine the retention period using this hierarchy:

1. Legal minimum: What does the law require you to keep?
2. Legal maximum: What does the law require you to delete? (GDPR purpose limitation)
3. Business need: How long do you actually need it?
4. Risk tolerance: What is the cost of keeping it vs deleting it?

The retention period is: MAX(legal minimum, business need) but NOT LONGER THAN legal maximum (if applicable).

Step 3: Define Disposal Actions

---

Automated Retention Enforcement

PostgreSQL Implementation

-- Retention policy table
CREATE TABLE retention_policies (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    date_column VARCHAR(100) NOT NULL,
    retention_days INTEGER NOT NULL,
    action VARCHAR(20) NOT NULL, -- 'DELETE', 'ANONYMIZE', 'ARCHIVE'
    condition TEXT, -- Optional WHERE clause
    enabled BOOLEAN DEFAULT TRUE,
    last_run TIMESTAMP,
    records_affected INTEGER DEFAULT 0
);

-- Insert policies
INSERT INTO retention_policies (table_name, date_column, retention_days, action, condition) VALUES
('support_tickets', 'closed_at', 1095, 'ANONYMIZE', 'status = ''closed'''),
('recruitment_candidates', 'applied_at', 730, 'DELETE', 'status != ''hired'''),
('session_logs', 'created_at', 90, 'DELETE', NULL),
('newsletter_unsubscribed', 'unsubscribed_at', 365, 'DELETE', NULL),
('audit_logs', 'created_at', 2555, 'ARCHIVE', NULL);

-- Enforcement function
CREATE OR REPLACE FUNCTION enforce_retention_policies()
RETURNS TABLE(policy_id UUID, table_name TEXT, records_affected BIGINT) AS $$
DECLARE
    policy RECORD;
    affected BIGINT;
    query TEXT;
BEGIN
    FOR policy IN
        SELECT * FROM retention_policies WHERE enabled = TRUE
    LOOP
        IF policy.action = 'DELETE' THEN
            query := format(
                'DELETE FROM %I WHERE %I < NOW() - INTERVAL ''%s days''',
                policy.table_name, policy.date_column, policy.retention_days
            );
            IF policy.condition IS NOT NULL THEN
                query := query || ' AND ' || policy.condition;
            END IF;
        ELSIF policy.action = 'ANONYMIZE' THEN
            -- Anonymization requires table-specific logic
            -- This is a simplified example
            query := format(
                'UPDATE %I SET email = ''[email protected]'', name = ''Anonymized'' WHERE %I < NOW() - INTERVAL ''%s days''',
                policy.table_name, policy.date_column, policy.retention_days
            );
            IF policy.condition IS NOT NULL THEN
                query := query || ' AND ' || policy.condition;
            END IF;
        END IF;

        EXECUTE query;
        GET DIAGNOSTICS affected = ROW_COUNT;

        UPDATE retention_policies
        SET last_run = NOW(), records_affected = affected
        WHERE id = policy.id;

        RETURN QUERY SELECT policy.id, policy.table_name::TEXT, affected;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Scheduling

# Run retention enforcement daily at 3 AM
# /etc/cron.d/retention-enforcement
0 3 * * * postgres psql -d ecosire -c "SELECT * FROM enforce_retention_policies();" >> /var/log/retention-enforcement.log 2>&1

---

Legal Holds

When a Legal Hold Applies

A legal hold suspends normal retention policies when litigation is anticipated, pending, or ongoing. All potentially relevant data must be preserved regardless of retention schedules.

Implementation

-- Legal hold table
CREATE TABLE legal_holds (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    matter_name VARCHAR(255) NOT NULL,
    description TEXT,
    custodians TEXT[], -- Users whose data is held
    tables_affected TEXT[], -- Tables where retention is suspended
    created_at TIMESTAMP DEFAULT NOW(),
    released_at TIMESTAMP,
    created_by UUID REFERENCES users(id)
);

-- Modified retention enforcement respects legal holds
CREATE OR REPLACE FUNCTION enforce_retention_with_holds()
RETURNS void AS $$
BEGIN
    -- Skip tables under active legal hold
    DELETE FROM session_logs
    WHERE created_at < NOW() - INTERVAL '90 days'
    AND 'session_logs' NOT IN (
        SELECT UNNEST(tables_affected)
        FROM legal_holds
        WHERE released_at IS NULL
    );
END;
$$ LANGUAGE plpgsql;

---

Verification and Compliance

Monthly Retention Audit

• All retention policies are documented and current
• Automated enforcement ran successfully (check logs)
• No data exists beyond retention period (sample check)
• Legal holds are active for all pending litigation
• New data categories have been assigned retention periods
• Third-party processors comply with retention requirements

---

Frequently Asked Questions

What happens if we keep data longer than necessary under GDPR?

Retaining personal data beyond its necessary retention period violates GDPR's storage limitation principle (Article 5(1)(e)). Supervisory authorities can issue fines of up to EUR 20 million or 4% of global annual turnover. More practically, excess data increases your exposure in a breach --- you are liable for data you should not have had.

How do we handle retention for backup copies?

Backups complicate retention because they contain snapshots of data at specific points in time. Options: (1) rotate backups on a schedule aligned with your longest retention period, (2) encrypt backups and destroy the encryption key when retention expires ("crypto-shredding"), (3) accept that backups may contain expired data but document this as a technical limitation with compensating controls.

How do we implement retention in Odoo?

Odoo does not have built-in retention automation. Implement it using: (1) scheduled actions (cron jobs) that archive or anonymize old records, (2) custom modules that enforce retention rules on specific models, (3) database-level functions for bulk operations. ECOSIRE provides Odoo customization for automated data lifecycle management.

---

What Comes Next

Data retention is one component of a complete governance program. Combine it with privacy by design for new systems, employee data privacy for HR data, and vendor contract management for third-party processors.

Contact ECOSIRE for data retention policy design and implementation consulting.

---

Published by ECOSIRE -- helping businesses manage data through its entire lifecycle.