Skip to main content
skill CX Codex

data-retention-archiving-planner — Claude Code skill: how to install

data-retention-archiving-planner plans data retention policies, maps compliance requirements, and automates cold storage migration for databases.

MIT 273 installs ★ 64 updated 2026-01-12

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add patricio0312rev/skills --skill data-retention-archiving-planner -a claude-code
Open on GitHub

What data-retention-archiving-planner does

This skill structures data retention schedules and generates migration workflows for relational databases. It contains reference retention matrices and regulatory baselines covering GDPR, HIPAA, SOX, and PCI DSS across hot and cold storage tiers.

Implementation patterns cover PostgreSQL table partitioning, TypeScript cleanup jobs using Prisma transactions, and soft-delete middleware. It also drafts batch export jobs using psql \COPY to stream compressed files directly into Amazon S3 buckets. If your environment lacks an ARCHIVE_DATABASE_URL or valid AWS CLI credentials, the generated archival scripts fail immediately on execution.

What it helps with

  • Define data retention schedules compliant with GDPR, HIPAA, or SOX
  • Design PostgreSQL partition tables by date ranges for historical datasets
  • Implement automated batch cleanup jobs for expired sessions and audit logs
  • Migrate cold records from production databases into compressed S3 dumps

Example prompts for the agent

  • Create a data retention policy table for an e-commerce platform under SOX rules
  • Generate a TypeScript Prisma job to archive orders older than two years in batches
  • Set up a Prisma soft-delete middleware and a purge script for deleted accounts

How to install data-retention-archiving-planner

1Skills CLI (Vercel)

npx skills add patricio0312rev/skills --skill data-retention-archiving-planner -a claude-code

2Manually: put the files in place

~/.claude/skills/data-retention-archiving-planner/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/data-retention-archiving-planner/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about data-retention-archiving-planner

How to install data-retention-archiving-planner in Claude Code?

Run npx skills add patricio0312rev/skills --skill data-retention-archiving-planner -a claude-code in your terminal.

What does data-retention-archiving-planner do?

It drafts retention policies, configures SQL partitions, and produces automated cleanup or cold storage migration scripts.

What database tools are used in the generated code?

The provided patterns focus on PostgreSQL, Prisma ORM in TypeScript, and export scripts using psql with Amazon S3.

Source text

SKILL.md · MIT
---
name: data-retention-archiving-planner
description: Plans and implements data retention policies with archival strategies, compliance requirements, automated cleanup jobs, and cold storage migration. Use for "data retention", "data archival", "GDPR compliance", or "storage optimization".
---

# Data Retention & Archiving Planner

Manage data lifecycle with automated retention and archiving.

## Retention Policy Document

```markdown
# Data Retention Policy

## Retention Periods

| Data Type             | Hot Storage | Cold Storage | Total Retention | Reason            |
| --------------------- | ----------- | ------------ | --------------- | ----------------- |
| User accounts         | Active      | N/A          | Indefinite      | Business need     |
| Order history         | 2 years     | 5 years      | 7 years         | Tax compliance    |
| Logs                  | 30 days     | 90 days      | 120 days        | Operational       |
| Analytics events      | 90 days     | 1 year       | 15 months       | Business insights |
| Audit trails          | 1 year      | 6 years      | 7 years         | Legal compliance  |
| User sessions         | 30 days     | None         | 30 days         | Security          |
| Failed login attempts | 90 days     | None         | 90 days         | Security          |

## Compliance Requirements

### GDPR (EU)

- Right to erasure (right to be forgotten)
- Data minimization
- Storage limitation

### HIPAA (Healthcare)

- Minimum 6 years retention
- Secure archival required

### SOX (Financial)

- 7 years retention for financial records
- Immutable audit trails

### PCI DSS (Payments)

- 1 year minimum for audit logs
- 3 months minimum for transaction logs
```

## Archive Schema Design

```sql
-- Hot database: Current active data
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Cold database: Archived historical data
CREATE TABLE orders_archive (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  archived_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Create partition for time-based archival
CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
```

## Archival Job Implementation

```typescript
// jobs/archive-orders.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();
const archivePrisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.ARCHIVE_DATABASE_URL,
    },
  },
});

interface ArchivalJob {
  table: string;
  retentionDays: number;
  batchSize: number;
}

async function archiveOrders() {
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - 730); // 2 years

  console.log(`📦 Archiving orders older than ${cutoffDate.toISOString()}`);

  let archived = 0;
  let hasMore = true;

  while (hasMore) {
    await prisma.$transaction(async (tx) => {
      // Find orders to archive
      const ordersToArchive = await tx.order.findMany({
        where: {
          created_at: { lt: cutoffDate },
          status: { in: ["delivered", "cancelled"] },
        },
        take: 1000,
      });

      if (ordersToArchive.length === 0) {
        hasMore = false;
        return;
      }

      // Copy to archive database
      await archivePrisma.order.createMany({
        data: ordersToArchive.map((order) => ({
          ...order,
          archived_at: new Date(),
        })),
        skipDuplicates: true,
      });

      // Delete from hot database
      await tx.order.deleteMany({
        where: {
          id: { in: ordersToArchive.map((o) => o.id) },
        },
      });

      archived += ordersToArchive.length;
      console.log(`  Archived ${archived} orders...`);
    });

    // Rate limiting
    await new Promise((resolve) => setTimeout(resolve, 100));
  }

  console.log(`✅ Archived ${archived} orders total`);
}

// Schedule: Run nightly
archiveOrders();
```

