> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Archival Strategy

> This document outlines the data archival strategy for the Encore OS platform. The goal is to maintain database performance by archiving old data while ensuring…

**Version:** 1.0.0\
**Date:** 2026-01-16\
**Status:** Planning\
**Reference:** docs/migration/SUPABASE\_PERFORMANCE\_RECOMMENDATIONS.md

***

## Executive Summary

This document outlines the data archival strategy for the Encore OS platform. The goal is to maintain database performance by archiving old data while ensuring compliance with healthcare data retention requirements.

***

## 1. Archival Candidates

### 1.1 Audit & Activity Logs

| Table              | Retention Period | Archive After | Priority |
| ------------------ | ---------------- | ------------- | -------- |
| `pf_audit_logs`    | 7 years (HIPAA)  | 1 year        | High     |
| `pf_activity_logs` | 3 years          | 6 months      | High     |
| `pf_login_history` | 2 years          | 6 months      | Medium   |
| `pf_api_logs`      | 1 year           | 3 months      | Medium   |

### 1.2 Workflow & Execution Logs

| Table                    | Retention Period | Archive After | Priority |
| ------------------------ | ---------------- | ------------- | -------- |
| `fw_workflow_executions` | 3 years          | 1 year        | High     |
| `fw_execution_logs`      | 2 years          | 6 months      | High     |
| `fw_approval_history`    | 5 years          | 1 year        | Medium   |
| `fw_debug_sessions`      | 90 days          | 30 days       | Low      |
| `fw_sandbox_executions`  | 90 days          | 30 days       | Low      |

### 1.3 Notifications & Communications

| Table                        | Retention Period | Archive After | Priority |
| ---------------------------- | ---------------- | ------------- | -------- |
| `pf_notifications`           | 2 years          | 6 months      | High     |
| `pf_notification_deliveries` | 1 year           | 3 months      | Medium   |
| `pf_email_logs`              | 2 years          | 6 months      | Medium   |

### 1.4 Temporary & Session Data

| Table            | Retention Period | Archive After | Priority |
| ---------------- | ---------------- | ------------- | -------- |
| `pf_sessions`    | 30 days          | N/A (delete)  | High     |
| `pf_temp_files`  | 7 days           | N/A (delete)  | High     |
| `fw_form_drafts` | 90 days          | 30 days       | Medium   |

***

## 2. Healthcare Compliance Requirements

### 2.1 HIPAA Requirements

* **Patient Records:** Minimum 6 years from date of creation or last effective date
* **Audit Logs:** Minimum 6 years (some states require longer)
* **Business Records:** Generally 6-7 years

### 2.2 State-Specific Requirements

| State      | Requirement | Notes                                                  |
| ---------- | ----------- | ------------------------------------------------------ |
| California | 7 years     | From date of service                                   |
| New York   | 6 years     | From date of discharge                                 |
| Florida    | 7 years     | For adults, 7 years for minors after reaching majority |
| Texas      | 10 years    | For adults                                             |

### 2.3 Compliance Rules

1. **Never archive PHI-containing records prematurely**
2. **Maintain audit trail for archived data**
3. **Ensure archived data is retrievable**
4. **Document archival process for compliance audits**

***

## 3. Archival Methods

### 3.1 Method 1: Archive Tables (Recommended)

Create parallel archive tables in a separate schema.

```sql theme={null}
-- Create archive schema
CREATE SCHEMA IF NOT EXISTS archive;

-- Create archive table for audit logs
CREATE TABLE archive.pf_audit_logs (
    LIKE public.pf_audit_logs INCLUDING ALL
);

-- Move old records to archive
INSERT INTO archive.pf_audit_logs
SELECT * FROM public.pf_audit_logs
WHERE created_at < NOW() - INTERVAL '1 year';

-- Delete from main table
DELETE FROM public.pf_audit_logs
WHERE created_at < NOW() - INTERVAL '1 year';
```

**Advantages:**

* Data stays in same database
* Easy to query archived data when needed
* Simple implementation

**Disadvantages:**

* Storage costs remain in database
* May need to manage archive table growth

### 3.2 Method 2: External Storage (Future)

Export to external storage for long-term retention.

**Options:**

* AWS S3 / Glacier (cold storage)
* Azure Blob Storage
* Google Cloud Storage

**Use When:**

* Archive data exceeds practical database size
* Cost optimization is priority
* Data is rarely accessed

### 3.3 Method 3: Hard Delete (Non-PHI Only)

For temporary data that doesn't require retention.

