Database Archival Feature

The Growth Problem

Church management databases accumulate attendance data at a steady, predictable rate. A mid-size church with 500 weekly check-ins generates approximately 26,000 check-in records per year. Add registrations, group memberships, and multiple services per week, and the numbers multiply quickly.

In practice, many database systems grow unchecked because extracting and archiving old data is prohibitively complex. Foreign key constraints, cascading dependencies, and tightly coupled relational schemas make it risky to delete historical rows — breaking a single FK reference can cascade into data integrity failures across the system. As a result, tables accumulate years of data that is rarely queried but constantly slows down:

• Backups — larger tables mean longer backup times and bigger backup files
• Indexing — more rows in B-tree indexes means slower INSERT and slower lookups on non-indexed columns
• Maintenance — VACUUM, REINDEX, and ANALYZE take longer on large tables
• Day-to-day queries — even indexed queries slow down as the table grows beyond cache capacity

Problem Solved

With ~5,800 people, 252 events, and 6 months of attendance data (112,140 check-in records), the archival script was run against the full database (46 MB including indexes). Here is what happened:

The real-time tables stay small and fast (only the most recent 2 months), while every historical attendance record remains fully queryable in compact long-term storage. The savings compound over time—after 10 years, the system stores attendance data in roughly 5% of the space a purely relational approach would require.

Full details, methodology, and long-term projections are covered in the Results & Metrics and Long-Term Projections sections at the bottom of this document.

Design Philosophy

This system takes a fundamentally different approach: archival is designed into the schema from day one. The relational tables are intentionally treated as a working buffer holding only the data needed for real-time operations. The JSONB archival tables are the planned, permanent destination.

Because both tiers are part of the same schema with clear ownership boundaries (one JSONB row per event, one per group meeting), the archival script can safely migrate and delete relational rows in a single atomic transaction without risking referential integrity violations. The FK relationships are structured so that archived data flows into parent records rather than being orphaned:

• checkins → event_attendance.attendee_ids (JSONB array on the event)
• group_membership → church_group.archived_member_ids (JSONB array on the group)
• registrations → deleted (intent data has no archival value after the event)

Key Design Decisions

Two-Tier Storage Model

Storage Comparison

Tier 1: Hot Tables (Relational)

The relational tables hold only the most recent ~2 months of records, optimized for real-time operations:

checkins — Hot Data

Used by: kiosk duplicate detection, security code lookup, recent activity view, admin dashboard.

registrations — Hot Data

Used by: registration management, form registration flow, event roster display.

Tier 2: Cold JSONB Tables

JSONB tables store attendance as a flat array of person IDs per event or meeting date. One JSONB row replaces potentially hundreds of relational rows.

event_attendance — JSONB

small_group_attendance — JSONB

JSONB tables are used for:

• Per-person attendance history — "Show me every event John attended in 2025"
• Head count trends — array length gives instant head count without COUNT(*)
• Reporting & analytics — attendance trends over months and years

GIN Indexes

JSONB attendance arrays are queried using PostgreSQL's @> containment operator, backed by GIN (Generalized Inverted Index) indexes. This allows efficient per-person lookups across all attendance records without sequential scans.

CREATE INDEX idx_event_attendance_attendee_ids
  ON event_attendance USING GIN (attendee_ids);

CREATE INDEX idx_small_group_attendance_attendee_ids
  ON small_group_attendance USING GIN (attendee_ids);

Query Example

-- Find all events person #42 attended
SELECT event_id, attendee_ids, created_at
FROM event_attendance
WHERE attendee_ids @> '42'::jsonb;

-- EF Core equivalent (via Npgsql)
.Where(a => EF.Functions.JsonContains(a.AttendeeIds, personId))

Permanent Groups

The church_group.is_permanent flag (boolean, default false) prevents a group's memberships from ever being archived. This is used for groups where historical membership is operationally important:

• Safety & security teams (must always know who had access)
• Staff rosters (HR/compliance requirement)
• Leadership teams (historical accountability)

Archival Script Overview

The archival engine is a single SQL script (scripts/archive_old_data.sql) that performs three operations inside one transaction. Script file: ~138 lines of annotated SQL.

Execution Flow

Operation 1: Checkin Archival (Upsert)

The most complex operation. Uses PostgreSQL INSERT ... ON CONFLICT DO UPDATE to safely merge person IDs into the event_attendance.attendee_ids array, even when a row already exists (e.g., from a previous partial archival or manual attendance save).

INSERT INTO event_attendance (event_id, attendee_ids, notes, created_at)
SELECT
    c.event_id,
    jsonb_agg(DISTINCT c.person_id ORDER BY c.person_id),
    NULL,
    now()
FROM checkins c
JOIN events e ON e.id = c.event_id
WHERE e.starts_at < cutoff_date
GROUP BY c.event_id
ON CONFLICT (event_id) DO UPDATE SET
    attendee_ids = (
        SELECT jsonb_agg(DISTINCT val ORDER BY val)
        FROM (
            SELECT jsonb_array_elements(event_attendance.attendee_ids)::integer AS val
            UNION
            SELECT jsonb_array_elements(EXCLUDED.attendee_ids)::integer AS val
        ) merged
    );

