How We Track Continuous Membership Days — The Technical Breakdown

Technical companion to Continuous Membership Days in The Numbers That Matter series.

---

This post is the nerd stuff. If you came here from the main article, welcome — here's how continuous membership tracking actually works under the hood.

The core idea: store the anchor, not a counter

The simplest and most efficient approach is to store a single date — the day the current unbroken streak began — rather than maintaining a running counter that increments daily.

membershipStreakStart: DateTime (nullable)

The current streak at any point in time is just:

continuousDays = today - membershipStreakStart

That's it. One date field, one subtraction. No cron jobs incrementing counters overnight, no event replay, no accumulated drift. The calculation is always fresh because it's always computed from the anchor.

Why not a counter?

A daily counter (continuousDays: Integer, incremented nightly) seems simpler at first glance. But it introduces problems:

It requires a reliable nightly job. If the job fails or skips, every member's count is wrong until you detect and repair it. The anchor approach can't drift — the math is always correct regardless of what background jobs did or didn't run.

It's expensive to query by threshold. Finding all members with 365+ days using a counter means scanning every member record. With an anchor date, it's a simple indexed comparison:

SELECT * FROM members
WHERE membership_streak_start IS NOT NULL
  AND membership_streak_start <= NOW() - INTERVAL '365 days'
  AND membership_expires_at >= NOW();

A B-tree index on membership_streak_start makes this near-instant at any scale. The third condition — checking the expiration date — ensures the membership is currently active, not just that a streak started long ago.

It's harder to audit. If a member disputes their count, an anchor date is trivially verifiable. A counter requires reconstructing history to prove it's correct.

Handling lapses and grace periods

The anchor only works if you maintain it correctly. Two additional fields handle the edge cases:

membershipStreakStart:     DateTime?  -- null = no active streak
membershipGracePeriodEnd:  DateTime?  -- null = membership is current
longestStreakDays:          Integer    -- high-water mark, denormalized

On membership activation (new signup or reactivation): if membershipStreakStart is null, set it to today.

On membership expiration or payment failure: set membershipGracePeriodEnd to the current time plus the club's configured grace period (e.g., 3-7 days). The streak is still alive during grace.

On renewal during grace period: null out membershipGracePeriodEnd. The streak continues unbroken. The member never knew anything happened.

On grace period expiration without renewal: this is the lapse event. Before nulling the anchor:

1. Calculate the completed streak: gracePeriodEnd - membershipStreakStart
2. Write it to a history table: streak_history(member_id, streak_start, streak_end, total_days)
3. Update longestStreakDays if this streak exceeded the previous record
4. Set membershipStreakStart = null

The lapse check can be a lightweight scheduled job or a delayed job queued at the moment of expiration — whichever fits your infrastructure.

The history table

CREATE TABLE streak_history (
  id            UUID PRIMARY KEY,
  member_id     UUID REFERENCES members(id),
  streak_start  TIMESTAMPTZ NOT NULL,
  streak_end    TIMESTAMPTZ NOT NULL,
  total_days    INTEGER NOT NULL,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

This serves three purposes: member-facing "longest streak" displays, analytics on churn patterns (how long do streaks typically last before breaking?), and win-back targeting (members with long historical streaks are high-value reactivation candidates).

Querying for incentive programs

The anchor approach makes threshold-based queries trivial:

Members eligible for the 500 Lounge (500+ active days):

WHERE membership_streak_start <= NOW() - INTERVAL '500 days'
  AND membership_streak_start IS NOT NULL
  AND membership_expires_at >= NOW()

Members approaching a milestone (e.g., within 7 days of 365):

WHERE membership_streak_start BETWEEN
    NOW() - INTERVAL '365 days'
    AND NOW() - INTERVAL '358 days'
  AND membership_streak_start IS NOT NULL
  AND membership_expires_at >= NOW()

Members whose streak broke in the last 30 days with a previous streak over 200 days (win-back candidates):

SELECT * FROM streak_history
WHERE streak_end >= NOW() - INTERVAL '30 days'
  AND total_days >= 200;

All of these are index-friendly and performant.

Real-time access control (the smart lock scenario)

For gating physical access based on streak — like a lounge that only opens for 500+ day members — the check at the lock is:

1. NFC bracelet tap → look up member
2. Read membership_streak_start
3. If null → deny
4. If today - membership_streak_start < 500 → deny
5. If membership_expires_at < today → deny
6. Otherwise → unlock

That's three field reads and two comparisons. No aggregation, no history scan, no external service call. Fast enough for real-time access control on a door lock.

Extending the pattern: visit streaks

The same anchor approach works for visit-based streaks (e.g., consecutive weeks with at least one visit):

visitStreakAnchorWeek:  Integer?  -- ISO week number when streak began
visitStreakYear:        Integer?  -- year of anchor week

On each check-in, compare the current ISO week to the last recorded visit week. If the gap is exactly one week, the streak continues. If it's the same week, no action needed. If the gap is two or more weeks, the streak breaks — snapshot to history and reset the anchor.

---

Back to the main article: Continuous Membership Days — The Metric Your Club Should Be Tracking