```sql theme={null}
-- Delete old sessions
DELETE FROM pf_sessions
WHERE expires_at < NOW() - INTERVAL '7 days';

-- Delete old temp files
DELETE FROM pf_temp_files
WHERE created_at < NOW() - INTERVAL '7 days';
```

**Use Only For:**

* Session data
* Temporary files
* Debug/sandbox data
* Non-business-critical logs

***

## 4. Implementation Plan

### Phase 1: Infrastructure Setup

1. Create `archive` schema
2. Create archive tables for high-priority candidates
3. Set up archive RLS policies (platform\_admin only)
4. Create archival functions

```sql theme={null}
-- Create archive schema
CREATE SCHEMA IF NOT EXISTS archive;

-- Grant access to platform admins only
GRANT USAGE ON SCHEMA archive TO authenticated;
```

### Phase 2: Archival Functions

```sql theme={null}
-- Example archival function
CREATE OR REPLACE FUNCTION archive_old_audit_logs(retention_days INTEGER DEFAULT 365)
RETURNS INTEGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    archived_count INTEGER;
BEGIN
    -- Insert into archive
    INSERT INTO archive.pf_audit_logs
    SELECT * FROM public.pf_audit_logs
    WHERE created_at < NOW() - (retention_days || ' days')::INTERVAL;
    
    GET DIAGNOSTICS archived_count = ROW_COUNT;
    
    -- Delete from main table
    DELETE FROM public.pf_audit_logs
    WHERE created_at < NOW() - (retention_days || ' days')::INTERVAL;
    
    RETURN archived_count;
END;
$$;
```

### Phase 3: Scheduled Archival

Options for scheduling:

1. **Supabase Edge Functions** (cron trigger)
2. **External scheduler** (n8n, Vercel cron)
3. **pg\_cron extension** (if available)

**Recommended Schedule:**

* Weekly archival runs (low-traffic period)
* Monthly summary reports
* Quarterly compliance audits

### Phase 4: Monitoring

1. Track archive table sizes
2. Monitor archival job success/failure
3. Alert on failed archival runs
4. Report on archived record counts

***

## 5. Table Size Assessment Query

Run this query to identify archival candidates by size:

```sql theme={null}
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_total_relation_size(schemaname||'.'||tablename) as size_bytes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 30;
```

***

## 6. Archival Verification Query

After archival, verify data integrity:

```sql theme={null}
-- Count records by date range
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as record_count
FROM public.pf_audit_logs
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC
LIMIT 12;

-- Verify no records older than retention period
SELECT COUNT(*) 
FROM public.pf_audit_logs
WHERE created_at < NOW() - INTERVAL '1 year';

-- Compare archive counts
SELECT 
    'public' as schema,
    COUNT(*) as count
FROM public.pf_audit_logs
UNION ALL
SELECT 
    'archive' as schema,
    COUNT(*) as count
FROM archive.pf_audit_logs;
```

***

## 7. Risk Mitigation

### 7.1 Data Loss Prevention

* Always archive before delete
* Test archival process in staging first
* Maintain backup before first production run
* Verify record counts after each run

### 7.2 Compliance

* Document archival dates for audit
* Maintain chain of custody for PHI
* Test data retrieval from archive
* Regular compliance review

### 7.3 Performance

* Run archival during low-traffic periods
* Batch large archival operations
* Monitor lock contention
* Use transactions with appropriate isolation

***

## 8. Success Metrics

| Metric                  | Target | Measurement                     |
| ----------------------- | ------ | ------------------------------- |
| Archive completion rate | 100%   | Jobs completed / Jobs scheduled |
| Data integrity          | 100%   | Archived count = Deleted count  |
| Query performance       | \<1s   | Archive query execution time    |
| Storage reduction       | >20%   | Main table size reduction       |

***

## 9. Next Steps

1. [ ] Run table size assessment query
2. [ ] Identify top 10 archival candidates by size
3. [ ] Create archive schema and initial tables
4. [ ] Implement archival functions
5. [ ] Test in staging environment
6. [ ] Schedule production archival
7. [ ] Set up monitoring and alerting

***

## 10. References

* HIPAA Retention Requirements: [HHS.gov](https://www.hhs.gov/hipaa/index.html)
* Supabase Storage Best Practices: [Supabase Docs](https://supabase.com/docs/guides/storage)
* PostgreSQL Partitioning: [PostgreSQL Docs](https://www.postgresql.org/docs/current/ddl-partitioning.html)

***

**Document Owner:** Platform Team\
**Review Schedule:** Quarterly\
**Last Updated:** 2026-01-16