## Automated Cleanup Jobs

```typescript
// jobs/cleanup-old-data.ts
interface CleanupJob {
  table: string;
  column: string;
  retentionDays: number;
}

const CLEANUP_JOBS: CleanupJob[] = [
  {
    table: "sessions",
    column: "created_at",
    retentionDays: 30,
  },
  {
    table: "password_reset_tokens",
    column: "created_at",
    retentionDays: 1,
  },
  {
    table: "failed_login_attempts",
    column: "attempted_at",
    retentionDays: 90,
  },
  {
    table: "analytics_events",
    column: "created_at",
    retentionDays: 90,
  },
];

async function runCleanupJobs() {
  console.log("🗑️  Running cleanup jobs...\n");

  for (const job of CLEANUP_JOBS) {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - job.retentionDays);

    const result = await prisma.$executeRawUnsafe(
      `
      DELETE FROM "${job.table}"
      WHERE "${job.column}" < $1
    `,
      cutoffDate
    );

    console.log(
      `✅ ${job.table}: Deleted ${result} rows older than ${job.retentionDays} days`
    );
  }

  console.log("\n✅ Cleanup complete!");
}
```

## Soft Delete Pattern

```typescript
// Soft delete for GDPR compliance
model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  deletedAt DateTime? // NULL = active, NOT NULL = deleted
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([deletedAt])
}

// Middleware to filter soft-deleted records
prisma.$use(async (params, next) => {
  if (params.action === 'findMany' || params.action === 'findFirst') {
    params.args.where = {
      ...params.args.where,
      deletedAt: null, // Only show non-deleted
    };
  }
  return next(params);
});

// Hard delete after retention period
async function purgeDeletedUsers() {
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - 90); // 90 days retention

  const result = await prisma.user.deleteMany({
    where: {
      deletedAt: { lt: cutoffDate },
    },
  });

  console.log(`🗑️  Purged ${result.count} deleted users`);
}
```

## Cold Storage Migration

```bash
#!/bin/bash
# scripts/migrate-to-s3.sh

# Dump old orders to S3 for cold storage
CUTOFF_DATE="2022-01-01"

echo "📦 Migrating orders to S3..."

# 1. Export to CSV
psql $DATABASE_URL -c "\COPY (
  SELECT * FROM orders WHERE created_at < '$CUTOFF_DATE'
) TO STDOUT WITH CSV HEADER" | gzip > orders_archive.csv.gz

# 2. Upload to S3
aws s3 cp orders_archive.csv.gz s3://my-cold-storage/orders/

# 3. Verify upload
if aws s3 ls s3://my-cold-storage/orders/orders_archive.csv.gz; then
  echo "✅ Uploaded to S3"

  # 4. Delete from database
  psql $DATABASE_URL -c "DELETE FROM orders WHERE created_at < '$CUTOFF_DATE'"

  echo "✅ Deleted from database"
else
  echo "❌ S3 upload failed, skipping deletion"
  exit 1
fi
```

## Compliance Automation

```typescript
// Right to be forgotten (GDPR)
async function deleteUserData(userId: number) {
  console.log(`🗑️  Deleting user data for user ${userId}...`);

  await prisma.$transaction(async (tx) => {
    // 1. Anonymize orders (keep for business records)
    await tx.order.updateMany({
      where: { userId },
      data: {
        userId: null,
        shippingAddress: "[DELETED]",
        billingAddress: "[DELETED]",
      },
    });

    // 2. Delete personal data
    await tx.userProfile.delete({ where: { userId } });
    await tx.paymentMethod.deleteMany({ where: { userId } });
    await tx.address.deleteMany({ where: { userId } });

    // 3. Soft delete user account
    await tx.user.update({
      where: { id: userId },
      data: {
        email: `deleted-${userId}@example.com`,
        name: "[DELETED]",
        deletedAt: new Date(),
      },
    });
  });

  console.log(`✅ User data deleted`);
}
```

## Monitoring & Alerting

```typescript
// Monitor archive job health
async function checkArchivalHealth() {
  // Check oldest active order
  const oldestOrder = await prisma.order.findFirst({
    orderBy: { created_at: "asc" },
  });

  const age = Date.now() - oldestOrder.created_at.getTime();
  const ageDays = age / (1000 * 60 * 60 * 24);

  if (ageDays > 750) {
    // > 2 years + buffer
    console.error("⚠️  Orders older than retention period found!");
    await sendAlert({
      title: "Archive job failing",
      message: `Oldest order is ${ageDays.toFixed(0)} days old`,
    });
  }

  // Check archive database size
  const archiveCount = await archivePrisma.order.count();
  console.log(`📊 Archive database: ${archiveCount} orders`);

  // Check hot database size
  const hotCount = await prisma.order.count();
  console.log(`📊 Hot database: ${hotCount} orders`);
}
```