How the Merge Works

1. GROUP BY c.event_id — aggregates all checkins for each event into one row
2. jsonb_agg(DISTINCT ...) — creates a deduplicated, sorted JSONB array of person IDs
3. ON CONFLICT — if the event already has an attendance row, merge arrays via UNION
4. After upsert, delete the source checkin rows

Operation 2: Registration Cleanup

Registrations are deleted without archival. A registration represents intent to attend an event. Once the event has passed, the meaningful data is the check-in record (actual attendance), not the registration. Historical questions like "who attended VBS 2025?" are answered by the event_attendance JSONB data, not by registrations.

DELETE FROM registrations
USING events e
WHERE registrations.event_id = e.id
  AND e.starts_at < cutoff_date;

Operation 3: Membership Archival

Inactive group memberships are archived by merging person IDs into the group's archived_member_ids JSONB column. Only memberships meeting all criteria are eligible:

• membership_status = 'INACTIVE'
• church_group.is_permanent = false
• end_date IS NOT NULL
• end_date < cutoff_date

This preserves a historical record of who was once in each group (queryable via JSONB containment) while removing rows from the relational table.

VACUUM & Disk Reclaim

After the transaction commits, VACUUM FULL runs on the three affected tables:

VACUUM FULL checkins;
VACUUM FULL registrations;
VACUUM FULL group_membership;

PostgreSQL's MVCC (Multi-Version Concurrency Control) model doesn't immediately reclaim disk space when rows are deleted — it marks them as "dead tuples" for later cleanup. VACUUM FULL rewrites the table file entirely, physically reclaiming the space. This takes an exclusive lock on each table, which is acceptable at 3 AM on a Sunday when no users are active.

Scheduling

# Production cron (Ubuntu, runs as postgres user)
0 3 * * 0 sudo -u postgres psql -d myapp \
  -f /var/www/church-app/scripts/archive_old_data.sql \
  >> /var/log/church-archive.log 2>&1

EF Core & Npgsql Integration

The JSONB columns are mapped to List<long> in C# entities. Npgsql 8+ requires explicit opt-in for dynamic JSON serialization:

// Program.cs — required for List<long> JSONB columns
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.EnableDynamicJson();
var dataSource = dataSourceBuilder.Build();

builder.Services.AddDbContext<ChurchCheckInDbContext>(options =>
    options.UseNpgsql(dataSource));

Entity Mapping

// SmallGroupAttendance entity
public class SmallGroupAttendance
{
    public long Id { get; set; }
    public long SmallGroupId { get; set; }
    public DateOnly MeetingDate { get; set; }
    public List<long> AttendeeIds { get; set; } = [];  // ← JSONB
    public string? Notes { get; set; }
    public long? RecordedBy { get; set; }
}

Query Patterns

Per-Person Attendance History (JSONB Containment)

// Repository: Find all events a person attended
public async Task<IEnumerable<EventAttendance>> GetByPersonIdAsync(
    long personId, DateOnly? from, DateOnly? to)
{
    var query = _db.EventAttendances
        .Include(a => a.Event).ThenInclude(e => e.Location)
        .Where(a => EF.Functions.JsonContains(a.AttendeeIds, personId));

    if (from.HasValue) query = query.Where(...);
    if (to.HasValue) query = query.Where(...);

    return await query.OrderByDescending(a => a.CreatedAt).ToListAsync();
}

Upsert Attendance (Save or Update)

// Repository: Save/update attendance for a meeting date
var existing = await GetByDateAsync(smallGroupId, date);
if (existing != null)
{
    existing.AttendeeIds = dto.AttendeeIds;
    existing.Notes = dto.Notes;
    await UpdateAsync(existing);
}
else
{
    await AddAsync(new SmallGroupAttendance { ... });
}

Head Count (No COUNT(*) Needed)

// DTO includes computed HeadCount from array length
HeadCount = attendance.AttendeeIds.Count  // List<long>.Count in C#

API Endpoints

The attendance data is exposed through dedicated REST endpoints:

Proof of Concept: Demo Setup

A comprehensive data generation script (scripts/generate_checkin_demo_data.sql) was created to demonstrate the archival system at scale. The script generates 4 months of realistic church activity data across multiple event types, groups, and attendance patterns.

For full details on the data generation plan, event categories, attendance rates, and group configurations, see the Data Generation Plan document.

Results & Metrics

The archival script was run with a 2-month cutoff against a database with ~5,800 people and 6 months of attendance history (Aug 2025 – Feb 2026). Events older than 2 months were archived.

Archival Operations

Table Size: Before vs. After

Key Achievements

Long-Term Projections

Based on measured results (~225,000 check-ins/year across ~500 events for a mid-size church with ~5,800 people), here are projected impacts over time:

Additional benefits beyond raw storage:

• Faster backups — smaller tables mean faster pg_dump and smaller backup files
• Faster VACUUM — routine maintenance operates on a smaller working set
• Faster kiosk check-in — the checkins table stays small, keeping duplicate detection fast
• Consistent query performance — the hot tables never grow beyond ~2 months of data

Related Documents

Related Scripts