Database Data Archiving

You are a data management expert. Help me implement data archiving.

## Archiving Needs
Data types: ${{DATA_TYPES}}
Retention requirements: ${{RETENTION}}
Access patterns: ${{ACCESS}}
Compliance: ${{COMPLIANCE}}

## Please Design:

1. **Archiving Strategy**

// Approaches:
// - Cold storage archive
// - Separate archive tables
// - Partitioned tables
// - Time-based deletion


2. **Table Partitioning**

-- Range partitioning by date:
CREATE TABLE orders (
  id BIGINT,
  created_at TIMESTAMP,
  data JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');


3. **Archive Tables**

-- Archive table structure:
CREATE TABLE orders_archive (
  LIKE orders INCLUDING ALL
);

-- Archive procedure:
CREATE PROCEDURE archive_old_orders(cutoff_date DATE)
LANGUAGE plpgsql AS $$
BEGIN
  -- Move to archive
  INSERT INTO orders_archive
  SELECT * FROM orders
  WHERE created_at < cutoff_date;

  -- Delete from main
  DELETE FROM orders
  WHERE created_at < cutoff_date;
END;
$$;


4. **Tiered Storage**

// Storage tiers:
// Hot: Current data (SSD)
// Warm: 30-90 days (HDD)
// Cold: > 90 days (S3/Glacier)
// Archive: > 1 year (Glacier Deep Archive)


5. **Compression**

-- PostgreSQL compression:
ALTER TABLE orders_archive SET (
  toast_tuple_target = 128
);

-- TimescaleDB compression:
SELECT compress_chunk(chunk);


6. **Access Layer**

// Unified access:
async function getOrder(id: string) {
  // Try hot storage first
  let order = await db.orders.findUnique({ where: { id } });

  if (!order) {
    // Fall back to archive
    order = await db.ordersArchive.findUnique({ where: { id } });
  }

  return order;
}


7. **Automation**

-- Scheduled archiving:
SELECT cron.schedule(
  'archive-old-orders',
  '0 2 * * 0',  -- Weekly at 2 AM
  $$CALL archive_old_orders(NOW() - INTERVAL '90 days')$$
);


8. **Compliance**

// Retention policies:
// - Legal hold
// - Right to deletion
// - Audit trail
// - Data lineage