Data Generation Plan

Context & Purpose

Generate a large volume of realistic check-in, registration, small group attendance, and event data over a 4-month historical period (October 2025 – January 2026). The purpose is to demonstrate the JSONB archival system's efficiency by comparing table sizes before and after running the weekly archival script.

The data is pushed to production for a live demo of database size reduction — showing how the automated archival system compresses tens of thousands of relational rows into compact JSONB arrays.

Current State (Pre-Generation)

• Children's ministry groups exist (K-5th grade, EC rooms, High School, Middle School) but have 0 member rosters
• Only 7 children identified (relationship_id=2) — insufficient for realistic demo data
• All data generation uses existing people from the database; no new people or families created

ID Ranges (Collision Avoidance)

All generated data uses reserved ID ranges to avoid collisions with existing production data. The script is idempotent — it includes DELETE WHERE id BETWEEN X AND Y cleanup at the top for safe re-runs.

Step 1: Populate Children's Ministry Groups

Assign existing people as "children" into grade-level groups. Since we cannot identify real children (no DOB data), we pick people who aren't already in many groups and distribute them across 19 ministry groups.

Total: ~1,260 memberships across children's ministry groups.

Step 2: Create Sunday Events (~102 events)

For each Sunday from October 5, 2025 through January 25, 2026 (17 Sundays), create 6 event types:

Total: 102 Sunday events

Step 3: Create Weekday Events (~56 events)

Total: 56 weekday/special events

Step 4: Create Check-In Records (~52,000+)

Check-in records are the primary bulk data generator. Each record includes a security code (random Letter-Number-Letter-Number pattern) and a checked_in_at timestamp offset randomly from the event start time (0–45 minutes).

Estimated total: ~52,700 checkins

Step 5: Create Registrations (~8,000)

Registrations represent intent to attend, created for various event categories:

• Middle School Winter Camp — all Middle School 6th/7th/8th grade members (~180 registrations)
• High School Winter Retreat — all High School 9th-12th grade members (~280 registrations)
• Sunday Children's events — K-5 members for 4 selected Sundays (~1,920 registrations)
• Sunday High School events — High School members for 8 selected Sundays (~2,240 registrations)
• Wednesday Middle School events — Middle School members for 8 selected weeks (~1,440 registrations)
• Miscellaneous — additional event registrations (~1,940 registrations)

Estimated total: ~8,000 registrations

Step 6: Create Small Groups + Attendance (~3,500 records)

Expand Existing Small Groups

Add ~12 more members to each of the 4 existing small groups (IDs 3051, 3105, 3075, 3090) and create 17 weeks of attendance at 95% rate. Total: 68 attendance records.

15 New Small Groups

15 groups × ~15 avg members × 17 weeks × 85% attendance = ~3,250 attendance records
Plus 4 existing groups × 17 weeks = 68 records
Total: ~3,318 small_group_attendance records

Step 7: Baseline JSONB Event Attendance

Create a small number of event_attendance JSONB records for a handful of older events. These represent "already archived" data that serves as a comparison baseline, showing the JSONB format alongside the relational data before archival runs.

Expected Record Counts

Total new records: ~68,000+

Actual Results

The script was executed on the local database on February 23, 2026. The actual results exceeded the planned targets:

Archival Demo Results

After generating the data, the archive_old_data.sql script was run with a 2-month cutoff. Events from October and November 2025 (older than 2 months from the script's run date) were archived.

Archival Operation Counts

Table Size Comparison

Size Reduction Summary

What Happened to the Data

Demo Workflow

Steps to reproduce the archival demonstration:

1. Snapshot Pre-Archival Sizes

SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
WHERE relname IN ('checkins', 'registrations', 'event_attendance',
                    'small_group_attendance', 'group_membership');

2. Run Archival Script

# On production (Linux, cron would run this automatically)
sudo -u postgres psql -d myapp -f scripts/archive_old_data.sql

# On local Windows dev machine
PGPASSWORD=nbka4ko "/c/Program Files/PostgreSQL/18/bin/psql.exe" \
  -U postgres -d myapp -f scripts/archive_old_data.sql

3. Snapshot Post-Archival Sizes

Run the same size query again and compare the results.

4. Count Records Moved

SELECT COUNT(*) FROM event_attendance;   -- new JSONB rows
SELECT COUNT(*) FROM checkins;            -- should be reduced
SELECT COUNT(*) FROM registrations;       -- should be reduced

Verification Steps

• Run the data generation script on local DB
• Query record counts for each table
• Run archive_old_data.sql and verify checkins/registrations from Oct-Nov are cleaned up
• Verify event_attendance JSONB rows were created with correct attendee_ids arrays
• Compare table sizes before/after with pg_total_relation_size()
• Push to production, repeat size comparison there

Files Modified