## Restore from Archive

```typescript
// Restore archived order (e.g., for audit)
async function restoreArchivedOrder(orderId: number) {
  // Find in archive
  const archivedOrder = await archivePrisma.order.findUnique({
    where: { id: orderId },
  });

  if (!archivedOrder) {
    throw new Error("Order not found in archive");
  }

  // Copy to hot database
  await prisma.order.create({
    data: {
      ...archivedOrder,
      archived_at: undefined,
    },
  });

  console.log(`✅ Restored order ${orderId} from archive`);
}
```

## Schedule Configuration

```yaml
# cron schedule for archival jobs
jobs:
  archive-orders:
    schedule: "0 2 * * *" # 2 AM daily
    command: "npm run job:archive-orders"

  cleanup-sessions:
    schedule: "0 3 * * *" # 3 AM daily
    command: "npm run job:cleanup-sessions"

  purge-deleted-users:
    schedule: "0 4 * * 0" # 4 AM Sunday
    command: "npm run job:purge-deleted"

  health-check:
    schedule: "0 */6 * * *" # Every 6 hours
    command: "npm run job:check-archival-health"
```

## Best Practices

1. **Define clear policies**: Document retention periods
2. **Automate everything**: Manual cleanup is unreliable
3. **Test restore**: Regularly test archive restoration
4. **Monitor job health**: Alert on failures
5. **Compliance first**: Meet legal requirements
6. **Soft delete**: Before hard delete
7. **Batch operations**: Avoid locking tables

## Output Checklist

- [ ] Retention policy documented
- [ ] Archive schema designed
- [ ] Archival jobs implemented
- [ ] Cleanup jobs automated
- [ ] Soft delete pattern (if applicable)
- [ ] Cold storage migration
- [ ] GDPR compliance (right to be forgotten)
- [ ] Job scheduling configured
- [ ] Monitoring and alerting
- [ ] Restore procedure tested

© patricio0312rev · MIT · patricio0312rev/skills

Similar skills

All skills for Data and databases →
check-skill-provenance-stamps-before-archiving-knowledge-entries ZaxbyHub check-skill-provenance-stamps-before-archiving-knowledge-entries is a Claude Code skill that checks source_refs and UUIDs before archiving knowledge entries, stopping removal if an active skill depends on one. CX Codex +1 ★ 539repo stars zomato-ai-data-engineering-pipeline reason-machines zomato-ai-data-engineering-pipeline is a Claude Code skill for building a food delivery batch pipeline across S3, Snowflake, dbt, and Airflow, with OpenAI for review enrichment and text-to-SQL. CX Codex +1 128installs prisma-postgres prisma A deprecated compatibility name for prisma-postgres-setup. Use it only when explicitly invoked; new projects should select prisma-postgres-setup instead. CX Codex +1 431,100installs prisma-postgres-setup prisma prisma-postgres-setup provisions or connects a Prisma Postgres database and links it to your app. Useful for spinning up dev databases quickly or wiring connections in Vercel and v0. CX Codex +1 425,700installs firebase-data-connect Google LLC firebase-data-connect is a Claude Code skill for building Firebase SQL Connect backends with PostgreSQL. It activates for schema design, authorized operations, realtime updates, or SDK generation. CX Codex +1 179,900installs prisma-patterns affaan-m prisma-patterns is a Claude Code skill with production patterns and schema guidelines for Prisma ORM in TypeScript. It helps write safe queries, transactions, and pagination while avoiding connection pool exhaustion. CX Codex +1 6,300installs

From the same set148

The whole set →
backend-latency-profiler-helper backend-latency-profiler-helper tracks down slow API endpoints, lists likely causes, and puts the fixes in priority order. CX Codex +1 275installs rbac-policy-tester rbac-policy-tester generates role permission matrices and automated authorization test suites to catch RBAC regressions in CI. CX Codex +1 274installs etl-sync-job-builder etl-sync-job-builder generates TypeScript ETL pipelines with incremental watermark tracking, idempotent batch upserts, and retry backoff logic. CX Codex +1 274installs structured-logging-standardizer structured-logging-standardizer sets up structured JSON logging with request correlation IDs using AsyncLocalStorage and pino. Keeps backend logs queryable and connected. CX Codex +1 273installs reliability-strategy-builder reliability-strategy-builder builds a failure-handling plan for a service: circuit breaker, retries with backoff, fallback, bulkhead isolation and SLOs with error budgets. CX Codex +1 273installs e2e-test-builder e2e-test-builder generates end-to-end browser tests for Playwright and Cypress using Page Object patterns, test fixtures, and semantic selectors. CX Codex +1 273installs caching-strategy-optimizer The caching-strategy-optimizer skill configures CI/CD caching for dependencies, Docker layers, build outputs, and test results. Use it when installs, builds, or tests slow down the pipeline. CX Codex +1 272installs component-scaffold-generator component-scaffold-generator builds the skeleton of a React or Vue component: typed props, variants, a test file, a Storybook story and JSDoc with an example. It asks for a name and props, then writes the files. CX Codex +1 272installs

Project sponsors

Companies that keep this analytics open