The hardest problem in a 25-day challenge is not day one. Day one is easy because it is new. The hard problem is day eight, when the novelty is gone and there are seventeen days left.
The architecture I built for the AI Advent Challenge was designed specifically to solve day eight.
Sequential door unlocking, a streak system, and a multi-year data model are the three decisions that shaped how 900 participants moved through 25 days of AI skills.
Sequential Unlocking as a Retention Mechanism
The most important architectural decision was also the most constraining one: you cannot skip ahead.
Each door in the AI Advent Challenge has an unlock_at timestamp and an is_active flag. A player can only open a door if the current time is past its unlock_at value and is_active is set to true. The calendar page renders all 25 doors, but only the ones that have passed their unlock time are actually openable. The rest show a locked state with a countdown.
This is not a UX decision. It is a retention mechanism. The alternative, unlocking all 25 doors on day one and letting players move at their own pace, produces a familiar pattern: people consume everything in one session, feel done, and never come back. That is binge-and-abandon. It works fine for passive content. It kills engagement for a challenge that is supposed to build a daily practice.
Sequential unlocking creates a reason to return tomorrow that is architecturally enforced. Not motivational copy. Not a push notification. A database constraint.
The doors Table
The doors table is the core content entity. Each row represents one day’s experience: a lesson, a challenge, and a reward.
Key fields:
✅
day_number— the numeric day (1 through 25)✅
edition— which year this door belongs to (2025, 2026, and so on)✅
unlock_at— the exact UTC timestamp when this door becomes openable✅
is_active— a manual override that lets me enable or disable a door independently of its unlock time✅
lesson_md,challenge_md,reward_md— the three content blocks, stored as markdown✅
contributor_substack_username— comma-separated for doors with multiple contributors
The content fields are append-only. This is a hard rule enforced by convention rather than a database constraint, because SQL constraints interact badly with time-based fields during restores. When I update a door’s content, I merge new material using --- markdown dividers rather than overwriting the existing text. No content that has been published to players is ever deleted from the database. That rule protects the integrity of historical challenge data across multiple editions.
The Multi-Year Edition Model
The 2025 challenge is complete. The 2026 edition is in active development. Both coexist in the same database without any conflicts because every table that holds edition-specific data carries an edition column.
The CURRENT_EDITION environment variable controls which edition is “live” for the purposes of admin analytics, funnel calculations, and new player registration. Changing the edition for a new year is a single environment variable update, not a data migration.
The calendar page shows doors from all editions where is_active = true. This means players who completed the 2025 challenge can still see their history alongside the new 2026 content. They do not lose their progress records when a new year starts. Their streak data, their claims, their certificate, all of it remains intact.
The admin analytics RPCs scope their funnel calculations to CURRENT_EDITION so the pirate metrics dashboard always reflects the active challenge rather than blending historical data from previous years.
The Streak System
The streak system tracks daily engagement without any ambiguity. The streaks table stores three fields per user: current_streak, longest_streak, and last_claim_date.
When a player completes a door, a database trigger fires and updates their streak row. The update is idempotent: completing the same door twice in one day does not increment the streak. Only the first completion of a new day counts.
The streak definition is intentionally simple. A streak increments when you complete any door on a day you have not already completed one. It resets to one if you miss a day entirely. The simplicity is deliberate. Complex streak rules create edge cases that generate support tickets. A simple rule that players can explain to themselves in one sentence creates no confusion.
The longest_streak field serves a specific purpose beyond display. In the pirate metrics dashboard, I use longest_streak >= 3 as the definition of “retained” user. More on that in the metrics post, but the data model decision that makes that calculation possible is here.
The Claims Table
A claims row is written every time a player completes a door. It stores the user, the door, whether they completed the challenge task, their text response, and how long they spent on the door.
The unique constraint on (user_id, door_id) prevents duplicate claims. The idempotency is important because the frontend can be tapped twice on mobile, network retries happen, and users occasionally refresh the page mid-completion.
One row per player per door, regardless of how many times the completion request fires.
The time_spent_seconds field was added after the first edition when I realized I had no signal on whether players were reading the lesson content or just clicking through to claim the reward. Time on page is a proxy for engagement quality, not a perfect signal, but it is better than nothing.
The Contributor Linkage
37 contributors submitted content for the 2025 edition. Getting their authorship attribution right in the data model required one careful design decision.
Contributors are linked to their doors through doors.contributor_substack_username, a comma-separated field. When a contributor signs up for a DraftKit account and enters their Substack URL, a trigger in handle_new_user() parses the username from the URL and matches it against the contributors table. If the match succeeds, the trigger sets contributors.user_id and grants the contributor role in user_roles.
The entire contributor onboarding flow is triggered by a Substack URL match. No manual admin step required for the linkage. I approve the application, send the invite, and the database handles the rest when they sign up.
The AI Discovery Layer
The platform has three AI agent discovery surfaces built in from the start:
✅ JSON-LD in
<head>— structured schema withEvent,Organization, andCoursetypes. All 25 doors are listed asCourseInstanceobjects. AI agents that crawl the landing page get a machine-readable description of the entire challenge structure.✅
/ai.txt— a plain-text capability manifest describing what the platform does, what content it contains, and what AI agents can recommend it for.✅
robots.txtwith explicit allows for GPTBot, ClaudeBot, PerplexityBot, and Google-Extended.
These surfaces are invisible to human visitors. They exist entirely for AI crawlers and answer engines that need to understand the product well enough to recommend it accurately.
Register for the 2026 Edition
The AI Advent Challenge runs every December. 900 people participated in 2025. The 2026 edition opens contributor applications before launch.