Reddit Demand Signal Routine

Reddit Demand Signal Routine — Technical Specification #

Version: Final · Status: For execution by an AI coding agent · Scope: A new daily routine added to an existing, already-authenticated Reddit bot agent.

Overview #

This document specifies the Reddit Demand Signal Routine — a scheduled routine that is added to a Reddit bot agent that already exists, is already logged in to Reddit, and already has access to Notion, a Big Brain knowledge skill, and an inter-agent message channel shared with peer bots (a chief-of-staff, an X bot, a Substack bot, and a group of prospector agents).

Every day at 06:00 America/New_York, the routine harvests the Reddit communities the operator's account is subscribed to, extracts the unmet needs people state in their own words, clusters those needs into durable themes, scores each theme for recurrence rather than virality, and publishes the survivors — with evidence, a content angle, a recommended format, and a recommended platform — into a Notion page called Reddit Signal that lives under the operator's existing Demand Signal page.

Three properties distinguish it from a generic Reddit scraper:

1. It is filtered through a confirmed lens. Before it publishes anything, the routine derives a structured model of the operator's unique value proposition — their psychological-operations perspective — from their own email, their X posts, their Substack essays, the Big Brain skill, their Reddit history, and the accumulated understanding of the peer bots. It then proposes that model in chat and waits for the operator to confirm or correct it. Demand the operator cannot uniquely serve is treated as noise.
2. It prefers recurrence to trends. The scoring model rewards themes that reappear across many days and many communities, and actively penalizes single-day spikes through an explicit burstiness multiplier and a set of hard promotion gates. The routine will miss a viral moment rather than fill the board with signal that evaporates.
3. It manages its own community portfolio. The routine discovers, evaluates, joins, and leaves subreddits on its own judgment, with no cap on how many it may be subscribed to and no human approval gate — every action logged with the numbers that drove it.

It then keeps correcting itself: as the operator publishes on X and Substack, the routine attributes that work back to the themes that suggested it, updates the weighting of the lens, detects drift, and proposes amendments in chat.

The document is written to be executed cold. Every default is a decision that has already been made, every threshold is a real number with a rationale, and every external interface is specified down to the request body. Sections 5, 6, 13, and 19 are normative reference material — the data model, the configuration keys, the scoring derivation, and the error taxonomy — and should be consulted directly rather than recalled.

What this routine deliberately does not do: it does not build its own X or Substack scrapers (it asks the peer bots), it does not draft or publish content, it does not post, comment, vote, or message on Reddit, it takes no write action in the operator's inbox, and it does not run in real time.

Table of Contents #

1. Before You Start — Customization Decisions
2. Project Overview and Vision
3. Technology Stack and Architecture
4. Conventions, Code Standards, and Repository Layout
5. Data Model and Persistence Schema
6. Configuration Reference
7. The Lens Model — Identity, Value Proposition, and Confirmation
8. Cross-Bot Coordination and the Agent Message Contract
9. Identity Corpus Ingestion (Email, X, Substack, Big Brain, Reddit History)
10. Reddit Ingestion Subsystem
11. Subreddit Membership Management
12. Demand Extraction — From Raw Reddit Content to Demand Units
13. Theme Clustering and the Recurrence Scoring Model
14. Content Angle, Format, and Platform Recommendation
15. Notion Output — The Reddit Signal Subpage
16. Chat Interaction and Confirmation Protocol
17. Continuous Lens Refinement and Feedback Loops
18. Scheduling, Run Lifecycle, and Orchestration
19. Error Handling, Retries, and Rate Limiting
20. Observability, Logging, and Run Reporting
21. Security, Privacy, and Platform Compliance
22. Testing Strategy and Quality Gates
23. Performance, Cost, and Capacity Budgets
24. Milestones and Execution Plan
25. Executor Instructions
26. Appendices

1. Before You Start — Customization Decisions #

This section exists so that nothing in this build ever stalls on a question. Every decision below is already made, and every default is already applied in the configuration that ships with the routine. Read the tables, build against the defaults, and change a row only if the operator explicitly tells you to. If the operator is unavailable, the defaults stand and you proceed.

Each row names the configuration key that controls the behavior. Section 6 is the normative reference for key names, types, precedence, and validation; this section only tells you which key to reach for and what value it already holds. Every key named here exists in Section 6 under exactly this name and with exactly this default.

1.1 How to use this section #

1. Skim Section 1.2.1 end to end before writing any code. It is the shortest complete description of what the operator will experience. Section 1.2.2 is the same exercise for the decisions the operator will never think about.
2. Build with the defaults. Do not add configuration switches that are not in Section 6.
3. If the operator overrides a row, record the override in docs/DECISIONS.md in the repository using the format in Section 1.4, then change the value in the configuration file — never in code.
4. Never hard-code a value from these tables into a source file. Every one of them is a tunable constant and must live in configuration (Section 4.12, rule RDSR-CON-135).

Requirement IDs in this section use the prefix RDSR-PRE-###. IDs are allocated once and never reused; a gap in the sequence means a decision was withdrawn, not that a row is missing.

1.2 Customization decisions #

The table is split in two. Section 1.2.1 is the operator's table — every row changes something the operator sees, receives, or is answerable for. Section 1.2.2 is the builder's table — every row is a default the operator may accept without ever being asked.

1.2.1 Operator decisions #

1.2.2 Build defaults you may accept without asking #

1.3 What you need before you start #

This is a preconditions checklist, not a provisioning guide. Every item below is expected to already exist on the host agent. If one is missing, that is an operator task, not a build task — record it, report it in chat, and continue building against the fallback noted.

rdsr doctor is the single command that checks all of the above. Run it first, run it after any configuration change, and run it as the first step of any incident triage. Its exit codes are listed in Section 3.9.

On a genuinely first install, several of these checks describe preconditions that later bootstrap steps create — there is no Reddit Signal page to write to before the page tree is built, and no subscriptions to count before the first snapshot. rdsr doctor --bootstrap runs the reduced set that applies at that moment (configuration, secrets, database, disk, and clock); Section 18.10 fixes the order of the first run and re-runs the full blocking set once the preconditions exist.

1.4 Recording a deviation #

If the operator overrides any default in Section 1.2, append an entry to docs/DECISIONS.md before changing the configuration:

## D-014 — Membership join pacing raised to 9 per day

- **Date:** 2026-03-04
- **Supersedes:** RDSR-PRE-020 default (3 joins/day)
- **Decided by:** operator
- **Rationale:** initial listening set is far smaller than the addressable set; a faster ramp
  is acceptable for the first two weeks. This changes the rate at which the routine adds
  communities; it does not change the fact that there is no cap on how many it may hold and
  no approval step before it adds one.
- **Config change:** `membership.joinsPerDay: 3 -> 9`
- **Revert condition:** any `RDSR_REDDIT_RATE_LIMITED` on a membership call, or 2026-03-18,
  whichever comes first.

Every deviation gets an ID, a rationale, the exact key change, and a revert condition. A deviation without a revert condition is a permanent decision and must say so explicitly.

2. Project Overview and Vision #

2.1 The problem in one page #

The operator publishes on X and on Substack. Their differentiating perspective is psychological operations in the analytic sense: influence mechanics, narrative framing, cognitive bias, information-environment analysis, and the ethics of persuasion. That perspective is genuinely scarce. What is not scarce is the daily question of what to point it at.

Today that question is answered by one of two bad methods. The first is introspection — write whatever seems interesting this morning — which produces uneven relevance and no compounding. The second is trend chasing — watch what is spiking, write into the spike — which produces content that is maximally competitive at the exact moment it is least differentiated, and which decays to zero value within days. Both methods share the same defect: they contain no evidence that any specific human ever wanted the thing being written.

Reddit is unusual among public platforms in that people go there to state what they need, in their own words, in the form of a question, a complaint, or a request for help. Those statements are timestamped, attributed to a community, threaded with attempted answers, and retrievable through an authenticated API. When a need is genuinely unmet, the thread shows it: the question gets asked again next week in a different subreddit, the top answer is contested, the accepted answer is a link to something that does not actually answer it.

The gap this routine fills is the disciplined pipeline between those two facts. It takes the stated needs of the communities the operator's account already follows, extracts them into structured demand units, clusters them into themes, and then applies two filters that a generic listening tool cannot: durability — does this need keep recurring across days and across communities, or was it one thread that got popular — and lens fit — is this a question that this specific person, with this specific psychological-operations perspective, can answer better than the median commentator.

What comes out is a short, ranked, evidenced list. Not topics. Not keywords. Specific articulated demands, each carrying the excerpts that prove it exists, an angle that only this operator would take, a recommended format, and a recommended platform. It lands in Notion every morning at 06:00 Eastern without being asked.

The routine is deliberately narrow. It does not write. It does not post. It does not recommend a posting schedule. It answers one question — what is worth writing about, and why do we believe that — and it answers it with evidence.

2.2 What the routine is #

In one sentence: every morning, harvest the communities the account follows, extract the demand stated in them, cluster it into recurring themes, score those themes for durability and lens fit, publish the survivors to Notion with evidence and angles, adjust the account's community memberships, and report in chat.

On reading versus joining. The routine does not only read communities it has joined. It also samples a small number of public subreddits it has not joined, at reduced depth, so that a join decision is made on evidence rather than on a guess. Reading a public community's listings requires no membership and no write action of any kind. Joining is what commits the routine to sustained, full-depth coverage of a community — and to counting it as part of the operator's own real subscription list. Every subscribed community is harvested every run unless it is NSFW-flagged, on safety.excludedSubreddits, or operator-blocked; any subscribed community that produces zero documents is named, with its reason, in the run report's coverage section.

The routine executes as 17 stages in a fixed order. Each stage is checkpointed; a run that fails at stage 12 resumes at stage 12 rather than at stage 1. Section 18 owns the lifecycle, resumption semantics, and stage-level contracts. This table is orientation only — one line each.

The canonical order is fixed and is referenced by that exact spelling everywhere in this document:

preflight → lens_resolve → peer_sync → membership_snapshot → harvest → normalize →
candidate_filter → extract → embed → cluster → score → select → enrich → notion_publish →
membership_actions → chat_digest → finalize

2.3 Goals and non-goals #

Goals.

1. RDSR-OVW-001 — Durable demand over ephemeral trends. The system's primary output is the set of needs that keep coming back. A single-day spike is treated as weaker evidence than the same volume spread over eleven days, and the scoring model enforces that arithmetically rather than editorially.
2. RDSR-OVW-002 — Evidence for every claim. No theme reaches Notion without verbatim excerpts, their subreddit, their permalink, and their timestamp. The operator must be able to click through from any assertion to the thread that carries it.
3. RDSR-OVW-003 — The lens as a first-class filter, not a post-hoc garnish. Lens fit is a weighted scoring component with a hard gate on Core promotion. A theme with enormous demand that the operator has no distinctive claim on does not become a Core theme.
4. RDSR-OVW-004 — Autonomous community management. The routine decides which subreddits to listen to, joins the promising ones, and leaves the ones that have stopped producing signal, without asking. Its listening set is expected to look materially different after sixty days than it did on day one.
5. RDSR-OVW-005 — A Notion page a human actually wants to read. Ranked, bounded, skimmable, with the reasoning visible but collapsed. The output is a briefing, not a data dump.
6. RDSR-OVW-006 — Continuous self-correction. What the operator actually publishes, and how it performs, feeds back into the lens and into scoring. The system's model of the operator improves without the operator being asked to maintain it.
7. RDSR-OVW-007 — Unattended operation. The steady state is that nobody touches it. The routine reports, degrades, retries, and recovers on its own; human attention is required only for lens confirmation and for genuine credential failures.
8. RDSR-OVW-008 — Reproducibility. Any published ranking can be recomputed from stored inputs and produce the same ordering. Rankings are auditable, not vibes.
9. RDSR-OVW-009 — Work that earns sharing and durable audience growth. The operator asked for content that supports growth, sharing, and account growth, and this system optimizes for the input that produces it: demand that is real, unmet, recurring, and uniquely servable by this operator — the kind of thing people send to someone else because it answered a question they had already given up on. The routine measures demand and recommends; it does not promise virality and it does not optimize for reach directly. Realized engagement enters only as a low-weight prior over format and platform choice (Section 17.1.2), never into the Recurrence Score and never into the lens, because optimizing a positioning instrument for reach turns it into the trend tracker this whole system exists to avoid. Whether the published work actually grew the audience is reported as a success metric (RDSR-OVW-031), not fed back as a target.

Non-goals.

1. RDSR-OVW-010 — Writing the content. The routine produces a demand brief. Drafting is the operator's work, possibly assisted by other agents, and is out of this system's scope.
2. RDSR-OVW-011 — Posting anywhere. No publishing to X, Substack, or Reddit. The routine's only writes are to Notion, to its own database, to the chat channel, and to the operator's own Reddit subscriptions.
3. RDSR-OVW-012 — Real-time alerting. There is no streaming path and no "breaking signal" notification. A system whose thesis is that recurrence beats spikes has no business optimizing for latency.
4. RDSR-OVW-013 — Scraping X or Substack. Those corpora are obtained by asking x-bot and substack-bot. The routine never fetches those platforms directly.
5. RDSR-OVW-014 — Being a general Reddit analytics tool. No subreddit dashboards and no engagement analytics beyond the single reported attribution metric in Section 2.6 (RDSR-OVW-031). Every other feature exists to serve the demand brief.
6. RDSR-OVW-015 — Multi-user support. One operator, one lens, one Notion destination, one Reddit identity. There is no tenancy model and none should be added.
7. RDSR-OVW-016 — Credential provisioning. The Reddit, Notion, Big Brain, and message bus credentials already exist. This routine reads them from the existing secret store and does nothing else with them.

2.4 Explicitly out of scope #

2.5 Users and stakeholders #

One identity. The routine authenticates as the operator's own Reddit account. It is not a separate bot account and one must not be provisioned. mine in /subreddits/mine/subscriber means the operator's real subscriptions; a join or a leave changes the operator's own memberships and is visible on their own profile; /user/{name}/ overview reads the operator's own history. This is the premise of the whole product — the routine listens to the communities the operator actually belongs to — and it is also why the account-safety discipline in Section 21.6 matters: the only account at risk is the operator's.

Two facts about peers govern all of Section 8. First, the message bus internals are unknown, so every peer interaction goes through the AgentBus adapter defined in Section 3.5 with the filesystem drop-box fallback in Section 8.6. Second, every peer is optional. A run with zero peer replies completes successfully with a recorded corpus-confidence penalty. No peer is ever on the critical path.

2.6 Success metrics #

These are the metrics the routine reports on and the operator judges it by. Each is computed from stored data with no manual bookkeeping. Table and column names below are defined normatively in Section 5; they appear here so each metric has an unambiguous computation.

Three of these deserve emphasis. Theme survival rate is the metric that tells you whether the product thesis is holding: if Core themes routinely evaporate in two weeks, the scoring model is behaving like a trend tracker and the burstiness penalty needs re-tuning. False-spike rate is its adversarial twin: it catches the specific failure of a viral thread dragging a theme through the Core gates on volume alone. Growth follow-through is the one metric the routine reports but deliberately does not chase — it exists so that "did this grow the account" has an answer, without that answer ever becoming an input to the score.

rdsr report --metrics prints all of these for the last 30 days and is the intended way to review them.

2.7 The signal philosophy #

The scoring model in Section 13 is not a neutral ranking function that happens to have weights. It is an argument about how a creator builds durable authority, expressed as arithmetic. This subsection states the argument so that whoever tunes those weights later knows what they are tuning against.

A trend is a distribution of attention with a short half-life and a very large number of simultaneous suppliers. When something spikes, three things become true at once: demand is temporarily high, supply is about to become overwhelming, and the window closes before most suppliers finish producing. Writing into a spike means competing at the moment of maximum competition with the least differentiated possible take, for an audience that will have forgotten the topic within a fortnight. The economics are bad even when the execution is good. Worse, spike-driven content does not compound: nothing written about last month's discourse cycle is worth linking to this month.

A recurring need has the opposite shape. If people in four different communities keep asking a version of the same question across three weeks, several things follow. The demand is real rather than reflexive. It is not being met — if it were, the question would stop being asked. Supply is thin, because thin supply is precisely what causes repetition. And the demand will still be there next month, which means the thing you write has a shelf life measured in quarters rather than days. Content aimed at recurring needs accumulates: it gets linked, it gets cited back, it becomes the thing people are pointed to when the question is asked again. That accumulation is authority, and it is also the only durable form of audience growth — which is why RDSR-OVW-009 treats sharing and account growth as the consequence of serving recurring demand well, rather than as a target to be optimized directly.

Two more properties matter for this specific operator. First, a psychological-operations lens is an analytic lens, and analysis takes time to produce and time to read; it is structurally mismatched with a 48-hour attention window. Second, that lens is most valuable on questions that people keep circling without resolving — persistent confusion is very often a sign that the conventional framing is wrong, and reframing is exactly what this operator does well. Recurrence detection and lens fit are therefore not two independent filters that happen to be combined; they point at the same class of opportunity.

The belief is encoded in four specific mechanisms, all of which Section 13 derives in full:

1. The burstiness penalty. The Recurrence Score multiplies the raw component score by (1 − 0.45 × burstiness). A theme whose evidence is concentrated into a single day loses nearly half its score no matter how large the volume. This is the single most important line in the model.
2. Persistence carries the largest single weight (0.22) — more than volume, intensity, and differentiation combined (0.20). The model would rather have a steady trickle than a flood.
3. Volume is deliberately the joint-smallest component (0.05). Volume is what a trend tracker maximizes; here it is a tiebreaker.
4. The Core gates are conjunctive and time-shaped. core requires active_days ≥ 4, distinct_subreddits ≥ 2, and span_days ≥ 10 in addition to RS ≥ 0.62 and L ≥ 0.55. No amount of score compensates for a theme that has only existed for three days or only appeared in one community. A spike physically cannot satisfy span_days ≥ 10.

The corollary is that this system will miss things. It will not tell the operator about the story everyone is talking about today. That is a design decision, not a limitation: the operator already knows what is spiking, because everyone does. What nobody has is the list of questions that four hundred people asked over three weeks and nobody answered well.

2.8 Worked end-to-end example #

The following walk-through is invented for illustration. Quoted text is fabricated, kept under 40 words per quote in line with the evidence conventions in Section 4.9, and is not drawn from any real post. Every count in it sits inside the caps in Section 1.2.2 and matches the capacity model in Section 23.

Run run_20260311_7QK4ZB, 06:00:04 America/New_York.

preflight acquires the run lock, confirms the operator's Reddit token carries identity, read, mysubreddits, subscribe, and history, resolves the Demand Signal parent page to a single match, and applies no migrations. lens_resolve loads lens_v4, confirmed nine days earlier, whose pillars include "why credible information fails to persuade" and "how framing survives fact-correction."

peer_sync gets three replies inside the 45-second budget: chief-of-staff reports the operator is preparing a talk on institutional trust; substack-bot reports two essays published in the last 14 days, one on source credibility; x-bot reports 31 posts, with the highest-engagement one about a fact-check that backfired. The prospectors broadcast returns nothing before the deadline and is recorded as a zero-reply broadcast, not an error.

membership_snapshot reconciles a 41-community portfolio: 37 subscriptions — 12 core, 19 active, 6 probation — plus 4 candidate communities discovered on previous runs and sampled at reduced depth without being joined.

harvest issues 524 Reddit requests across all 41 communities, which at the sustained 90 requests per minute takes about 350 seconds and finishes comfortably inside the 420-second stage budget. It fetches 3,140 posts and 8,588 comments — 11,728 documents, against the 12,000-document per-run cap. normalize collapses 886 of those to existing rows by body_hash equality and stores 10,842 documents, flagging 108 non-English documents as stored but excluded from extraction. candidate_filter passes 1,301 documents to extraction against the 1,400 cap, recording the rest as rejected candidates with a reason — link-only posts, single-emoji comments, pure agreement replies — so the extraction-yield metric stays honest.

extract produces 634 demand units, a yield of 487 per 1,000 candidates, inside the 300–650 band in RDSR-OVW-027. Twenty-three of them, spread across four subreddits, are typed explainer_gap or credibility_dispute and orbit the same articulated need. Three representative excerpts:

"I sent my dad the correction from a source he trusts and he just got more sure he was right. What actually happened in his head there?" — invented example, r/example_community_a, 2026-03-09

"Everyone says 'just show them the evidence.' I have shown them the evidence eleven times. Nobody explains why that makes it worse." — invented example, r/example_community_b, 2026-03-05

"Is there a name for when debunking something makes people believe it harder? I keep looking and only finding arguments about whether it's real." — invented example, r/example_community_c, 2026-03-02

embed computes 634 vectors at 1536 dimensions. cluster matches 19 of the 23 units to an existing theme, thm_01JQ4YB8N2C7VXM6R0KDPZ3TFE, first created on the run of 2026-02-28, and attaches the remaining 4 as new evidence.

score computes the theme's components for the 14-day window: Breadth B = 0.71 (4 distinct subreddits, none dominating), Persistence P = 0.78 (evidence on 8 of the last 14 days, span_days = 11), Unmet need U = 0.83 (top replies are contested; several threads have no substantive answer), Lens fit L = 0.88 — computed once for the theme by the lens-fit function in Section 7.7, never per demand unit — Intensity I = 0.52, Volume V = 0.34, Differentiation D = 0.69. Raw score is 0.20(0.71) + 0.22(0.78) + 0.18(0.83) + 0.20(0.88) + 0.10(0.52) + 0.05(0.34) + 0.05(0.69) = 0.142 + 0.1716 + 0.1494 + 0.176 + 0.052 + 0.017 + 0.0345 = 0.7425. Burstiness is 0.19 — evidence is well spread — so the penalty multiplier is 1 − 0.45(0.19) = 0.9145, and the recency factor is 0.97. RS = 0.7425 × 0.9145 × 0.97 = 0.6586.

The Core gates are all satisfied: RS 0.659 ≥ 0.62, active_days 8 ≥ 4, distinct_subreddits 4 ≥ 2, span_days 11 ≥ 10, L 0.88 ≥ 0.55. The theme promotes from emerging to core on day 11 of its life. Under a volume-weighted model it would have promoted on day 2 and been wrong; under this model it waited until the evidence justified it.

select ranks it first. This run creates 1 new core entry (cap 3), 4 new emerging (cap 6) and 7 new watchlist (cap 10); nine further themes are considered and cut, each with a recorded exclusion reason. The board that results carries 38 live entries — 11 core, 15 emerging, and 12 watchlist — well inside the 60-row watchlist ceiling. enrich generates the angle — the backfire effect framed as an identity-defense mechanism rather than a reasoning failure, with the practical consequence that correction targeting the claim is structurally the wrong move — recommends the format substack_essay, and recommends the platform substack, on the grounds that the demand units are long-form questions asking for mechanism, not for a take.

notion_publish upserts the entry into the Reddit Signal subpage under Demand Signal: the theme name, status core, RS 0.66, the seven components in a collapsed block, the three excerpts above with permalinks and dates, the angle, the format, the platform, and the note that this theme has been live for 11 days and has strengthened for 6 consecutive runs.

membership_actions joins r/example_community_c, which had been a candidate for six days and has now contributed evidence to three separate Core themes, and leaves a subreddit that has produced no demand unit in 28 days. Both fit inside the day's pacing budget of 3 joins and 2 leaves, are spaced 41 seconds apart by the randomized 20–90 second spacing, and complete well inside the 300-second stage budget; nothing defers to tomorrow.

chat_digest sends one message at 06:20 — outside the 22:00–06:00 quiet window, and exempt from it in any case: 38 themes live on the board, 1 new Core promotion, 1 join, 1 leave, 4 themes decayed to dormant, no confirmations needed. finalize writes totals — 524 Reddit requests, about 1,000,000 chat tokens and 420,000 embedding tokens, $2.41 of model spend against the $8.00 ceiling — applies the 90-day body-retention prune, and closes the run as succeeded at 06:20:39, 20 minutes and 35 seconds end to end.

2.9 Glossary pointer #

Every term used with a specific technical meaning in this document — demand unit, theme, lens, burstiness, tier, evidence span, and the rest — is defined in the glossary in Section 26.

3. Technology Stack and Architecture #

3.1 Stack table #

Version lines below are stated as major lines only. This is the only section of this document that states version numbers; everywhere else, dependencies are named without them.

On snoowrap. The Reddit client is written in-repo, as a thin typed wrapper over fetch against https://oauth.reddit.com. snoowrap is deliberately not used because it is unmaintained; depending on an unmaintained client for the routine's single most important data source would put the whole system's viability behind someone else's abandoned release schedule. The in-repo client is roughly 400 lines, exposes exactly twelve methods, covers the twelve endpoints the routine needs, and is specified in Section 10.3. Exactly one of those twelve methods writes anything to Reddit.

3.2 Version policy #

These version lines are a known-good floor, not a lockfile. At build time, install the current stable release of each dependency (npm install <pkg>@latest, or your ecosystem's equivalent), confirm the major line still matches, and let the lockfile record the exact resolved versions.

Two additions specific to Notion. The Notion API version header that the installed SDK sends must be read from the installed SDK and confirmed against the value configuration ships in notion.apiVersion (Section 6), rather than re-derived from this document, because Notion's 2025-09-03 API version and later model a database as a container of data sources, and queries target a data_source_id rather than a database id — a structural change that determines the shape of every query the routine issues. Any version earlier than the one Section 6 ships does not support data sources and is not supported by this routine. Verify the header value and the current request/response shapes against the official Notion API documentation on day one of the build, record the confirmed value in configuration, and record the verification date in docs/DECISIONS.md.

The routine's own outbound identity is likewise never a literal in this document. The Reddit User-Agent is rendered at runtime from a template that interpolates the application version read from the package manifest and the username read back from the identity call (Section 10); no source file, example, or log line carries a hard-coded version.

3.3 Runtime and platform assumptions #

3.4 Architecture overview #

                              ┌───────────────────────────────┐
                              │        SchedulerHost          │
                              │  host scheduler (preferred)   │
                              │  or in-process cron fallback  │
                              └───────────────┬───────────────┘
                                              │ fires 06:00 America/New_York
                                              ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                            Run Orchestrator                                  │
│         run lock · checkpointing · resume · degradation · budgets            │
│                                                                              │
│   1 preflight ──▶ 2 lens_resolve ──▶ 3 peer_sync ──▶ 4 membership_snapshot   │
│        │                                                          │          │
│        ▼                                                          ▼          │
│   5 harvest ──▶ 6 normalize ──▶ 7 candidate_filter ──▶ 8 extract             │
│        │                                                          │          │
│        ▼                                                          ▼          │
│   9 embed ──▶ 10 cluster ──▶ 11 score ──▶ 12 select ──▶ 13 enrich            │
│        │                                                          │          │
│        ▼                                                          ▼          │
│   14 notion_publish ──▶ 15 membership_actions ──▶ 16 chat_digest             │
│        │                                                                     │
│        ▼                                                                     │
│   17 finalize                                                                │
└───────┬──────────────┬───────────────┬───────────────┬──────────────────────┘
        │              │               │               │
        │              │               │               │
  ┌─────▼─────┐  ┌─────▼──────┐  ┌─────▼──────┐  ┌─────▼───────┐
  │RedditClient│  │NotionGateway│  │  AgentBus  │  │ LLMProvider │
  │   PORT     │  │    PORT     │  │    PORT    │  │    PORT     │
  └─────┬─────┘  └─────┬──────┘  └─────┬──────┘  └─────┬───────┘
        │              │               │               │
        ▼              ▼               ▼               ▼
  ┌───────────┐  ┌───────────┐  ┌─────────────┐  ┌──────────────┐
  │ Reddit    │  │ Notion    │  │ Agent       │  │ Model        │
  │ OAuth API │  │ API       │  │ Message Bus │  │ endpoint     │
  │ EXTERNAL  │  │ EXTERNAL  │  │ (or drop-   │  │ (host access │
  │           │  │           │  │  box files) │  │  by default) │
  └───────────┘  └───────────┘  └─────────────┘  └──────────────┘

  ┌──────────────────────────────┐     ┌──────────────────────────────┐
  │   SQLite (WAL)               │     │  Embedding index (in-memory, │
  │   INTERNAL STORE             │◀───▶│  Float32Array, rebuilt from  │
  │   runs · run_stages ·        │     │  the embeddings table)       │
  │   documents · candidates ·   │     │  INTERNAL STORE              │
  │   demand_units · themes ·    │     └──────────────────────────────┘
  │   theme_members · subreddits │
  │   · lens_profiles            │
  └──────────────────────────────┘

  ┌──────────────┐                     ┌──────────────┐
  │ SecretStore  │  PORT — read-only   │SchedulerHost │  PORT — job
  │              │  credential access  │              │  registration
  └──────────────┘                     └──────────────┘

Four external boundaries: Reddit API, Notion API, Agent Message Bus, LLM Provider. Two internal stores: SQLite and the embedding index. Two further ports that are not data boundaries but are isolation boundaries: SecretStore and SchedulerHost.

Module map. Every directory under src/ maps to exactly one responsibility and one owning section.

3.5 The port-and-adapter boundary #

Six interfaces isolate the routine from everything it does not own. Every one of them has at least two implementations: the real one and a deterministic test double. No code outside src/reddit/, src/notion/, src/agents/, src/llm/, src/corpus/providers/, and src/config/ may import a vendor SDK or issue a network call. Two of the corpus providers — src/corpus/providers/x.ts and substack.ts — are inside that list only for symmetry; they issue no network call of their own and delegate entirely to AgentBus.

These signatures are normative. Implementations belong to the owning sections; this is the contract they implement against.

// src/util/ports.ts — shared call options used by every port.

/** Every outbound call carries a deadline and a cancellation signal. Section 4.7. */
export interface CallOpts {
  readonly signal?: AbortSignal;
  readonly timeoutMs?: number;
  /** Correlates the call with a run and stage in logs and cost accounting. */
  readonly ctx?: { readonly runId: RunId; readonly stage: StageName };
}

export interface Page<T> {
  readonly items: readonly T[];
  /** Opaque cursor. `undefined` means the caller has reached the end. */
  readonly cursor?: string;
}

3.5.1 RedditClient #

The port has exactly twelve methods, of which exactly one — subscribe — mutates anything on Reddit. That is the whole write surface of this product against the operator's account, and it is why the account-safety argument in Section 21.6 is short. Section 10.2 restates this interface with the Reddit-specific request and response types; the method names, their count, and the single mutator are identical in both places.

// src/reddit/client.ts

export type SubredditKey = string & { readonly __brand: 'SubredditKey' };
export type Fullname = string & { readonly __brand: 'Fullname' }; // t3_… | t1_…

export type ListingSort = 'new' | 'hot' | 'top' | 'rising';
export type ListingWindow = 'hour' | 'day' | 'week' | 'month' | 'year' | 'all';
export type CommentSort = 'top' | 'new' | 'confidence';

export interface RateLimitSnapshot {
  readonly used: number | null;          // X-Ratelimit-Used
  readonly remaining: number | null;     // X-Ratelimit-Remaining
  readonly resetSeconds: number | null;  // X-Ratelimit-Reset
  readonly observedAt: number;           // epoch ms
  /** Set when the last response carried Retry-After. */
  readonly retryAfterMs?: number;
}

export interface RedditIdentity {
  readonly username: string;
  readonly scopes: readonly string[];
  readonly createdUtc: number;
}

export interface SubredditSummary {
  readonly key: SubredditKey;
  readonly displayName: string;
  readonly subscribers: number;
  readonly over18: boolean;
  readonly subredditType: 'public' | 'restricted' | 'private' | 'gold_only' | 'archived';
}

export interface SubredditAbout extends SubredditSummary {
  readonly title: string;
  readonly publicDescription: string;
  readonly activeUserCount?: number;
  readonly submissionType: 'any' | 'link' | 'self';
  readonly quarantine: boolean;
}

export interface RedditPost {
  readonly fullname: Fullname;
  readonly subreddit: SubredditKey;
  readonly title: string;
  readonly selftext: string;
  /** Raw username, used once and only to derive author_hash. Never persisted. Section 5.3. */
  readonly author: string;
  readonly createdUtc: number;
  readonly score: number;
  readonly upvoteRatio: number;
  readonly numComments: number;
  readonly permalink: string;
  readonly over18: boolean;
  readonly linkFlairText?: string;
  readonly isSelf: boolean;
  readonly locked: boolean;
  readonly removed: boolean;
}

export interface RedditComment {
  readonly fullname: Fullname;
  readonly parentFullname: Fullname;
  readonly linkFullname: Fullname;
  readonly subreddit: SubredditKey;
  readonly body: string;
  /** Raw username, used once and only to derive author_hash. Never persisted. */
  readonly author: string;
  readonly createdUtc: number;
  readonly score: number;
  readonly depth: number;
  readonly permalink: string;
  readonly isSubmitter: boolean;
}

export interface CommentTree {
  readonly comments: readonly RedditComment[];
  /** Unexpanded `more` stubs, expanded by getMoreChildren when the budget allows. */
  readonly moreIds: readonly string[];
  readonly truncated: boolean;
}

export interface SubscribeResult {
  readonly subreddit: SubredditKey;
  readonly action: 'sub' | 'unsub';
  /** Result of the confirmation read-back described in Section 11.5. */
  readonly confirmed: boolean;
}

export interface RedditClient {
  /** 1. GET /api/v1/me — the credential and scope check used by preflight and doctor. */
  getMe(opts?: CallOpts): Promise<RedditIdentity>;

  /** 2. GET /subreddits/mine/subscriber — full pagination handled internally. */
  listSubscriptions(opts?: CallOpts): Promise<readonly SubredditSummary[]>;

  /** 3. GET /r/{sub}/{listing} — one page per call; the planner drives the cursor. */
  getListing(
    sub: SubredditKey,
    params: {
      readonly sort: ListingSort;
      readonly limit: number;
      readonly cursor?: string;
      readonly window?: ListingWindow;
    },
    opts?: CallOpts,
  ): Promise<Page<RedditPost>>;

  /** 4. GET /r/{sub}/comments/{linkId} — flattened to the configured depth. */
  getComments(
    sub: SubredditKey,
    link: Fullname,
    params: { readonly maxDepth: number; readonly limit: number; readonly sort: CommentSort },
    opts?: CallOpts,
  ): Promise<CommentTree>;

  /** 5. POST /api/morechildren — expands `more` stubs; chunked at 100 ids per call. */
  getMoreChildren(
    link: Fullname,
    children: readonly string[],
    params: { readonly sort: CommentSort; readonly maxDepth: number },
    opts?: CallOpts,
  ): Promise<CommentTree>;

  /** 6. GET /search or /r/{sub}/search — candidate discovery, Section 11.3. */
  search(
    query: string,
    params: {
      readonly sub?: SubredditKey;
      readonly sort: ListingSort;
      readonly window: ListingWindow;
      readonly limit: number;
      readonly cursor?: string;
    },
    opts?: CallOpts,
  ): Promise<Page<RedditPost>>;

  /** 7. GET /r/{sub}/about */
  getSubredditAbout(sub: SubredditKey, opts?: CallOpts): Promise<SubredditAbout>;

  /**
   * 8. POST /api/subscribe — THE ONLY MUTATING METHOD IN THIS INTERFACE.
   * Joining and leaving are the same endpoint with a different action, so there is exactly
   * one write path to audit, to rate-limit, and to reason about.
   */
  subscribe(
    action: 'sub' | 'unsub',
    sub: SubredditKey,
    opts?: CallOpts,
  ): Promise<SubscribeResult>;

  /** 9. GET /user/{name}/overview — the operator's own history, Sections 9.7 and 11.3. */
  getUserOverview(
    username: string,
    params: { readonly sort: 'new' | 'hot' | 'top'; readonly limit: number; readonly cursor?: string },
    opts?: CallOpts,
  ): Promise<Page<RedditPost | RedditComment>>;

  /** 10. GET /api/info?id=… — evidence reconciliation; chunked at 100 fullnames per call. */
  getInfo(
    fullnames: readonly Fullname[],
    opts?: CallOpts,
  ): Promise<readonly (RedditPost | RedditComment)[]>;

  /** 11. Rate-limit accounting read from the last response headers. Read-only. */
  currentRateLimit(): RateLimitSnapshot;

  /** 12. Cumulative request count for this run, for the budget accounting in Section 10.6. */
  requestsIssued(): number;
}

3.5.2 NotionGateway #

// src/notion/gateway.ts

export interface NotionPageRef {
  readonly id: string;
  readonly title: string;
  readonly url: string;
  readonly lastEditedTime: string; // UTC ISO-8601
}

export interface NotionDataSourceRef {
  readonly id: string;
  readonly databaseId: string;
  readonly name: string;
}

export interface NotionBlock {
  readonly id?: string;
  readonly type: string;
  /** Raw block payload in the shape the installed SDK expects. */
  readonly payload: Readonly<Record<string, unknown>>;
}

export interface NotionRow {
  readonly id: string;
  readonly properties: Readonly<Record<string, unknown>>;
  readonly lastEditedTime: string;
}

export interface NotionGateway {
  /** The API version header the installed SDK sends. Confirmed against config. Section 3.2. */
  apiVersion(): string;

  /** Title search, exact match, case-insensitive. Zero or >1 result is an error at the caller. */
  findPagesByExactTitle(title: string, opts?: CallOpts): Promise<readonly NotionPageRef[]>;

  getPage(pageId: string, opts?: CallOpts): Promise<NotionPageRef>;

  createChildPage(
    parentPageId: string,
    title: string,
    children: readonly NotionBlock[],
    opts?: CallOpts,
  ): Promise<NotionPageRef>;

  /** Full recursive read, depth-limited, used for content farm template inference. */
  readPageBlocks(
    pageId: string,
    params: { readonly maxDepth: number },
    opts?: CallOpts,
  ): Promise<readonly NotionBlock[]>;

  appendChildren(blockId: string, children: readonly NotionBlock[], opts?: CallOpts): Promise<void>;

  /** Delete-then-append, transactional at the caller's level, used for idempotent republish. */
  replaceChildren(blockId: string, children: readonly NotionBlock[], opts?: CallOpts): Promise<void>;

  createDatabase(
    parentPageId: string,
    title: string,
    schema: Readonly<Record<string, unknown>>,
    opts?: CallOpts,
  ): Promise<{ readonly databaseId: string; readonly dataSources: readonly NotionDataSourceRef[] }>;

  /** Resolve a database id to its data sources. Required by the current API model. Section 3.2. */
  resolveDataSources(databaseId: string, opts?: CallOpts): Promise<readonly NotionDataSourceRef[]>;

  /** Queries target a data source, not a database. Section 3.2. */
  queryDataSource(
    dataSourceId: string,
    params: {
      readonly filter?: Readonly<Record<string, unknown>>;
      readonly sorts?: readonly Readonly<Record<string, unknown>>[];
      readonly cursor?: string;
      readonly pageSize: number;
    },
    opts?: CallOpts,
  ): Promise<Page<NotionRow>>;

  /** Idempotent upsert keyed on an external-id property. Section 15.5. */
  upsertRow(
    dataSourceId: string,
    externalKey: string,
    properties: Readonly<Record<string, unknown>>,
    children: readonly NotionBlock[] | undefined,
    opts?: CallOpts,
  ): Promise<NotionRow>;

  archiveRow(pageId: string, opts?: CallOpts): Promise<void>;
}

3.5.3 AgentBus #

// src/agents/bus.ts

export type PeerId = 'chief-of-staff' | 'x-bot' | 'substack-bot' | (string & {});
export type GroupId = 'prospectors' | (string & {});
export type MessageId = string & { readonly __brand: 'MessageId' }; // msg_<ULID>

export interface OutboundAgentMessage {
  readonly to: PeerId;
  readonly kind: string;              // e.g. 'corpus.request', 'lens.signal.request'
  readonly body: Readonly<Record<string, unknown>>;
  readonly correlationId?: MessageId;
  readonly replyExpected: boolean;
}

export interface InboundAgentMessage {
  readonly id: MessageId;
  readonly from: PeerId;
  readonly kind: string;
  readonly body: unknown;             // unknown at the boundary; parsed by the caller. Section 4.5
  readonly correlationId?: MessageId;
  readonly receivedAt: string;        // UTC ISO-8601
}

export interface AgentReply<T> {
  readonly from: PeerId;
  readonly value: T;
  readonly latencyMs: number;
}

export interface AgentBus {
  /** 'native' when a host bus was discovered; 'dropbox' for the filesystem fallback. Section 8.6. */
  readonly transport: 'native' | 'dropbox';

  send(msg: OutboundAgentMessage, opts?: CallOpts): Promise<MessageId>;

  /**
   * Send and await a schema-validated reply. Resolves to null on timeout — a missing peer is
   * a degraded run, never a failed one. Section 8.4.
   */
  request<T>(
    msg: OutboundAgentMessage,
    params: { readonly timeoutMs: number; readonly parse: (raw: unknown) => T },
    opts?: CallOpts,
  ): Promise<AgentReply<T> | null>;

  /** Fan-out to a group of unknown size; collects whatever arrives before the deadline. */
  broadcast<T>(
    group: GroupId,
    msg: Omit<OutboundAgentMessage, 'to'>,
    params: { readonly timeoutMs: number; readonly parse: (raw: unknown) => T },
    opts?: CallOpts,
  ): Promise<readonly AgentReply<T>[]>;

  /** Drain unsolicited inbound messages (e.g. a priority push from chief-of-staff). */
  poll(params: { readonly max: number }, opts?: CallOpts): Promise<readonly InboundAgentMessage[]>;

  ack(id: MessageId, opts?: CallOpts): Promise<void>;
}

3.5.4 LLMProvider #

// src/llm/provider.ts

export type ModelTier = 'extract' | 'enrich' | 'embed';

export interface ChatRequest<T> {
  readonly tier: ModelTier;
  readonly system: string;
  /**
   * Untrusted content inside `user` is already fenced by the caller using the one canonical
   * fence in Section 21.5.2. The provider never adds, removes, or rewrites a fence.
   */
  readonly user: string;
  /** Structured-output schema. The provider must return a value that satisfies it. */
  readonly parse: (raw: unknown) => T;
  readonly schemaName: string;
  /** Ranking-relevant calls are always 0. Section 4.10. */
  readonly temperature: 0 | number;
  readonly maxOutputTokens: number;
  /** Content hash used for the deterministic prompt cache. Section 4.10. */
  readonly cacheKey: string;
}

export interface ChatResult<T> {
  readonly value: T;
  readonly usage: { readonly inputTokens: number; readonly outputTokens: number };
  readonly model: string;
  readonly cached: boolean;
  readonly latencyMs: number;
}

export interface EmbedRequest {
  readonly inputs: readonly string[];
  readonly cacheKeys: readonly string[];
}

export interface EmbedResult {
  readonly vectors: readonly Float32Array[];
  readonly dimensions: number;
  readonly model: string;
  readonly usage: { readonly inputTokens: number };
  readonly cachedCount: number;
}

export interface ProviderCapabilities {
  readonly supportsStructuredOutput: boolean;
  readonly maxInputTokens: Readonly<Record<ModelTier, number>>;
  readonly maxBatchEmbedInputs: number;
  readonly embeddingDimensions: number;
}

export interface LLMProvider {
  readonly name: string;
  chat<T>(req: ChatRequest<T>, opts?: CallOpts): Promise<ChatResult<T>>;
  embed(req: EmbedRequest, opts?: CallOpts): Promise<EmbedResult>;
  capabilities(): ProviderCapabilities;
}

3.5.5 SecretStore #

The routine reads credentials; it never provisions, writes, or rotates one. The inventory of secret names is owned by Section 6.3 — ten names in slash form — and is not restated here, because two lists of secret names in two sections is exactly the kind of divergence that ends with a run failing on a name nobody updated.

// src/config/secrets.ts
import type { Secret } from '../util/secret.js';   // the non-serializing box, Section 21.2

/** A secret name from the inventory in Section 6.3. Validated on the way in. */
export type SecretName = string & { readonly __brand: 'SecretName' };

export interface SecretStoreDescriptor {
  /** Where secrets come from. Never includes values. */
  readonly kind: 'host' | 'env' | 'file';
  readonly availableNames: readonly SecretName[];
}

export interface SecretStore {
  /**
   * Resolve a secret. Throws RDSR_SECRET_NOT_FOUND if absent (Section 19.3).
   * Returns Secret<string>, which has no toString, no toJSON, and no enumerable value —
   * so a secret cannot reach a log line, an error context, or a Notion payload by accident.
   */
  get(name: SecretName): Promise<Secret<string>>;

  /** As get(), but resolves to undefined for an absent secret instead of throwing. */
  tryGet(name: SecretName): Promise<Secret<string> | undefined>;

  /** True when the name resolves to a non-empty value. Never returns the value. */
  has(name: SecretName): Promise<boolean>;

  describe(): SecretStoreDescriptor;
}

The default implementation is a pass-through to the host agent's secret store. The env and file adapters exist for local development and CI and are selected by the secret-store adapter key in Section 6. There is no implementation that writes secrets, and there is no configuration key, environment variable, or chat command anywhere in this system that disables log redaction.

3.5.6 SchedulerHost #

// src/pipeline/scheduler.ts

export interface ScheduledJobSpec {
  readonly id: 'rdsr.daily' | 'rdsr.weekly' | 'rdsr.monthly';
  readonly cron: string;          // '0 6 * * *'
  readonly timezone: string;      // 'America/New_York'
  readonly command: string;       // 'rdsr run'
  /** If a fire is missed, run late up to this many minutes; otherwise skip. Section 18.6. */
  readonly catchUpWindowMinutes: number;
  readonly overlapPolicy: 'skip' | 'queue';
}

export interface RegisteredJob extends ScheduledJobSpec {
  readonly nextFireAt: string;    // UTC ISO-8601
  readonly lastFireAt?: string;
}

export interface JobHandle {
  readonly id: string;
  readonly kind: 'host' | 'in-process';
}

export interface SchedulerHost {
  readonly kind: 'host' | 'in-process';
  register(job: ScheduledJobSpec): Promise<JobHandle>;
  unregister(jobId: string): Promise<void>;
  list(): Promise<readonly RegisteredJob[]>;
  /** Manual fire; returns immediately with a ticket, does not await the run. */
  triggerNow(jobId: string): Promise<{ readonly runId: RunId }>;
}

The job id is a closed union of three scheduled jobs. There is no one-shot registration and no in-process delayed timer, because the routine is a process that starts, runs, and exits — a retry it schedules for fifteen minutes from now would die with it. Automatic retry is therefore the next run's preflight resuming the previous run's checkpoint, which Section 18.11 owns.

Every one of these six ports is constructed exactly once, in a composition root at src/pipeline/context.ts, and passed down explicitly. There is no service locator, no ambient singleton, and no module-level mutable state holding a client.

3.6 Data flow #

The path from one Reddit comment to one row on a Notion page, naming the table at each hop. Every table and column named here is defined normatively in Section 5, and no other name is used anywhere in this document.

1. Fetch. harvest calls RedditClient.getComments for a qualifying post in an in-scope subreddit. The raw JSON is parsed against a zod schema and discarded; only the typed value continues. Nothing raw is persisted.
2. Land. The typed comment is written to documents, whose primary key is the Reddit fullname (t1_…), with kind='comment', subreddit, parent_id, link_id, created_utc, created_at_iso, fetched_at, permalink, body, body_chars, first_run_id, last_seen_run, and author_hash — the HMAC-SHA-256 of the lowercased username under the install salt, rendered as 64 lowercase hex characters, as defined in Section 5.3 and keyed by the salt secret named in Section 6.3. There is no author column and the raw username is never stored. The insert is ON CONFLICT (id) DO UPDATE touching only the volatile counters (score, num_comments, last_seen_run), so re-harvest is idempotent by construction.
3. Normalize. normalize writes the NFKC-normalized, whitespace-collapsed analyzed text back into documents.body, and sets body_chars, body_hash (the 64-hex digest of that text), lang, and lang_confidence. There is no second text column: documents.body is the analyzed text, and there is no raw_text, normalized_text, or content_hash anywhere in the schema. Near-duplicates — the same question crossposted to four communities — are expressed by body_hash equality, not by a duplicate_of pointer; the duplicate rows are kept, and Section 13's crosspost discount stops them from counting as four independent pieces of evidence for Breadth.
4. Filter. candidate_filter writes one row per examined document to candidates (cnd_<ULID>, document_id, run_id, subreddit, filter_score, reasons_json, rejected, reject_reason, context_doc_ids), capped at filter.maxCandidatesPerRun. Candidacy is a row in this table, never a column on documents. Rejected documents get a row with rejected = 1 and a reason, which is how the extraction-yield metric stays honest. UNIQUE (run_id, document_id) is what makes re-running the stage converge instead of duplicating.
5. Extract. extract sends surviving candidates to the model — every excerpt fenced per Section 21.5.2 — and writes zero or more rows to demand_units: du_<ULID>, document_id, candidate_id, run_id, subreddit, type from the demand-unit-type enum, need_statement (the normalized restatement of the need), audience, intensity, unmet_confidence, evidence_span (the verbatim excerpt itself, capped by the stored-span limit in Section 6), evidence_offset, doc_created_utc, local_day, model, and prompt_version. The candidate row's extracted and unit_count are updated in the same transaction, and the model call is recorded in llm_calls.
6. Embed. embed computes a vector for each demand_units.need_statement and writes it to embeddings with owner_type='demand_unit', owner_id, model, dim, and the vector as a BLOB. A vector whose dimension does not match the configured dimension is refused, not coerced. The in-memory index is rebuilt from this table at the start of cluster.
7. Cluster. cluster compares each new vector against theme centroids, which are themselves embeddings rows referenced by themes.centroid_embedding_id. A match at or above cluster.assignmentThreshold appends a row to theme_members (theme_id, demand_unit_id, similarity, is_exemplar, assigned_at, assigned_run), sets demand_units.theme_id and theme_similarity, and updates the centroid. A unit that matches nothing at or above cluster.newClusterThreshold opens a new themes row at status='watchlist'.
8. Aggregate. score rolls theme_members up by local calendar day into theme_daily_activity (theme_id, day, unit_count, subreddit_count, weighted_evidence, mean_intensity, mean_unmet), then computes the seven components and the Recurrence Score over the 14-day window and writes them onto the themes row itself — component_b, component_p, component_u, component_l, component_i, component_v, component_d, raw_score, burstiness, recency_factor, rs, rs_previous, scored_at, scored_with_lens, scored_with_config_hash. There is no separate scores table. L is computed once per theme by the lens-fit function in Section 7.7 and consumed directly; there is no per-demand-unit lens fit feeding a second aggregation.
9. Gate. Still in score: the promotion and decay gates set gate_core_pass, gate_emerging_pass, gate_watchlist_pass and gate_detail_json, update themes.status, and append a row to theme_history with change_type='status_change', from_status, to_status, rs_before, rs_after, and components_json.
10. Select. select stamps themes.selected_in_run_id on every theme it puts on the slate, in rank order, respecting select.maxNewCorePerRun, select.maxNewEmergingPerRun, and select.maxNewWatchlistPerRun. Themes that were considered and cut get a theme_history row with change_type='published' whose components_json carries the rank it would have held and the exclusion reason, so the operator can always ask why something is missing. There is no selection table.
11. Enrich. enrich produces the angle, hooks, outline, format, and platform for each selected theme and writes them to theme_entries (ent_<ULID>, theme_id, run_id, version, angle, hooks_json, outline_json, format, platform, rationale, proof_points_json, differentiation, effort_estimate, content_hash, template_source, model, prompt_version). Another llm_calls row is recorded.
12. Render. notion_publish reads themes for status and components, the newest theme_entries row per theme, and the exemplar rows of theme_members joined through demand_units to documents for excerpts and permalinks. Evidence whose source is email is filtered out before rendering and never reaches Notion or chat (Section 9.3).
13. Publish. The gateway upserts one row per theme into the Signal Board data source, keyed on themes.id as the external key. themes.notion_page_id, notion_row_id, published_at and last_published_hash are written, and the local-to-Notion mapping is recorded in notion_objects (local_type='theme_row', local_id = themes.id, notion_id, content_hash, last_written_at). If the newly rendered theme_entries.content_hash equals themes.last_published_hash, the write is skipped entirely — that is what makes republishing idempotent, cheap, and free of no-op churn in Notion's edit history.
14. Close. finalize writes per-stage timings and checkpoints to run_stages and totals to runs — including truncated and truncation_reason when the run was cut short — appends the terminal transition to run_events, releases the row in run_locks, and executes the retention prune that nulls documents.body and stamps documents.body_pruned_at beyond the configured body-retention window, leaving theme_members, theme_daily_activity, embeddings, and demand_units.evidence_span intact.

The invariant worth memorizing: documents is append-only by natural key, theme_members is append-only by observation, and everything else is derived and recomputable. Any score, any ranking, and any published page can be rebuilt from documents, demand_units, and theme_members alone.

3.7 Why batch, not streaming #

The first reason is product identity. The scoring model exists to reward evidence that accumulates across days and communities and to penalize evidence that arrives in a single burst. A streaming architecture optimizes for the opposite property — it makes the freshest observation the most consequential one, because that is the observation that just arrived and just triggered a computation. Building a low-latency path into a system whose thesis is that latency does not matter would create constant pressure to act on it: someone would eventually add an alert for a fast-rising theme, and the product would quietly become the trend tracker it was designed not to be. The 14-day rolling window, the 14-day evidence half-life, and the span_days ≥ 10 Core gate all describe a system whose smallest meaningful unit of time is a day. A daily batch is the honest implementation of that.

The second reason is the arithmetic of rate limits. Reddit's OAuth API budget is per-identity and modest, and the routine shares that identity with whatever else the host agent does. One daily pass over the in-scope set, using new listings bounded by the harvest window and comment fetches capped at depth 2, is a predictable and plannable number of requests — about 524 for a typical run, roughly 350 seconds at the sustained 90 requests per minute, which the capacity model in Section 23 sizes precisely and which the routine can pace itself against with a known reset. A streaming or polling design has no such property: to detect change quickly you must poll frequently, and to poll frequently across dozens of subreddits you must either burn the entire budget on discovery or accept that most polls return nothing new. The same applies on the model side, where batching lets extract group documents into efficiently sized calls and lets the content-hash cache absorb re-processing, and on the Notion side, where a single publish pass per day replaces a continuous trickle of writes against an API with its own limits and its own conflict semantics.

The third reason is operational. A batch job has exactly one concurrency question — is another run in flight — and it is answered by a row in run_locks. It has one failure mode per stage, one checkpoint per stage, and one obvious recovery action: run it again. Its cost is a number you can compute in advance and see in a single row. Its correctness is testable by feeding a fixture corpus in one end and comparing the Notion payload at the other. A streaming system would need backpressure, watermarks, out-of-order handling, incremental centroid maintenance, partial-window recomputation, and a story for what happens when a theme's score changes at 14:20 on a Tuesday and the Notion page is already published. None of that complexity buys a single unit of the value this product delivers, and all of it would have to be maintained forever by a system whose steady state is supposed to be nobody touching it.

3.8 Failure posture #

The routine degrades; it does not abort. Every stage is checkpointed in run_stages, so a crashed or killed run resumes at the stage that failed rather than at the beginning. Stages declare whether they are required or degradable: preflight, lens_resolve, normalize, and finalize are required, and their failure ends the run with a terminal status; every other stage has a defined degraded behavior. A peer_sync that gets zero replies proceeds with a recorded corpus-confidence penalty. A harvest that loses three subreddits to rate limiting proceeds with the subreddits it got and records the gaps by name. An enrich that exhausts its model budget publishes the themes with scores and evidence but without angles, and says so on the page. A notion_publish that hits a conflict retries, and if it still cannot write, the run ends as partial with the full digest delivered to chat so the operator loses the page but not the findings. A partial run always publishes what it has and always states what is missing — in the Notion page, in the chat digest, and in the runs row, all three rendered from the same truncation record so they cannot drift. Silence is never an acceptable outcome, and a page that looks complete but is not is the worst outcome of all. Section 18 owns the stage contracts, checkpointing, and resume semantics; Section 19 owns the error taxonomy, retry policy, and rate-limit handling.

3.9 Build, packaging, and invocation #

package.json scripts.

{
  "name": "reddit-demand-signal",
  "type": "module",
  "engines": { "node": ">=24" },
  "bin": { "rdsr": "./dist/cli.js" },
  "scripts": {
    "build": "tsc -p tsconfig.build.json",
    "dev": "node --watch src/cli.ts",
    "typecheck": "tsc -p tsconfig.json --noEmit",
    "lint": "eslint .",
    "lint:fix": "eslint . --fix",
    "format": "prettier --write .",
    "format:check": "prettier --check .",
    "test": "vitest run",
    "test:unit": "vitest run --project unit",
    "test:integration": "vitest run --project integration",
    "test:e2e": "vitest run --project e2e",
    "test:watch": "vitest",
    "test:coverage": "vitest run --coverage",
    "migrate": "node dist/cli.js migrate",
    "doctor": "node dist/cli.js doctor",
    "verify": "npm run typecheck && npm run lint && npm run format:check && npm run test"
  }
}

npm run verify is the single pre-merge command. Section 22 defines the quality gates it enforces. tsconfig.build.json sets rootDir to src and includes only src/**/*.ts, which is what puts the entrypoint at dist/cli.js where bin and the migrate/doctor scripts expect it (Section 4.4).

CLI subcommands. The entrypoint is rdsr. This table is the complete command surface of the routine. No other section invents a subcommand or a flag; every verification command, runbook step, and operator instruction anywhere in this document names something in this table.

Global flags. --config <path>, --log-level <level>, --json (machine-readable output on stdout, logs to stderr), --no-color, --timeout <seconds>, --help, --version.

One spelling per concept. These are the canonical forms, and the alternatives are not accepted anywhere: resume is run --resume <run_id>, never --from <stage>; single-stage execution is run --only <stage>, never --stage; machine-readable output is the global --json, never a per-command --format json; backup and restore are db backup and db restore --verify, never backup --now or --verify-restore.

Exit codes. Stable and scriptable.

Codes 0, 4, 5, 8, and 10 are not failures and must not page anyone, and Section 18.5's scheduler integration treats exactly that set as non-alerting: 4 is the expected state until the lens is confirmed, 5 is a run that did its job and said what it missed, 8 is expected whenever the overlap policy is skip, and 10 is only ever returned by --dry-run in CI. Every other code alerts.

4. Conventions, Code Standards, and Repository Layout #

This section is binding on all code in the routine. Where it conflicts with a personal habit, this section wins; where it conflicts with a repository-wide standard in the host agent repository, the host standard wins for cross-cutting concerns (formatting, lint baseline) and this section wins for anything specific to the routine. Requirement IDs here use the prefix RDSR-CON-###.

4.1 Repository layout #

The routine lives at routines/reddit-demand-signal/ inside the existing Reddit bot agent repository. All paths below are relative to that directory.

routines/reddit-demand-signal/
├── package.json                     # scripts, bin: rdsr, engines, deps
├── tsconfig.json                    # strict base config, used for typecheck and tests
├── tsconfig.build.json              # extends base; rootDir src, emits to dist/, excludes test/
├── eslint.config.js                 # ESLint flat config
├── .prettierrc.json                 # formatting rules
├── vitest.config.ts                 # projects: unit, integration, e2e
├── .env.example                     # every RDSR_ variable, with placeholder values only
├── README.md                        # how to build, run, and operate; links to this spec
├── docs/
│   └── DECISIONS.md                 # the running decision log (Section 4.12)
├── config/
│   ├── default.json                 # shipped defaults; the values in Section 1.2 and Section 6
│   └── schema.ts                    # zod schema for the whole config object (Section 6)
├── src/
│   ├── cli.ts                       # entrypoint: parse argv, dispatch, map errors to exit codes
│   ├── config/
│   │   ├── load.ts                  # layer defaults ← file ← env ← overrides; validate; freeze
│   │   ├── types.ts                 # Config type inferred from config/schema.ts
│   │   ├── redact.ts                # produce a log-safe view of config (Section 21.2)
│   │   └── secrets.ts               # SecretStore port + host/env/file adapters (Section 3.5.5)
│   ├── pipeline/
│   │   ├── context.ts               # composition root: builds every port, returns RunContext
│   │   ├── orchestrator.ts          # stage registry, ordering, checkpointing, resume, budgets
│   │   ├── scheduler.ts             # SchedulerHost port + host and node-cron impls
│   │   ├── lock.ts                  # single-writer run lock over run_locks, heartbeat, takeover
│   │   ├── budget.ts                # wall-clock, token, cost, and request budget accounting
│   │   └── stages/
│   │       ├── preflight.ts
│   │       ├── lens-resolve.ts
│   │       ├── peer-sync.ts
│   │       ├── membership-snapshot.ts
│   │       ├── harvest.ts
│   │       ├── normalize.ts
│   │       ├── candidate-filter.ts
│   │       ├── extract.ts
│   │       ├── embed.ts
│   │       ├── cluster.ts
│   │       ├── score.ts
│   │       ├── select.ts
│   │       ├── enrich.ts
│   │       ├── notion-publish.ts
│   │       ├── membership-actions.ts
│   │       ├── chat-digest.ts
│   │       └── finalize.ts
│   ├── lens/
│   │   ├── model.ts                 # LensProfile, Pillar, Exclusion types and invariants
│   │   ├── propose.ts               # corpus → lens proposal
│   │   ├── confirm.ts               # confirmation, amendment, supersession state machine
│   │   ├── fit.ts                   # computeLensFit — L for a THEME, never for a unit (7.7)
│   │   └── refine.ts                # continuous refinement inputs (Section 17)
│   ├── reddit/
│   │   ├── client.ts                # RedditClient port + HTTP implementation (twelve methods)
│   │   ├── auth.ts                  # OAuth2 refresh-token exchange, token cache
│   │   ├── schemas.ts               # zod schemas for every Reddit response shape
│   │   ├── pagination.ts            # cursor iteration helpers with hard page caps
│   │   ├── rate-limit.ts            # header accounting, pacing, Retry-After handling
│   │   └── membership.ts            # tier model, join/leave decisions, pacing budget
│   ├── corpus/
│   │   ├── types.ts                 # CorpusItem, CorpusSource, confidence weighting
│   │   ├── assemble.ts              # merge all providers into one weighted corpus
│   │   └── providers/
│   │       ├── email.ts             # read-only email context provider, redact-before-disk
│   │       ├── x.ts                 # asks x-bot; never fetches X
│   │       ├── substack.ts          # asks substack-bot; never fetches Substack
│   │       ├── big-brain.ts         # queries the Big Brain skill
│   │       └── reddit-history.ts    # the operator's own Reddit history
│   ├── agents/
│   │   ├── bus.ts                   # AgentBus port
│   │   ├── native-bus.ts            # adapter over whatever host bus exists
│   │   ├── dropbox-bus.ts           # filesystem drop-box fallback (Section 8.6)
│   │   ├── envelope.ts              # message envelope, correlation, msg_<ULID> minting
│   │   └── contracts.ts             # zod schemas for every message kind exchanged
│   ├── extract/
│   │   ├── prompts.ts               # extraction system/user prompt templates
│   │   ├── schemas.ts               # zod schema for extraction output
│   │   ├── extract.ts               # batching, calling, parsing, retrying
│   │   ├── evidence.ts              # evidence-span selection and the 40-word render cap
│   │   └── filter.ts                # candidate_filter heuristics
│   ├── score/
│   │   ├── embed.ts                 # embedding computation, cache, dimension guard
│   │   ├── index.ts                 # brute-force cosine index over Float32Array
│   │   ├── cluster.ts               # theme matching, centroid maintenance, new-theme creation
│   │   ├── components.ts            # B, P, U, L, I, V, D assembly (L comes from src/lens/fit.ts)
│   │   ├── recurrence.ts            # RawScore, burstiness, recency, RS
│   │   ├── gates.ts                 # promotion, demotion, dormancy, retirement
│   │   └── select.ts                # ranking, per-run creation caps, exclusion reasons
│   ├── recommend/
│   │   ├── angle.ts                 # psychological-operations angle generation
│   │   ├── format.ts                # content-format selection
│   │   └── platform.ts              # platform selection
│   ├── notion/
│   │   ├── gateway.ts               # NotionGateway port + SDK implementation
│   │   ├── resolve.ts               # parent page and data-source resolution
│   │   ├── bootstrap.ts             # first-time page and database creation
│   │   ├── template.ts              # content farm template inference + default template
│   │   ├── blocks.ts                # block builders for every entry element
│   │   ├── publish.ts               # idempotent upsert, hash comparison, conflict handling
│   │   └── queue.ts                 # the deferred publish queue and its flush
│   ├── chat/
│   │   ├── digest.ts                # digest composition
│   │   ├── confirm.ts               # confirmation prompts, nudge policy, reply parsing
│   │   └── quiet-hours.ts           # send / hold / bypass decision
│   ├── db/
│   │   ├── connection.ts            # open, pragmas per Section 5.1, WAL, busy_timeout
│   │   ├── tx.ts                    # transaction helpers, per-stage boundaries
│   │   ├── migrate.ts               # forward-only numbered migration runner
│   │   ├── enums.ts                 # the frozen enum arrays (Section 4.2.1)
│   │   ├── migrations/
│   │   │   ├── 0001_initial.sql
│   │   │   ├── 0002_theme_history.sql
│   │   │   └── ...                  # numbered, never edited after commit
│   │   └── repositories/            # one file per Section 5 aggregate
│   │       ├── runs.ts
│   │       ├── run-stages.ts
│   │       ├── run-locks.ts
│   │       ├── run-events.ts
│   │       ├── config-overrides.ts
│   │       ├── subreddits.ts
│   │       ├── subreddit-metrics.ts
│   │       ├── membership-events.ts
│   │       ├── documents.ts
│   │       ├── harvest-watermarks.ts
│   │       ├── candidates.ts
│   │       ├── demand-units.ts
│   │       ├── embeddings.ts
│   │       ├── themes.ts
│   │       ├── theme-members.ts
│   │       ├── theme-activity.ts
│   │       ├── theme-history.ts
│   │       ├── theme-entries.ts
│   │       ├── lens-profiles.ts
│   │       ├── corpus-items.ts
│   │       ├── peer-messages.ts
│   │       ├── chat-messages.ts
│   │       ├── operator-commands.ts
│   │       ├── pending-decisions.ts
│   │       ├── notion-objects.ts
│   │       ├── llm-calls.ts
│   │       ├── api-calls.ts
│   │       ├── published-content.ts
│   │       ├── feedback-events.ts
│   │       ├── quarantine.ts
│   │       └── suppressed-hashes.ts
│   ├── llm/
│   │   ├── provider.ts              # LLMProvider port
│   │   ├── host-provider.ts         # default: delegates to host agent model access
│   │   ├── openai-provider.ts       # OpenAI-compatible adapter
│   │   ├── anthropic-provider.ts    # Anthropic adapter
│   │   ├── cache.ts                 # content-hash prompt cache (Section 4.10)
│   │   └── accounting.ts            # token and cost accounting into llm_calls
│   ├── obs/
│   │   ├── logger.ts                # pino construction, child loggers, field discipline
│   │   ├── events.ts                # the frozen event-name constants from Section 20.1.2
│   │   ├── metrics.ts               # counters and timers collected per run
│   │   └── report.ts                # run report and metrics rendering for rdsr report
│   └── util/
│       ├── ids.ts                   # every ID generator and validator (Section 4.3)
│       ├── clock.ts                 # Clock interface, SystemClock, FixedClock
│       ├── secret.ts                # Secret<T>, the non-serializing box (Section 21.2)
│       ├── text.ts                  # NFKC, whitespace, truncation, escaping, stripping
│       ├── retry.ts                 # the shared retry policy (Section 19)
│       ├── errors.ts                # RdsrError hierarchy (taxonomy in Section 19)
│       ├── error-catalog.ts         # the exhaustive switch over the Section 19.3 code union
│       ├── sort.ts                  # stable, total-order comparators
│       ├── hash.ts                  # content hashing for cache keys and publish diffs
│       └── concurrency.ts           # p-limit wrappers with named pools
├── test/
│   ├── unit/                        # mirrors src/, pure functions and small units
│   ├── integration/                 # real SQLite, fake ports
│   ├── e2e/                         # full pipeline over a fixture corpus
│   └── helpers/                     # port doubles, factories, fixture loaders
├── fixtures/
│   ├── reddit/                      # recorded API payloads, redacted
│   ├── notion/                      # recorded page and data-source shapes
│   ├── bus/                         # recorded peer responses, one per message kind
│   ├── corpora/                     # synthetic identity corpora
│   └── gold/                        # gold extraction and scoring sets (Section 22.4)
└── data/                            # NOT committed; .gitignore'd
    ├── rdsr.db
    ├── backups/
    ├── logs/
    └── dropbox/                     # agent message fallback queue (Section 8.6)

There is one repository file per aggregate in Section 5, named for its principal table. Three tables have no file of their own because they are always read and written through their parent aggregate: the lens pillar and lens evidence tables live in lens-profiles.ts, the peer context cache lives in peer-messages.ts, and the schema-migration ledger is owned by migrate.ts.

RDSR-CON-001: data/ is never committed. .gitignore must contain data/, and rdsr doctor fails if the database file is tracked by git.

RDSR-CON-002: fixtures/ may contain third-party content only in redacted form — usernames replaced with stable pseudonyms, no email addresses, no external URLs beyond reddit.com permalinks. See Section 21.5.

4.2 Naming conventions #

4.2.1 Frozen enum values #

These string values are part of the data model and appear in the database, in Notion, and in log lines. They are exact and case-sensitive, and they match the CHECK constraints in Section 5 exactly.

// src/db/enums.ts

export const THEME_STATUS = [
  'core', 'emerging', 'watchlist', 'dormant', 'retired', 'dismissed',
] as const;

export const SUBREDDIT_TIER = [
  'core', 'active', 'probation', 'candidate', 'blocked', 'left',
] as const;

export const DEMAND_UNIT_TYPE = [
  'unanswered_question', 'recurring_problem', 'contested_advice', 'explainer_gap',
  'tooling_gap', 'decision_paralysis', 'emotional_support', 'terminology_confusion',
  'credibility_dispute',
] as const;

export const LENS_STATUS = [
  'draft', 'proposed', 'confirmed', 'amendment_proposed', 'superseded',
] as const;

export const RUN_STATUS = [
  'pending', 'running', 'succeeded', 'partial', 'failed', 'blocked_awaiting_lens', 'skipped',
] as const;

export const RUN_TRIGGER = ['scheduled', 'manual', 'catch_up', 'retry'] as const;

export const PLATFORM = ['x', 'substack', 'both'] as const;

export const CONTENT_FORMAT = [
  'x_thread', 'x_single', 'x_quote_frame', 'substack_essay', 'substack_short',
  'substack_series', 'carousel_teardown', 'checklist', 'case_study',
  'annotated_example', 'field_guide',
] as const;

export const STAGE_NAME = [
  'preflight', 'lens_resolve', 'peer_sync', 'membership_snapshot', 'harvest', 'normalize',
  'candidate_filter', 'extract', 'embed', 'cluster', 'score', 'select', 'enrich',
  'notion_publish', 'membership_actions', 'chat_digest', 'finalize',
] as const;

export type ThemeStatus = (typeof THEME_STATUS)[number];
export type SubredditTier = (typeof SUBREDDIT_TIER)[number];
export type DemandUnitType = (typeof DEMAND_UNIT_TYPE)[number];
export type LensStatus = (typeof LENS_STATUS)[number];
export type RunStatus = (typeof RUN_STATUS)[number];
export type RunTrigger = (typeof RUN_TRIGGER)[number];
export type Platform = (typeof PLATFORM)[number];
export type ContentFormat = (typeof CONTENT_FORMAT)[number];
export type StageName = (typeof STAGE_NAME)[number];

RDSR-CON-010: These arrays are the single source of truth in code. Zod enums, SQL CHECK constraints, and Notion select options are all generated from them and must match Section 5's DDL. A value that is not in one of these arrays never reaches the database. RUN_TRIGGER includes retry because the resume path writes it; a CHECK constraint that omits it would abort every resumed run.

The six ethical exclusion categories are not listed here. They are owned by Section 21.8.1, mirrored as an enum in Section 5.4, and referenced by name everywhere else.

4.3 Identifier formats #

// src/util/ids.ts

export type RunId = string & { readonly __brand: 'RunId' };
export type ThemeId = string & { readonly __brand: 'ThemeId' };
export type DemandUnitId = string & { readonly __brand: 'DemandUnitId' };
export type DocumentId = string & { readonly __brand: 'DocumentId' };
export type LensVersionId = string & { readonly __brand: 'LensVersionId' };
export type MessageId = string & { readonly __brand: 'MessageId' };
export type SubredditKey = string & { readonly __brand: 'SubredditKey' };

/** run_YYYYMMDD_XXXXXX using the local date in the configured display zone. */
export function newRunId(clock: Clock, timezone: string): RunId;

/** thm_<ULID>, monotonic within the process. */
export function newThemeId(clock: Clock): ThemeId;

/** du_<ULID>, monotonic within the process. */
export function newDemandUnitId(clock: Clock): DemandUnitId;

/** msg_<ULID>, monotonic within the process. */
export function newMessageId(clock: Clock): MessageId;

/** lens_v<N>. The caller supplies N from the database; this only formats it. N >= 1. */
export function formatLensVersionId(n: number): LensVersionId;

/** Normalizes 'r/Foo', '/r/foo', 'Foo' → 'foo'. Throws on invalid subreddit names. */
export function toSubredditKey(input: string): SubredditKey;

/** Validates and brands a Reddit fullname; throws unless it matches /^t[135]_[a-z0-9]+$/. */
export function toDocumentId(fullname: string): DocumentId;

/** Type guards used at every boundary where an ID arrives as a plain string. */
export function isRunId(v: string): v is RunId;
export function isThemeId(v: string): v is ThemeId;
export function isDemandUnitId(v: string): v is DemandUnitId;

RDSR-CON-020: Every ID that crosses a boundary — a database read, a JSON payload, a CLI argument — is re-validated through the matching guard before being branded. A branded type is a claim that validation happened, and that claim must never be made by a cast.

4.4 TypeScript standards #

The base tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2023",
    "lib": ["ES2023"],
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "types": ["node"],

    "strict": true,
    "noUncheckedIndexedAccess": true,
    "exactOptionalPropertyTypes": true,
    "noImplicitOverride": true,
    "noFallthroughCasesInSwitch": true,
    "noImplicitReturns": true,
    "noPropertyAccessFromIndexSignature": true,
    "useUnknownInCatchVariables": true,

    "verbatimModuleSyntax": true,
    "erasableSyntaxOnly": true,
    "isolatedModules": true,

    "forceConsistentCasingInFileNames": true,
    "skipLibCheck": true,
    "declaration": false,
    "sourceMap": true,
    "outDir": "dist",
    "rootDir": "src"
  },
  "include": ["src/**/*.ts", "test/**/*.ts", "config/**/*.ts"]
}

rootDir is src, not .. With rootDir: "." TypeScript preserves the leading src/ path segment and the entrypoint lands at dist/src/cli.js, while package.json declares bin: { "rdsr": "./dist/cli.js" } — the build succeeds and the installed binary is a broken path. tsconfig.build.json extends this file, keeps rootDir: "src", and narrows include to ["src/**/*.ts"] so neither test/ nor config/ is emitted; the config schema is imported by src/config/load.ts through a build-time copy rather than compiled into the output tree.

Why each of the non-obvious options matters here:

Hard rules.

• RDSR-CON-030: any is banned. ESLint enforces @typescript-eslint/no-explicit-any as an error with no allowlist. If you need an escape hatch, use unknown and narrow.
• RDSR-CON-031: unknown at every boundary. Any value arriving from HTTP, the database, the filesystem, the message bus, a model response, or process.env is typed unknown and must pass through a zod schema before any property is read. No exceptions, including for APIs whose shapes are "obviously" stable.
• RDSR-CON-032: Branded types for every ID. RunId, ThemeId, DemandUnitId, DocumentId, SubredditKey, MessageId, LensVersionId. A function that takes a ThemeId must be impossible to call with a DocumentId, and both are strings.
• RDSR-CON-033: readonly by default. All interface properties are readonly. All array parameters and returns are readonly T[]. Mutable structures are permitted only as function-local accumulators and must never be returned without being frozen or copied.
• RDSR-CON-034: No default exports. Named exports only, so every symbol has one canonical name that is greppable and refactorable.
• RDSR-CON-035: No barrel files. No index.ts that re-exports a directory. Barrels create import cycles, defeat tree-shaking, and make it impossible to tell from an import statement where a symbol actually lives. Import from the defining module.
• RDSR-CON-036: No non-null assertions (!). Narrow, or throw with a message. ESLint enforces @typescript-eslint/no-non-null-assertion.
• RDSR-CON-037: No type assertions (as T) except immediately after a validated guard, and never as unknown as T. Branding helpers in src/util/ids.ts are the only permitted home for an assertion, and each one sits directly behind a runtime check.
• RDSR-CON-038: Discriminated unions over optional-field soup. A stage result is { kind: 'ok'; ... } | { kind: 'degraded'; reason: string; ... }, not an object with six optional fields and an implied contract.
• RDSR-CON-039: Exhaustiveness checks on every union switch, using a shared assertNever(x: never): never helper. Adding an enum member — or an error code to the Section 19.3 union — must produce a compile error at every site that needs updating.

4.5 Validation discipline #

The rule: parse, don't validate. A schema is defined once; the static type is inferred from it. Never write an interface and a schema separately — they will diverge.

// src/reddit/schemas.ts

import { z } from 'zod';

export const redditPostSchema = z.object({
  name: z.string().regex(/^t3_[a-z0-9]+$/),
  subreddit: z.string().min(1),
  title: z.string(),
  selftext: z.string().default(''),
  author: z.string(),
  created_utc: z.number().int().positive(),
  score: z.number().int(),
  upvote_ratio: z.number().min(0).max(1),
  num_comments: z.number().int().nonnegative(),
  permalink: z.string().startsWith('/r/'),
  over_18: z.boolean(),
  link_flair_text: z.string().nullable().optional(),
  is_self: z.boolean(),
  locked: z.boolean().default(false),
  removed_by_category: z.string().nullable().optional(),
});

/** The ONLY place the post type is defined. Never hand-write a parallel interface. */
export type RedditPostRaw = z.infer<typeof redditPostSchema>;

The author field is parsed because Reddit sends it and the routine needs it for exactly one operation — deriving documents.author_hash — after which the raw value is dropped. It is never bound into an INSERT, never logged, and never rendered.

Schema module layout. Each boundary owns one schema module:

Parse failures become typed errors. A raw ZodError never escapes the module that produced it.

// src/util/parse.ts

import { z } from 'zod';
import { RdsrError } from './errors.js';

/**
 * Parse `input` with `schema`, converting any failure into a typed RdsrError carrying the
 * error code appropriate to the boundary. `sample` is a redacted excerpt for diagnostics —
 * never the full payload, never a secret, never third-party personal content.
 */
export function parseOrThrow<S extends z.ZodTypeAny>(
  schema: S,
  input: unknown,
  meta: {
    readonly code: string;         // a code from the Section 19.3 catalog
    readonly stage: StageName;
    readonly what: string;         // 'reddit.listing.post'
    readonly sample?: string;
  },
): z.infer<S> {
  const result = schema.safeParse(input);
  if (result.success) return result.data;
  throw new RdsrError({
    code: meta.code,
    message: `Schema validation failed for ${meta.what}`,
    retryable: false,
    stage: meta.stage,
    context: {
      what: meta.what,
      issues: result.error.issues.slice(0, 5).map((i) => ({
        path: i.path.join('.'),
        code: i.code,
        message: i.message,
      })),
      sample: meta.sample?.slice(0, 400),
    },
  });
}

RDSR-CON-040: Schema-invalid model output is retried up to the configured schema-repair attempt count (Section 6, default 2) with the validation error attached as a repair instruction, then the document is skipped and counted. A single malformed extraction must never fail a run. The count lands in the run report; if it exceeds 2% of extraction calls, finalize marks the run partial and the digest says so.

Tolerance policy at each boundary. Reddit and Notion schemas are lenient about unknown fields — upstream adds fields regularly and that must never break a run — but are strict about the fields we read: a missing created_utc is an error, an unexpected new_upstream_flag is ignored. Model output schemas are strict in both directions: unknown keys are rejected, because an unexpected key in a structured response usually means the model ignored the schema.

4.6 Error handling conventions #

Section 19 owns the error taxonomy, the code catalog, and the retry policy. This subsection owns the coding style.

Throw vs. return.

• RDSR-CON-050: Throw for the exceptional — conditions the calling code cannot meaningfully handle at the call site: a missing credential, an unparseable response, a database constraint violation, a configuration error.
• RDSR-CON-051: Return a typed result for the expected-and-handled — conditions that are a normal part of operation and that the caller has a specific plan for: a peer that did not reply, a subreddit that is private, a document that produced no demand units, a Notion row that is unchanged since the last publish.

// The expected-and-handled shape. No exceptions used for control flow.
export type StageOutcome<T> =
  | { readonly kind: 'ok'; readonly value: T }
  | { readonly kind: 'degraded'; readonly value: T; readonly reasons: readonly string[] }
  | { readonly kind: 'skipped'; readonly reason: string };

Wrapping.

• RDSR-CON-052: Wrap at boundaries, never in the middle. An error crossing out of src/reddit/, src/notion/, src/agents/, src/llm/, src/corpus/providers/, or src/db/ is wrapped in an RdsrError with the boundary's code and the original attached as cause. Inside a module, let errors propagate untouched.
• RDSR-CON-053: Never re-wrap an RdsrError. If it already has a code, add context with a dedicated helper and rethrow the same instance rather than nesting causes three deep.
• RDSR-CON-054: Every wrap adds stage and at least one identifying field to context (subreddit, theme_id, document_id, peer, notion_page_id). An error without a subject is unactionable.

Swallowing.

• RDSR-CON-055: An error may be swallowed only when all three hold: it is expected, the degraded behavior is specified in this document, and the swallow emits a warn log with an error code and a counter increment. A bare catch {} is a lint error.
• RDSR-CON-056: The never-swallow list. These always propagate to the orchestrator and end the stage: credential and authorization failures, configuration errors, database corruption or migration failures, programmer errors (TypeError, RangeError, ReferenceError), AbortError from a cancellation signal, and any error thrown inside preflight, lens_resolve, normalize, or finalize.

Style.

• RDSR-CON-057: catch (err: unknown) always; narrow with a helper before use. Never assume err instanceof Error.
• RDSR-CON-058: Error messages are declarative sentences describing what failed, in English, without the word "error" and without punctuation-as-emphasis. Machine-actionable detail goes in code and context, never in the message string.
• RDSR-CON-059: Never put a secret, a full API payload, a full prompt, a raw username, or personally identifying content into a message or context. Secret<T> (Section 21.2) makes the first of those a type error rather than a review comment. Redaction rules are in Section 21.2 and have no off switch.
• RDSR-CON-060: process.exit() is called in exactly one place: src/cli.ts, after the error has been logged and mapped to an exit code from Section 3.9.

4.7 Asynchrony and concurrency conventions #

• RDSR-CON-070: No unbounded Promise.all over unbounded input. Promise.all is permitted only over a fixed, statically known, small set (for example, the four peer requests in peer_sync). Anything derived from data — subreddits, documents, themes — goes through a named concurrency pool.

// src/util/concurrency.ts
import pLimit from 'p-limit';

export type PoolName = 'reddit' | 'llm' | 'notion' | 'db';

export interface Pools {
  readonly reddit: <T>(fn: () => Promise<T>) => Promise<T>;
  readonly llm: <T>(fn: () => Promise<T>) => Promise<T>;
  readonly notion: <T>(fn: () => Promise<T>) => Promise<T>;
}

/** Limits come from configuration (Section 6); the defaults are 4 / 4 / 2. */
export function createPools(cfg: {
  readonly reddit: number;   // reddit.concurrency, default 4
  readonly llm: number;      // llm.concurrency, default 4
  readonly notion: number;   // notion.concurrency, default 2
}): Pools {
  return {
    reddit: pLimit(cfg.reddit),
    llm: pLimit(cfg.llm),
    notion: pLimit(cfg.notion),
  };
}

/**
 * Bounded map with fail-soft semantics: a rejected item resolves to a typed failure rather
 * than poisoning the whole batch. Used everywhere a stage iterates over data.
 */
export async function mapPool<T, R>(
  items: readonly T[],
  limit: <U>(fn: () => Promise<U>) => Promise<U>,
  fn: (item: T, index: number) => Promise<R>,
): Promise<readonly ({ ok: true; value: R } | { ok: false; item: T; error: unknown })[]>;

• RDSR-CON-071: Every outbound call has a deadline. No fetch without an AbortSignal.timeout(). Deadlines are never literals in call sites: each boundary reads its own request-timeout key from configuration (Section 6 defines one per boundary for Reddit, Notion, the model provider, and the agent bus) and enforces it inside the port implementation.
• RDSR-CON-072: Cancellation is AbortSignal, end to end. The orchestrator owns one AbortController per run. SIGINT/SIGTERM aborts it. Every port accepts a signal in CallOpts and every long loop checks signal.aborted between items — including the synchronous-looking clustering loop, which yields between batches precisely so that it can. Combine the run signal with a per-call timeout using AbortSignal.any([runSignal, AbortSignal.timeout(ms)]).
• RDSR-CON-073: AbortError is never retried and never swallowed. It short-circuits to the orchestrator, which checkpoints and exits with code 9.
• RDSR-CON-074: No async function without await. Lint-enforced (@typescript-eslint/require-await). A function that does not await should not be async.
• RDSR-CON-075: No floating promises. Lint-enforced (@typescript-eslint/no-floating-promises). Every promise is awaited, returned, or explicitly handed to a pool.
• RDSR-CON-076: No setTimeout for sequencing. Delays exist only inside the retry helper, the lock heartbeat, and the membership pacer, all of which take the injectable Clock so tests never actually wait.
• RDSR-CON-077: Stages are sequential; parallelism lives inside a stage. The orchestrator never runs two stages concurrently. This is what makes checkpointing, transaction boundaries, and the single-writer assumption all trivially correct.

4.8 Time conventions #

• RDSR-CON-080: Storage is UTC ISO-8601 strings with millisecond precision and a Z suffix: 2026-03-11T10:00:04.512Z. Columns holding these end in _at. The one exception is Reddit's own created_utc, which is stored as an integer epoch-seconds value in a column ending in _utc, exactly as received, so it is never silently re-interpreted.
• RDSR-CON-081: Display is America/New_York, always, in the chat digest, in the Notion page, and in rdsr report. Rendering happens at the edge, never in storage.
• RDSR-CON-082: Never use the system local zone implicitly. new Date().toLocaleString() without an explicit zone is a lint error. Every conversion names its zone.
• RDSR-CON-083: Date arithmetic is banned. No adding milliseconds to compute "14 days ago," because that is wrong across a DST boundary. Use luxon DateTime with an explicit zone and calendar-aware minus({ days: 14 }).
• RDSR-CON-084: A "day" in the scoring model is a calendar day in America/New_York. active_days, span_days, demand_units.local_day, and the daily rollup in theme_daily_activity all bucket by the local calendar date, because that is the unit a human reasons in. The bucket key is stored as TEXT in YYYY-MM-DD form.
• RDSR-CON-085: Time is injected, never read from the ambient environment.

// src/util/clock.ts
import { DateTime } from 'luxon';

export interface Clock {
  /** Current instant as UTC ISO-8601 with milliseconds. */
  nowIso(): string;
  /** Current instant as epoch milliseconds. */
  nowMs(): number;
  /** Current instant as a luxon DateTime in the given IANA zone. */
  now(zone: string): DateTime;
  /** Calendar date string YYYY-MM-DD in the given zone. */
  today(zone: string): string;
  /** Resolves after `ms`, cancellable. The only sanctioned sleep in the codebase. */
  sleep(ms: number, signal?: AbortSignal): Promise<void>;
}

export class SystemClock implements Clock { /* ... */ }

/** Test double: deterministic, advanceable, sleeps resolve immediately. */
export class FixedClock implements Clock {
  constructor(startIso: string);
  advance(ms: number): void;
}

Every module that needs time takes a Clock through its constructor or its context argument. Direct use of Date.now(), new Date(), or DateTime.now() outside src/util/clock.ts is a lint error. This is what makes DST behavior, retry backoff, decay windows, lock heartbeats and quiet hours all testable without waiting. Where Section 5 defines a database trigger that reads SQLite's ambient now, that trigger is a backstop against out-of-band edits only; on the normal path the repository layer writes the timestamp from the injected Clock and the trigger's guard does not fire.

4.9 Text handling conventions #

Text passes through four stages of handling: normalization on ingest, excerpting for evidence, escaping for Notion, and truncation for display.

• RDSR-CON-090: Normalize to NFKC on ingest. Applied in normalize before hashing, before embedding, and before storage. The analyzed text is what documents.body holds and documents.body_hash digests; there is no separate raw-text column, so normalization must be lossless enough that an excerpt is still recognizably what was posted, and the permalink is what makes the original verifiable.
• RDSR-CON-091: Collapse whitespace. Runs of whitespace become a single space; runs of three or more newlines become two. Leading and trailing whitespace is trimmed. Non-breaking spaces, thin spaces, and other Unicode space separators become ordinary spaces.
• RDSR-CON-092: Strip zero-width and control characters. Remove U+200B–U+200F, U+202A–U+202E, U+2060–U+2064, U+FEFF, and C0/C1 controls other than \n and \t. These break hashing, corrupt Notion rich text, and are a known vector for prompt injection through invisible characters.
• RDSR-CON-093: Strip emoji from analyzed text. Emoji contribute noise to embeddings and nothing to demand extraction, and they are removed before hashing so that two otherwise identical crossposts collide on body_hash as they should.
• RDSR-CON-094: Strip Reddit markup artifacts before analysis: quote markers (> ) at line start are removed but the quoted line is kept and flagged, /u/ and /r/ mentions are preserved as-is, superscript carets (^) are removed, and &/</>/' entities are decoded exactly once.
• RDSR-CON-095: The 40-word quotation cap at render time. Every evidence excerpt published to Notion or sent to chat is at most 40 words. If the selected span is longer, it is truncated at a word boundary at or before 40 words and an ellipsis character (…, U+2026, never three periods) is appended. This is a hard limit enforced in src/extract/evidence.ts, tested, and it applies everywhere third-party content is reproduced. The stored demand_units.evidence_span is a different and larger limit — the stored-span character cap in Section 6 — because keeping a little more context means the excerpt can be re-derived if the truncation rule ever changes, without re-fetching from Reddit. Two limits, one on the way in and one on the way out, and the outward one is the strict one. Section 21.5 owns the reasoning.
• RDSR-CON-096: Truncation rules. Truncate at word boundaries, never mid-word. Append …. Never truncate below 8 words — if a field cannot hold 8 words, omit it rather than publishing a fragment. Truncation limits: theme name 90 characters, angle 400 characters, rationale 800 characters, evidence excerpt 40 words.
• RDSR-CON-097: Notion escaping. Notion rich text is not Markdown, and text is passed as content strings, not as Markdown source. Never string-concatenate user text into a Markdown block payload. Build blocks structurally through src/notion/blocks.ts, which is the only module permitted to construct a Notion payload. Where a Markdown-ish representation is needed (the chat digest), escape *, _, `, [, ], ~, and |.
• RDSR-CON-098: Never interpolate third-party text into a prompt without the fence. Harvested Reddit content, peer replies, corpus items, and — because a model's output is not trusted either — model-derived text such as a need statement are all untrusted input. Every one of them is wrapped in the single canonical untrusted-content fence defined in Section 21.5.2, with its per-call nonce, at every model call site without exception. There is one fence format and one scrubber in this system, and this rule is the coding-level statement of it; Section 21.6 owns the wider prompt-injection posture.

// src/util/text.ts

export function normalizeForAnalysis(raw: string): string;
export function collapseWhitespace(s: string): string;
export function stripInvisible(s: string): string;
export function stripEmoji(s: string): string;
export function decodeRedditEntities(s: string): string;

/** Truncate to at most `maxWords` whole words, appending '…' if truncated. */
export function truncateWords(s: string, maxWords: number): string;

/** Truncate to at most `maxChars`, at a word boundary, appending '…' if truncated. */
export function truncateChars(s: string, maxChars: number): string;

/** The hard 40-word render cap. Thin wrapper so the limit has exactly one call site. */
export function toEvidenceExcerpt(span: string): string;

/** Escape for the chat channel's Markdown-ish rendering. Not used for Notion. */
export function escapeChatMarkdown(s: string): string;

/**
 * Wrap untrusted content in the canonical fence from Section 21.5.2, escaping any occurrence
 * of the fence markers inside the content. `nonce` is 16 hex characters, fresh per call.
 * This is the only function in the codebase permitted to emit a fence marker.
 */
export function fenceUntrusted(content: string, nonce: string): string;

4.10 Determinism #

Rankings must be reproducible. If the operator asks why a theme ranked third, the answer must be derivable from stored inputs, and re-running the computation must produce the same third place.

• RDSR-CON-100: Seeded randomness only. There is no unseeded random number in scoring, clustering, selection, or sampling. Math.random() is a lint error outside src/util/ids.ts (which uses a CSPRNG for ID entropy, a value that never affects ranking). Any algorithm needing randomness — sampling documents for a prompt, tie-breaking a cluster, choosing the 20-to-90-second spacing between two membership calls — takes a seed derived from the run ID: seed = fnv1a(runId + ':' + purpose). The pacing jitter is seeded too, so a replayed run makes the same calls at the same offsets.
• RDSR-CON-101: Stable, total sort orders. Every .sort() uses a comparator that is a total order with an explicit final tiebreaker on an ID. No sort ever ends in a tie, because a tie means the output depends on the input's incidental order.

// src/util/sort.ts

/** Descending by score, then ascending by theme id. Total order, no ties. */
export const byRecurrenceScoreDesc = (a: ScoredTheme, b: ScoredTheme): number =>
  b.rs - a.rs
  || b.components.p - a.components.p          // prefer more persistent on a score tie
  || b.evidenceCount - a.evidenceCount
  || (a.themeId < b.themeId ? -1 : a.themeId > b.themeId ? 1 : 0);

• RDSR-CON-102: Ranking-relevant model calls are temperature 0. Every call whose output can move a theme's position — extraction, cluster labeling, and anything feeding a score component — runs at temperature: 0, and the extraction temperature ships at 0.0 precisely so that rdsr backfill --scores can reproduce a stored ranking. enrich is the one exception: it runs at the recommendation temperature from Section 6, which is non-zero because an angle benefits from some variety, and it is excluded from the determinism guarantee in RDSR-CON-107, which covers scoring inputs only. Prose the operator reads may vary between runs; the order it is presented in may not.
• RDSR-CON-103: Model calls are cached by content hash. The cache key is sha256(promptTemplateName + ':' + promptTemplateVersion + ':' + model + ':' + canonicalJson(inputs)). Cached results are stored in the database and reused across runs. This makes rdsr backfill cheap, makes --dry-run nearly free, makes tests deterministic without network access, and makes a re-run of a failed stage not re-pay for work already done. Bumping a prompt template version invalidates exactly the affected entries.
• RDSR-CON-104: Floating-point outputs are rounded before storage and comparison. All score components and the Recurrence Score are stored rounded to 4 decimal places using half-away-from-zero rounding. Comparisons against gates use the rounded values. Without this, the same computation on two machines can straddle a threshold.
• RDSR-CON-105: Canonical JSON for hashing. Keys sorted, no insignificant whitespace, numbers in shortest round-trip form. One implementation in src/util/hash.ts, used for cache keys, for theme_entries.content_hash, and for the publish-diff comparison against themes.last_published_hash.
• RDSR-CON-106: Iteration order is explicit. Never rely on Object.keys() ordering or Map insertion order for anything that reaches output. Sort explicitly before iterating when the order matters.
• RDSR-CON-107: rdsr backfill --scores must reproduce the stored scores exactly when no scoring code has changed. This is an end-to-end test in Section 22 and is the single strongest guarantee that determinism has not regressed.

4.11 Database access conventions #

• RDSR-CON-110: Repository pattern. All SQL lives in src/db/repositories/. One repository per aggregate. No SQL string appears anywhere else in the codebase — not in a stage, not in a utility, not in a test helper. Lint enforces this with a no-restricted-syntax rule matching SQL keywords in template literals outside that directory.
• RDSR-CON-111: Prepared statements, prepared once. Each repository prepares its statements at construction and reuses them. better-sqlite3 statement preparation is the expensive part; doing it per call in a loop over eleven thousand documents is the single easiest performance mistake to make here.
• RDSR-CON-112: Parameterized always. No value is ever interpolated into SQL. Named parameters (:theme_id) are preferred over positional for anything with more than two parameters.
• RDSR-CON-113: Transaction boundaries are per stage, and never span stages. Each stage commits its own work. A stage may use several transactions internally — harvest commits per subreddit, extract commits per batch — so that a failure loses at most one unit of work. There is never an open transaction across a checkpoint, because a checkpoint that is not durable is not a checkpoint.
• RDSR-CON-114: Write in batched transactions, sized as Section 5.1 specifies (200 rows). Never one transaction per row — eleven thousand individual transactions is the classic performance failure here — and never one transaction for all eleven thousand, because a crash would then lose the whole stage instead of one batch.
• RDSR-CON-115: No async inside a transaction. better-sqlite3 is synchronous; mixing an await into a transaction body is a correctness bug, not a style issue. Fetch first, then open the transaction, then write.
• RDSR-CON-116: Pragmas are set once at connection open, from one list. Connection pragmas are defined normatively in Section 5.1; src/db/connection.ts applies exactly that list and nothing else. There is deliberately no second copy of the pragma block in this section, because two pragma lists in two sections is how a busy_timeout ends up with two different values.
• RDSR-CON-117: Row mappers are explicit and validated. A repository never returns a raw row object. It maps to a typed domain object, branding IDs through their guards, and parses any JSON-valued column through a zod schema. SQLite is a boundary like any other.
• RDSR-CON-118: Migrations are forward-only, numbered, and immutable. Files are NNNN_description.sql, applied in numeric order, recorded with the file's checksum. A migration that has been committed is never edited — the runner compares checksums on startup and refuses to proceed (exit code 7) if a previously applied migration's content has changed. Corrections are new migrations.
• RDSR-CON-119: No down migrations. Rolling back is restoring a backup with rdsr db restore --verify. Reversible migration pairs create the illusion of safety and are never exercised.
• RDSR-CON-120: Every migration is idempotent-safe to re-attempt: CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS. The runner wraps each migration in a transaction and records it only on success. No migration seeds a domain row that a later CHECK constraint would reject; in particular, no lens row is seeded, because the absence of one is the meaningful state.
• RDSR-CON-121: ANALYZE after any migration that adds an index, and PRAGMA optimize at the end of finalize. SQLite's query planner needs statistics on a table whose row counts grow by orders of magnitude in the first month.

4.12 Documentation conventions in code #

• RDSR-CON-130: Doc comments are required on: every exported interface and type, every exported function whose behavior is not obvious from its name and signature, every stage function, every repository method, every prompt template, and every non-obvious constant. They are not required on internal helpers whose names are self-explanatory.
• RDSR-CON-131: Doc comments explain why, not what. /** Computes the recurrence score. */ above computeRecurrenceScore is noise. /** Multiplies by (1 − 0.45 × burstiness) so single-day spikes cannot clear the Core gate on volume alone. */ is a comment worth reading.
• RDSR-CON-132: Every prompt template carries a header comment naming its template ID, its version, what it is for, what schema it must satisfy, and what changes require a version bump.
• RDSR-CON-133: Every magic number is either in configuration or has a comment citing the section that justifies it. The burstiness coefficient is permitted inline only with a comment referencing Section 13; preferably it reads from config.
• RDSR-CON-134: docs/DECISIONS.md is maintained by the developer, in the format shown in Section 1.4. It records: every deviation from a default in Section 1.2, every decision this document left to implementation judgment, every version-line confirmation (including the Notion API version verification from Section 3.2), and every tuning change to scoring weights with the observed before/after metrics. Entries are append-only and numbered D-001, D-002, …; superseding an earlier decision means adding a new entry that names the one it supersedes.
• RDSR-CON-135: Every tunable constant lives in configuration, not inline. The test is: would anyone ever want to change this without a code change? If yes — thresholds, weights, limits, timeouts, window lengths, batch sizes, pacing budgets, model names, cap counts — it belongs in Section 6's configuration schema with a default. If no — a mathematical constant, a protocol constant like the t3_ prefix, a format string — it may be a frozen module constant. This is the rule Section 1.1 points at.
• RDSR-CON-136: README.md covers operation, not design. How to install, build, run, what the subcommands do, where the database lives, how to read a run report, and how to recover from the five most likely operational problems. Design rationale lives in this specification; the README links to it and does not duplicate it.
• RDSR-CON-137: .env.example lists every RDSR_ variable with a placeholder value, a one-line comment, and a marker of whether it is required or optional. Real values never appear; secrets are shown as <from secret store>.

4.13 Git and review conventions #

Branch naming. <type>/<area>-<short-description>, lowercase, hyphenated.

Examples: feat/score-burstiness-penalty, fix/reddit-pagination-cursor-loss, chore/bump-notion-sdk.

Commit messages. Conventional-commit style, imperative mood, scoped to a module.

<type>(<scope>): <imperative summary, <= 72 chars>

<body: what changed and why, wrapped at 80 columns. Reference requirement IDs and
section numbers where relevant. State any behavior change explicitly.>

Refs: RDSR-CON-101, Section 13.4

Example:

feat(score): apply burstiness penalty before gate evaluation

The penalty was previously applied after the promotion gates were checked, which let a
single-day spike clear the core gate on raw score and then get penalized cosmetically in
the published number. Gates now evaluate the penalized RS, which is what Section 13
specifies. Recomputed the last 30 runs with `rdsr backfill --scores`: three themes lose
core status, all three were single-day spikes.

Refs: RDSR-SCR-022, Section 13.5

RDSR-CON-140: One logical change per commit. A commit that both moves a file and changes its behavior is two commits.

RDSR-CON-141: Never commit data/, a real secret, a real API payload containing personal information, or a .env file. A pre-commit hook runs a secret scan and blocks on a match.

Pull request contents. Every PR body contains:

1. What changed — one paragraph, plain language.
2. Why — the requirement ID, section number, or decision entry that motivates it.
3. Behavior change — explicitly "none" if there is none. If scores, rankings, or published output change, say exactly how and attach the before/after from rdsr backfill --scores.
4. Migration — the migration number if one was added, and whether it is destructive.
5. Configuration — any new or changed key, its default, and whether existing deployments need action.
6. Test evidence — what was added, and the output of npm run verify.
7. Rollback — how to undo this if it misbehaves in production.

RDSR-CON-142: A PR that changes a scoring weight, a gate threshold, or a prompt template must include a backfill comparison over at least the last 14 days of stored data. Scoring is the product; changing it blind is not permitted.

Pre-merge gates. All of the following must pass. Section 22 owns their definitions, thresholds, and enforcement.

RDSR-CON-143: The gates are not advisory. A red gate is a blocked merge, and the correct response is to fix the code, not to lower the threshold. Lowering a threshold requires its own PR, its own justification, and an entry in docs/DECISIONS.md.

5. Data Model and Persistence Schema #

This section is the single source of truth for persistence. Every table, column, type, constraint, default, index, and trigger in the routine is defined here. No other section introduces a table or a column. Sections that describe behavior over this data (Sections 10 through 21) reference these names but never redefine them. There are thirty-six tables; a name that does not appear in Section 5.2's inventory does not exist.

Requirement IDs in this section use the prefix RDSR-DAT-###.

5.1 Storage principles #

RDSR-DAT-001 — One file, one writer. The routine persists everything in a single SQLite database file at data/rdsr.db, relative to the routine root. There is exactly one writer process: the pipeline run. Read-only consumers (the rdsr CLI inspection commands, the doctor checks, the chat command handler when it only reads) open a second connection in read-only mode. This constraint is deliberate and is what makes SQLite safe here.

RDSR-DAT-002 — Why SQLite is the correct choice. The workload is a once-daily batch for a single operator. Peak write volume is on the order of 12,000 document rows, 1,400 candidate rows, and 640 demand-unit rows per run, with all writes originating from one process inside a window of a few minutes. There is no multi-tenant access, no horizontal scaling requirement, and no network boundary between the application and its data. A server database would add an operational dependency (a daemon to run, a port to secure, a backup agent to schedule) that buys nothing at this volume. SQLite in WAL mode gives durable transactions, real foreign keys, CHECK constraints, partial indexes, window functions, and JSON functions — every feature the scoring and clustering logic needs — inside a file the operator can copy, diff, and restore with cp.

RDSR-DAT-003 — Connection pragmas. Every connection, read-write or read-only, applies the following pragmas immediately after opening, in this order. They are not optional; the migration runner refuses to proceed if foreign_keys reports 0. This list is normative: no other section restates it, and src/db/connection.ts applies exactly this list and nothing else.

PRAGMA journal_mode = WAL;          -- concurrent readers during the run's writes
PRAGMA foreign_keys = ON;           -- FKs are OFF by default in SQLite; we require them
PRAGMA busy_timeout = 10000;        -- 10s: a read-only CLI must never fail a batch write
PRAGMA synchronous = NORMAL;        -- WAL + NORMAL is crash-safe; FULL costs fsyncs we do not need
PRAGMA temp_store = MEMORY;         -- sorts and temp B-trees stay off disk
PRAGMA cache_size = -65536;         -- 64 MiB page cache (negative = KiB), sized for clustering scans
PRAGMA mmap_size = 268435456;       -- 256 MiB memory map for large sequential reads
PRAGMA journal_size_limit = 67108864; -- truncate the WAL back to 64 MiB after checkpoints
PRAGMA auto_vacuum = INCREMENTAL;   -- set at creation; enables reclaim without a full VACUUM
PRAGMA analysis_limit = 400;        -- bound the cost of PRAGMA optimize
PRAGMA trusted_schema = OFF;        -- defense in depth: no schema-embedded function calls

auto_vacuum = INCREMENTAL only takes effect if it is set before the first table is created, so migration 001 sets it as its first statement. A read-only connection sets query_only = ON in addition to the above and skips journal_mode (which is a no-op on a read-only handle).

The canonical opener lives in src/db/connection.ts:

import Database from 'better-sqlite3';
import type { Database as Db } from 'better-sqlite3';

export interface OpenDbOptions {
  readonly path: string;
  readonly readOnly?: boolean;
  readonly busyTimeoutMs?: number;
}

export function openDatabase(opts: OpenDbOptions): Db {
  const db = new Database(opts.path, { readonly: opts.readOnly ?? false });
  if (!opts.readOnly) {
    db.pragma('journal_mode = WAL');
    db.pragma('synchronous = NORMAL');
    db.pragma('journal_size_limit = 67108864');
  } else {
    db.pragma('query_only = ON');
  }
  db.pragma('foreign_keys = ON');
  db.pragma(`busy_timeout = ${opts.busyTimeoutMs ?? 10000}`);
  db.pragma('temp_store = MEMORY');
  db.pragma('cache_size = -65536');
  db.pragma('mmap_size = 268435456');
  db.pragma('trusted_schema = OFF');

  const fk = db.pragma('foreign_keys', { simple: true });
  if (fk !== 1) {
    throw new Error('RDSR_DB_FOREIGN_KEYS_DISABLED: refusing to operate without foreign keys');
  }
  return db;
}

RDSR-DAT-004 — Transaction discipline. Each pipeline stage commits at least once. Long stages (harvest, extract, embed) commit in batches of 200 rows so that a crash loses at most one batch, and so the WAL does not grow without bound. Never one transaction per row — 9,000 individual transactions is the single easiest performance mistake available here — and never one transaction for all 9,000, because a crash would lose the whole stage. Transactions are opened with BEGIN IMMEDIATE (not the default deferred mode) to acquire the write lock up front and turn lock contention into a fast, deterministic failure instead of a mid-transaction SQLITE_BUSY. Nested logical transactions use SAVEPOINT. The repository layer in src/db/repositories/ exposes a withTransaction<T>(fn: () => T): T helper; no stage calls BEGIN directly.

RDSR-DAT-005 — STRICT tables. Every table is declared STRICT. SQLite's default dynamic typing would silently accept a string in an integer column, which in a pipeline that writes LLM-derived values is a real hazard. STRICT restricts column types to INT, INTEGER, REAL, TEXT, BLOB, and ANY, and rejects type mismatches at insert time. Consequently this schema uses no BOOLEAN, DATETIME, VARCHAR, or NUMERIC type names anywhere.

RDSR-DAT-006 — Type conventions.

RDSR-DAT-007 — Outgrowing SQLite. The routine migrates off SQLite when any of these thresholds is crossed and sustained for seven consecutive runs:

1. Database file exceeds 20 GiB after a prune and incremental vacuum.
2. documents exceeds 25 million live rows.
3. The p95 wall-clock time of the cluster stage exceeds 240 seconds on the operator's hardware.
4. A second concurrent writer becomes a genuine requirement (for example, a second routine in the same agent needing write access to themes).

The migration target is PostgreSQL with pgvector. Because every query lives in src/db/repositories/ behind hand-written SQL and typed row mappers — no ORM — the port is a mechanical rewrite of those files plus a dialect pass over src/db/migrations/. Nothing in src/pipeline/ touches SQL directly, which is the property that keeps this exit cheap. At the projected growth in Section 5.2 (roughly 3.43 million documents rows per year), threshold 2 is approximately seven years away; the escape hatch exists for correctness of design, not because it is expected to be used soon.

RDSR-DAT-008 — Pseudonymous identifiers: one construction, used everywhere. Two columns in this schema stand in for something a person could be identified by, and both use the same construction. There is exactly one definition and it is here.

author_hash = lowercase_hex( HMAC-SHA-256( key = <rdsr/author_salt>,
                                           message = lowercase(reddit_username) ) )

The result is exactly 64 lowercase hexadecimal characters, and the DDL enforces length(author_hash) = 64. The key is the installation-local secret named rdsr/author_salt (Section 6.3). HMAC is used rather than SHA-256(salt ‖ value) because a keyed MAC is the correct primitive for this job and is not vulnerable to length-extension. No truncation is applied: a 64-character column costs 3.43 million × 64 bytes ≈ 220 MB a year, which is affordable, and truncation buys nothing here because the collision surface is not the threat.

The raw Reddit username is never stored, never logged, never sent to a model, and never appears in Notion, in chat, or in any worked example in this document. There is no author column on documents, and there is no configuration that creates one; the schema makes the raw value unrepresentable. When Reddit reports the author as [deleted] or [removed], that literal string is hashed like any other value, so every deleted author shares one well-known hash and the column stays NOT NULL. Section 10's field map maps the Reddit author field through this HMAC on the way in; Section 21 owns the privacy rationale.

The second keyed identifier is the content pepper, the secret named rdsr/privacy_pepper. Where this schema stores a hash of third-party content whose only purpose is equality testing — quarantine.payload_hash and suppressed_hashes.hash — the hash is HMAC-SHA-256(key = <rdsr/privacy_pepper>, message = normalized_body) rather than a bare digest, so a copy of the database cannot be used to confirm that a specific known document passed through the routine. Content hashes whose purpose is change detection on the routine's own output — documents.body_hash, theme_entries.content_hash, notion_objects.content_hash, corpus_items.body_hash, lens_evidence.excerpt_hash, chat_messages.body_hash, embeddings.input_hash, runs.config_hash — are plain SHA-256, because they compare the routine's own values to each other and are meaningless outside the install.

5.2 Entity-relationship overview #

                       ┌──────────────────┐
                       │ schema_migrations│  (ledger, no FKs)
                       └──────────────────┘
   ┌───────────────┐   ┌──────────────────┐   ┌───────────────────┐
   │config_overrides│  │       runs       │◄──┤    run_stages     │
   └───────────────┘   └────────┬─────────┘   ├───────────────────┤
                                │             │    run_events     │
                                │             ├───────────────────┤
                                │             │     run_locks     │
                                │             └───────────────────┘
                                │ run_id (declared FK on most tables)
        ┌───────────────────────┼────────────────────────────┐
        │                       │                            │
┌───────▼────────┐     ┌────────▼─────────┐        ┌─────────▼────────┐
│   subreddits   │     │    documents     │        │  lens_profiles   │
│  (key = PK)    │◄────┤ subreddit FK     │        │ version = PK     │
└───┬────┬───┬───┘     └────────┬─────────┘        └───┬──────────┬───┘
    │    │    │                 │ id                   │          │
    │    │    │        ┌────────▼─────────┐   ┌────────▼─────┐ ┌──▼──────────┐
    │    │    │        │    candidates    │   │ lens_pillars │ │lens_evidence│
    │    │    │        └────────┬─────────┘   └──────┬───────┘ └─────────────┘
    │    │    │                 │                    │ centroid_embedding_id
    │    │    │        ┌────────▼─────────┐          │
    │    │    │        │   demand_units   │──────────┼────────────┐
    │    │    │        └────────┬─────────┘          │            │
    │    │    │                 │ theme_id           │      ┌─────▼──────┐
    │    │    │        ┌────────▼─────────┐          └─────►│ embeddings │
    │    │    │        │      themes      │◄────────────────┤ (polymorph)│
    │    │    │        └──┬───┬───┬───┬───┘                 └─────▲──────┘
    │    │    │           │   │   │   │                           │
    │    │    │  ┌────────▼┐ ┌▼───────────────┐ ┌─────────────┐   │
    │    │    │  │theme_   │ │theme_daily_    │ │theme_history│   │
    │    │    │  │members  │ │activity        │ └─────────────┘   │
    │    │    │  └─────────┘ └────────────────┘                   │
    │    │    │           │                                       │
    │    │    │  ┌────────▼─────┐   ┌────────────────┐  ┌─────────┴───┐
    │    │    │  │theme_entries │──►│ notion_objects │  │corpus_items │
    │    │    │  └──────────────┘   └────────────────┘  └─────────────┘
    │    │    │
    │    │  ┌─▼──────────────────────┐  ┌──────────────────────┐
    │    │  │subreddit_metrics_daily │  │  harvest_watermarks  │
    │    │  └────────────────────────┘  └──────────────────────┘
    │    │
    │  ┌─▼────────────────┐
    │  │ membership_events│
    │  └──────────────────┘
    │
    │   ┌───────────────┐ ┌──────────────────┐ ┌───────────────┐ ┌──────────────────┐
    └──►│ peer_messages │ │peer_context_cache│ │ chat_messages │ │operator_commands │
        └───────────────┘ └──────────────────┘ └───────┬───────┘ └────────┬─────────┘
                                                       │                  │
        ┌───────────┐ ┌───────────┐ ┌──────────────────▼┐ ┌───────────────▼──┐
        │ llm_calls │ │ api_calls │ │ published_content │ │ pending_decisions│
        └───────────┘ └───────────┘ └───────────────────┘ └──────────────────┘

        ┌────────────────┐ ┌────────────┐ ┌───────────────────┐
        │ feedback_events│ │ quarantine │ │ suppressed_hashes │
        └────────────────┘ └────────────┘ └───────────────────┘

        ┌────────────────────┐
        │ notion_write_queue │  (durable deferred Notion writes; run_id FK)
        └────────────────────┘

Solid arrows are declared foreign keys. run_id appears on many tables as an indexed column referencing runs(id); it is a declared foreign key everywhere except runs itself.

That is thirty-six tables. Steady-state size after one year, with the retention policy in Section 5.7 applied:

Section 23 owns capacity budgets in detail; the numbers above are the input to it, and the backup footprint derived from them is in Section 5.8.

5.3 Full DDL #

All statements below appear verbatim in src/db/migrations/. They are grouped here by domain for readability; Section 5.6 maps them to numbered migration files.

Universal conventions applied to every table:

• Primary keys are explicit and never INTEGER PRIMARY KEY AUTOINCREMENT unless the row has no natural identity (AUTOINCREMENT is avoided entirely; a bare INTEGER PRIMARY KEY reuses rowids only after deletion, which is acceptable for pure append-and-prune tables).
• Every foreign key names its ON DELETE behavior explicitly. RESTRICT is used where deletion would destroy an audit trail; CASCADE where the child has no meaning without the parent; SET NULL where the child survives independently.
• Every enum column carries a CHECK listing the exact canonical strings (Section 5.4).
• Every table has a created_at (and, where mutated, updated_at) in UTC ISO-8601.

5.3.1 System and run control #

-- Applied-migration ledger. Never modified by hand.
CREATE TABLE schema_migrations (
  version      INTEGER NOT NULL PRIMARY KEY,
  name         TEXT    NOT NULL,
  checksum     TEXT    NOT NULL CHECK (length(checksum) = 64),
  applied_at   TEXT    NOT NULL,
  duration_ms  INTEGER NOT NULL DEFAULT 0 CHECK (duration_ms >= 0),
  applied_by   TEXT    NOT NULL DEFAULT 'rdsr-migrate'
) STRICT;

No indexes beyond the primary key: the table is read whole at startup and holds fewer than fifty rows for the life of the product.

-- Operator overrides of configuration keys, persisted from chat or CLI.
-- This is the fourth layer of the precedence chain defined in Section 6.1.
CREATE TABLE config_overrides (
  key           TEXT    NOT NULL PRIMARY KEY,       -- dotted path, e.g. 'score.weights.persistence'
  value_json    TEXT    NOT NULL CHECK (json_valid(value_json)),
  previous_json TEXT             CHECK (previous_json IS NULL OR json_valid(previous_json)),
  set_by        TEXT    NOT NULL CHECK (set_by IN ('chat', 'cli', 'doctor', 'migration')),
  set_at        TEXT    NOT NULL,
  expires_at    TEXT,                                -- NULL = permanent
  reason        TEXT,
  command_id    TEXT    REFERENCES operator_commands(id) ON DELETE SET NULL,
  active        INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))
) STRICT;

CREATE INDEX idx_config_overrides_active ON config_overrides(active, expires_at);

• idx_config_overrides_active — startup loads only active, unexpired overrides; this keeps that read a single index scan instead of a full table scan plus JSON parsing of dead rows.

-- One row per pipeline run.
CREATE TABLE runs (
  id                     TEXT    NOT NULL PRIMARY KEY,   -- run_YYYYMMDD_XXXXXX
  scheduled_for          TEXT    NOT NULL,               -- UTC instant the 06:00 America/New_York slot maps to
  local_day              TEXT    NOT NULL,               -- YYYY-MM-DD in America/New_York
  trigger                TEXT    NOT NULL
                           CHECK (trigger IN ('scheduled', 'manual', 'catch_up', 'retry')),
  resumed_from           TEXT    REFERENCES runs(id) ON DELETE SET NULL,
  status                 TEXT    NOT NULL
                           CHECK (status IN ('pending','running','succeeded','partial','failed',
                                             'blocked_awaiting_lens','skipped')),
  started_at             TEXT,
  finished_at            TEXT,
  duration_ms            INTEGER CHECK (duration_ms IS NULL OR duration_ms >= 0),
  window_start_utc       TEXT    NOT NULL,
  window_end_utc         TEXT    NOT NULL,
  window_hours           INTEGER NOT NULL DEFAULT 24 CHECK (window_hours BETWEEN 20 AND 28),
  evidence_days_observed INTEGER NOT NULL DEFAULT 0 CHECK (evidence_days_observed >= 0),
  degraded_window        INTEGER NOT NULL DEFAULT 0 CHECK (degraded_window IN (0, 1)),
  degraded_modes         TEXT             CHECK (degraded_modes IS NULL OR json_valid(degraded_modes)),
  truncated              INTEGER NOT NULL DEFAULT 0 CHECK (truncated IN (0, 1)),
  truncation_reason      TEXT,
  truncation_detail_json TEXT    NOT NULL DEFAULT '{}' CHECK (json_valid(truncation_detail_json)),
  backlog_multiplier     INTEGER NOT NULL DEFAULT 1 CHECK (backlog_multiplier BETWEEN 1 AND 7),
  lens_version           TEXT    REFERENCES lens_profiles(version) ON DELETE RESTRICT,
  config_hash            TEXT    NOT NULL CHECK (length(config_hash) = 64),
  scoring_config_hash    TEXT    NOT NULL CHECK (length(scoring_config_hash) = 64),
  routine_version        TEXT    NOT NULL,               -- semver of the routine package
  host                   TEXT    NOT NULL,
  dry_run                INTEGER NOT NULL DEFAULT 0 CHECK (dry_run IN (0, 1)),
  subreddits_harvested   INTEGER NOT NULL DEFAULT 0 CHECK (subreddits_harvested >= 0),
  docs_harvested         INTEGER NOT NULL DEFAULT 0 CHECK (docs_harvested >= 0),
  docs_new               INTEGER NOT NULL DEFAULT 0 CHECK (docs_new >= 0),
  candidates_selected    INTEGER NOT NULL DEFAULT 0 CHECK (candidates_selected >= 0),
  demand_units_extracted INTEGER NOT NULL DEFAULT 0 CHECK (demand_units_extracted >= 0),
  themes_touched         INTEGER NOT NULL DEFAULT 0 CHECK (themes_touched >= 0),
  themes_created         INTEGER NOT NULL DEFAULT 0 CHECK (themes_created >= 0),
  themes_published       INTEGER NOT NULL DEFAULT 0 CHECK (themes_published >= 0),
  joins_executed         INTEGER NOT NULL DEFAULT 0 CHECK (joins_executed >= 0),
  leaves_executed        INTEGER NOT NULL DEFAULT 0 CHECK (leaves_executed >= 0),
  docs_safety_excluded   INTEGER NOT NULL DEFAULT 0 CHECK (docs_safety_excluded >= 0),
  llm_call_count         INTEGER NOT NULL DEFAULT 0 CHECK (llm_call_count >= 0),
  llm_input_tokens       INTEGER NOT NULL DEFAULT 0 CHECK (llm_input_tokens >= 0),
  llm_output_tokens      INTEGER NOT NULL DEFAULT 0 CHECK (llm_output_tokens >= 0),
  llm_embedding_tokens   INTEGER NOT NULL DEFAULT 0 CHECK (llm_embedding_tokens >= 0),
  llm_cost_estimate_usd  REAL    NOT NULL DEFAULT 0.0 CHECK (llm_cost_estimate_usd >= 0.0),
  error_code             TEXT,
  error_summary          TEXT,
  report_path            TEXT,
  created_at             TEXT    NOT NULL,
  updated_at             TEXT    NOT NULL,
  CHECK (truncated = 0 OR truncation_reason IS NOT NULL),
  CHECK (trigger <> 'retry' OR resumed_from IS NOT NULL)
) STRICT;

CREATE UNIQUE INDEX uq_runs_local_day_scheduled
  ON runs(local_day) WHERE trigger = 'scheduled';
CREATE INDEX idx_runs_status_scheduled ON runs(status, scheduled_for DESC);
CREATE INDEX idx_runs_scheduled_for ON runs(scheduled_for DESC);
CREATE INDEX idx_runs_lens_version ON runs(lens_version);
CREATE INDEX idx_runs_blocked ON runs(scheduled_for DESC) WHERE status = 'blocked_awaiting_lens';

• uq_runs_local_day_scheduled — a partial unique index guaranteeing at most one scheduled run per local day, which is what makes catch-up, retry, and manual reruns safe: they use a different trigger and are therefore exempt.
• idx_runs_status_scheduled — the scheduler asks "is anything still running or pending?" on every wake-up; Section 18's stuck-run detection depends on this being cheap.
• idx_runs_scheduled_for — powers "the last N runs" for the CLI and the report generator.
• idx_runs_lens_version — answers "which runs used lens lens_v4?" when a lens amendment forces a rescore.
• idx_runs_blocked — counts consecutive blocked_awaiting_lens runs, which is what the spend guard in Section 7.3 reads to decide when to drop to harvest-only.

Four column groups on this table exist because behavior elsewhere depends on them and had nowhere to live:

• trigger = 'retry' and resumed_from. A resumed run is recorded with trigger retry carrying the id of the run it continues. Without the enum value every resume would abort on a CHECK violation; without the column the resume chain would be unauditable. The table-level CHECK makes the pair inseparable.
• The window columns. window_start_utc, window_end_utc, and window_hours record the evidence window the run actually covered. window_hours is 24 on an ordinary day and 23 or 25 across a DST transition, which is why Persistence normalizes by it rather than assuming a fixed day. evidence_days_observed is the denominator Persistence divides by, and degraded_window marks a run whose window was short or whose harvest was incomplete; promotion to core is suspended for themes whose evidence falls inside a degraded window.
• The truncation columns. truncated, truncation_reason, and truncation_detail_json back the truncation object in the run report, the coverage line in the Notion status callout, and the run-health line in the chat digest. All three surfaces render from these columns, computed once in finalize, so they cannot disagree. truncation_detail_json carries {stages_cut, documents_dropped, subreddits_dropped, coverage_share}.
• backlog_multiplier. The first run after a long blocked_awaiting_lens stretch processes more than one day of stored evidence; this records how many days it took on, so its cost is explicable rather than anomalous.

-- One row per stage per run. Seventeen stages, fixed order (Section 18).
CREATE TABLE run_stages (
  run_id        TEXT    NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
  stage         TEXT    NOT NULL
                  CHECK (stage IN ('preflight','lens_resolve','peer_sync','membership_snapshot',
                                   'harvest','normalize','candidate_filter','extract','embed',
                                   'cluster','score','select','enrich','notion_publish',
                                   'membership_actions','chat_digest','finalize')),
  stage_index   INTEGER NOT NULL CHECK (stage_index BETWEEN 1 AND 17),
  status        TEXT    NOT NULL
                  CHECK (status IN ('pending','running','succeeded','partial','failed','skipped')),
  skip_reason   TEXT,
  started_at    TEXT,
  finished_at   TEXT,
  duration_ms   INTEGER CHECK (duration_ms IS NULL OR duration_ms >= 0),
  budget_ms     INTEGER NOT NULL DEFAULT 0 CHECK (budget_ms >= 0),
  grace_used_ms INTEGER NOT NULL DEFAULT 0 CHECK (grace_used_ms >= 0),
  slack_left_ms INTEGER NOT NULL DEFAULT 0 CHECK (slack_left_ms >= 0),
  truncated     INTEGER NOT NULL DEFAULT 0 CHECK (truncated IN (0, 1)),
  attempt       INTEGER NOT NULL DEFAULT 0 CHECK (attempt >= 0),
  items_in      INTEGER NOT NULL DEFAULT 0 CHECK (items_in >= 0),
  items_out     INTEGER NOT NULL DEFAULT 0 CHECK (items_out >= 0),
  checkpoint    TEXT             CHECK (checkpoint IS NULL OR json_valid(checkpoint)),
  error_code    TEXT,
  error_message TEXT,
  PRIMARY KEY (run_id, stage)
) STRICT;

CREATE INDEX idx_run_stages_status ON run_stages(status, stage);
CREATE INDEX idx_run_stages_stage_started ON run_stages(stage, started_at DESC);
CREATE INDEX idx_run_stages_open_checkpoint ON run_stages(run_id)
  WHERE checkpoint IS NOT NULL AND status <> 'succeeded';

• idx_run_stages_status — resume logic finds the first non-succeeded stage of an interrupted run without scanning history.
• idx_run_stages_stage_started — the observability report in Section 20 charts per-stage duration over time; this makes that a single index range scan.
• idx_run_stages_open_checkpoint — the retention sweep must not null a checkpoint that a resume still needs; this partial index is exactly the set it must leave alone.

checkpoint holds a stage-specific JSON blob that lets a crashed stage resume mid-flight — for harvest it is {"completedSubreddits":["…"],"cursor":{"askreddit":"t3_abc123"}}; for extract it is {"lastCandidateId":"t3_xyz789","batchIndex":41}. Its shape is owned by each stage's section. Checkpoint payloads are small — counts, cursors, and identifier lists, never bulk data; the column simply guarantees the value is valid JSON.

budget_ms, grace_used_ms, and slack_left_ms record the deadline arithmetic Section 18 performs, so an operator reading a stage row can tell the difference between a run that was tight and a run that was broken.

-- The single-writer lock. Exactly one row, ever.
CREATE TABLE run_locks (
  id           TEXT    NOT NULL PRIMARY KEY CHECK (id = 'default'),
  run_id       TEXT    NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
  pid          INTEGER NOT NULL CHECK (pid > 0),
  host         TEXT    NOT NULL,
  stage        TEXT    NOT NULL,
  acquired_at  TEXT    NOT NULL,
  heartbeat_at TEXT    NOT NULL,
  released_at  TEXT
) STRICT;

CREATE INDEX idx_run_locks_heartbeat ON run_locks(heartbeat_at) WHERE released_at IS NULL;

• idx_run_locks_heartbeat — the staleness test is a single-row read, but the partial index keeps the "is the lock live?" question expressible in one indexed predicate and keeps released rows out of it.

The CHECK (id = 'default') is what makes the single-writer guarantee structural rather than conventional: a second lock row cannot be inserted. The holder writes heartbeat_at every run.heartbeatSeconds (Section 6.2.2, default 30). A lock whose heartbeat is older than run.staleLockSeconds (default 180 — six missed heartbeats) is stale and the next run takes it over automatically after confirming the recorded pid is not alive on this host. A wedged holder whose pid is alive is never taken over automatically; that case is the operator's, and rdsr unlock --force (Section 3.9) is the supported recovery. Forcing the lock marks the held run failed, writes a run_events row with event run.lock.force_released carrying the pid, host, run id, and heartbeat age, and clears the row — Section 18 owns the procedure, this table owns the state it manipulates.

-- Append-only per-run event log. The timeline behind the run report.
CREATE TABLE run_events (
  id          INTEGER NOT NULL PRIMARY KEY,
  run_id      TEXT    NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
  stage       TEXT,                                    -- NULL for run-level events
  event       TEXT    NOT NULL,                        -- dot.separated.lowercase, Section 20.1.2
  severity    TEXT    NOT NULL DEFAULT 'info'
                CHECK (severity IN ('debug','info','warn','error','critical')),
  payload     TEXT    NOT NULL DEFAULT '{}' CHECK (json_valid(payload)),
  occurred_at TEXT    NOT NULL
) STRICT;

CREATE INDEX idx_run_events_run ON run_events(run_id, occurred_at);
CREATE INDEX idx_run_events_name ON run_events(event, occurred_at DESC);
CREATE INDEX idx_run_events_severe ON run_events(occurred_at DESC)
  WHERE severity IN ('warn','error','critical');

• idx_run_events_run — replaying one run's timeline in order, which is what rdsr report does.
• idx_run_events_name — "when did membership.join.executed last fire?" across all runs.
• idx_run_events_severe — the alert digest reads only the interesting tail.

event holds a name from the registry in Section 20.1.2 and from nowhere else; the registry is not duplicated here, because a duplicate registry would diverge. payload is the event's structured fields, already redacted per Section 6.3.

5.3.2 Reddit surface #

-- Every subreddit the routine has ever seen, joined, or rejected.
CREATE TABLE subreddits (
  key                TEXT    NOT NULL PRIMARY KEY,  -- lowercase name, no 'r/' prefix
  display_name       TEXT    NOT NULL,
  title              TEXT,
  public_description TEXT,
  tier               TEXT    NOT NULL DEFAULT 'candidate'
                       CHECK (tier IN ('core','active','probation','candidate','blocked','left')),
  subscribers        INTEGER CHECK (subscribers IS NULL OR subscribers >= 0),
  active_users       INTEGER CHECK (active_users IS NULL OR active_users >= 0),
  created_utc        INTEGER,
  over_18            INTEGER NOT NULL DEFAULT 0 CHECK (over_18 IN (0, 1)),
  lang               TEXT    NOT NULL DEFAULT 'unknown',
  subreddit_type     TEXT    NOT NULL DEFAULT 'public'
                       CHECK (subreddit_type IN ('public','restricted','private','archived',
                                                 'gold_only','employees_only','user','unknown')),
  is_member          INTEGER NOT NULL DEFAULT 0 CHECK (is_member IN (0, 1)),
  joined_at          TEXT,
  left_at            TEXT,
  first_seen_at      TEXT    NOT NULL,
  last_harvested_at  TEXT,
  tier_changed_at    TEXT,
  settling_until     TEXT,                           -- no yield judgment before this instant
  pinned_by_operator INTEGER NOT NULL DEFAULT 0 CHECK (pinned_by_operator IN (0, 1)),
  blocked            INTEGER NOT NULL DEFAULT 0 CHECK (blocked IN (0, 1)),
  block_reason       TEXT,
  safety_excluded    INTEGER NOT NULL DEFAULT 0 CHECK (safety_excluded IN (0, 1)),
  discovery_source   TEXT    NOT NULL DEFAULT 'existing_subscription'
                       CHECK (discovery_source IN ('existing_subscription','mention','crosspost',
                                                   'search','peer_suggestion','operator_history',
                                                   'operator')),
  discovery_strength REAL    NOT NULL DEFAULT 0.0 CHECK (discovery_strength BETWEEN 0.0 AND 1.0),
  discovered_by_run  TEXT    REFERENCES runs(id) ON DELETE SET NULL,
  yield_score        REAL    NOT NULL DEFAULT 0.0 CHECK (yield_score BETWEEN 0.0 AND 1.0),
  yield_sample_docs  INTEGER NOT NULL DEFAULT 0 CHECK (yield_sample_docs >= 0),
  notes              TEXT,
  created_at         TEXT    NOT NULL,
  updated_at         TEXT    NOT NULL,
  CHECK (blocked = 0 OR tier = 'blocked'),
  CHECK (left_at IS NULL OR joined_at IS NOT NULL)
) STRICT;

CREATE INDEX idx_subreddits_tier ON subreddits(tier, yield_score DESC);
CREATE INDEX idx_subreddits_member ON subreddits(is_member) WHERE is_member = 1;
CREATE INDEX idx_subreddits_settling ON subreddits(settling_until)
  WHERE settling_until IS NOT NULL;
CREATE INDEX idx_subreddits_excluded ON subreddits(key)
  WHERE safety_excluded = 1 OR blocked = 1;

• idx_subreddits_tier — the harvest planner selects sources tier by tier and applies per-tier document caps; this serves that ordering directly.
• idx_subreddits_member — a partial index over the small membership set, used by the membership reconciliation step to diff local state against the live subscription list.
• idx_subreddits_settling — the membership evaluator must skip subreddits still inside their settling period; a partial index avoids scanning the long tail of candidates.
• idx_subreddits_excluded — the harvest planner resolves the never-harvest set in one scan.

The two table-level CHECKs encode invariants that would otherwise live only in prose: a blocked subreddit is always in tier blocked, and you cannot have left something you never joined.

discovery_source admits exactly seven values, and five of them — mention, crosspost, search, peer_suggestion, operator_history — are the five discovery sources Section 11.3 defines, one for one. The remaining two are not discoveries at all: existing_subscription is a community the operator already belonged to at bootstrap, and operator is one the operator named by hand. discovery_strength stores the combined strength Section 11.3 computes for the row, after the per-source weights in membership.discoverySources (Section 6.2.5) have been applied exactly once, so the number a membership decision reads is the number that was computed and not a re-derivation.

safety_excluded marks a community on the sensitive-community denylist owned by Section 21.8.1 and extended by safety.excludedSubreddits (Section 6.2.21). It is distinct from blocked, which is the operator's own choice. Skipping a subreddit for either reason writes a run_events row naming the subreddit and the reason; a bare count is not enough to tell an operator why coverage changed.

-- Per subreddit per day accounting: the input to every membership decision.
CREATE TABLE subreddit_metrics_daily (
  subreddit           TEXT    NOT NULL REFERENCES subreddits(key) ON DELETE CASCADE,
  day                 TEXT    NOT NULL,               -- YYYY-MM-DD, America/New_York
  run_id              TEXT    REFERENCES runs(id) ON DELETE SET NULL,
  docs_harvested      INTEGER NOT NULL DEFAULT 0 CHECK (docs_harvested >= 0),
  docs_new            INTEGER NOT NULL DEFAULT 0 CHECK (docs_new >= 0),
  candidates          INTEGER NOT NULL DEFAULT 0 CHECK (candidates >= 0),
  demand_units        INTEGER NOT NULL DEFAULT 0 CHECK (demand_units >= 0),
  themes_touched      INTEGER NOT NULL DEFAULT 0 CHECK (themes_touched >= 0),
  published_contrib   INTEGER NOT NULL DEFAULT 0 CHECK (published_contrib >= 0),
  unique_authors      INTEGER NOT NULL DEFAULT 0 CHECK (unique_authors >= 0),
  safety_excluded     INTEGER NOT NULL DEFAULT 0 CHECK (safety_excluded >= 0),
  mean_pillar_affinity REAL   NOT NULL DEFAULT 0.0
                        CHECK (mean_pillar_affinity BETWEEN 0.0 AND 1.0),
  yield_score         REAL    NOT NULL DEFAULT 0.0 CHECK (yield_score BETWEEN 0.0 AND 1.0),
  api_requests        INTEGER NOT NULL DEFAULT 0 CHECK (api_requests >= 0),
  created_at          TEXT    NOT NULL,
  PRIMARY KEY (subreddit, day)
) STRICT, WITHOUT ROWID;

CREATE INDEX idx_smd_day ON subreddit_metrics_daily(day DESC);
CREATE INDEX idx_smd_yield ON subreddit_metrics_daily(subreddit, day DESC, yield_score);

WITHOUT ROWID is correct here: the table is a pure composite-key fact table, always accessed by (subreddit, day) or by a range over day, and the clustered layout removes one B-tree hop per lookup while shrinking the file.

• idx_smd_day — the daily report aggregates every subreddit for one day.
• idx_smd_yield — the 28-day trailing yield window per subreddit, which is the single most frequent membership query.

mean_pillar_affinity is the day's mean demand_units.pillar_affinity for units sourced from this subreddit. It is a diagnostic only. It is not the lens-fit term L that the Recurrence Score consumes: L is computed once per theme by the function defined in Section 7.7 and is never aggregated from per-unit values. A subreddit whose mean pillar affinity is falling is a hint to look; it is never an input to a score.

-- Audit trail for every membership decision, executed or not.
CREATE TABLE membership_events (
  id                TEXT    NOT NULL PRIMARY KEY,     -- mev_<ULID>
  subreddit         TEXT    NOT NULL REFERENCES subreddits(key) ON DELETE RESTRICT,
  run_id            TEXT    REFERENCES runs(id) ON DELETE SET NULL,
  action            TEXT    NOT NULL
                      CHECK (action IN ('join','leave','promote','demote','probation','pin',
                                        'unpin','block','unblock','skip_paced','skip_settling',
                                        'deferred')),
  from_tier         TEXT    CHECK (from_tier IS NULL OR
                        from_tier IN ('core','active','probation','candidate','blocked','left')),
  to_tier           TEXT    CHECK (to_tier IS NULL OR
                        to_tier IN ('core','active','probation','candidate','blocked','left')),
  reason_code       TEXT    NOT NULL,
  reason_text       TEXT    NOT NULL,
  evidence_json     TEXT    NOT NULL DEFAULT '{}' CHECK (json_valid(evidence_json)),
  actor             TEXT    NOT NULL DEFAULT 'routine'
                      CHECK (actor IN ('routine','operator','doctor','reconciliation')),
  dry_run           INTEGER NOT NULL DEFAULT 0 CHECK (dry_run IN (0, 1)),
  executed          INTEGER NOT NULL DEFAULT 0 CHECK (executed IN (0, 1)),
  deferred_count    INTEGER NOT NULL DEFAULT 0 CHECK (deferred_count >= 0),
  api_response_code INTEGER,
  api_error         TEXT,
  occurred_at       TEXT    NOT NULL
) STRICT;

CREATE INDEX idx_membership_events_sub ON membership_events(subreddit, occurred_at DESC);
CREATE INDEX idx_membership_events_action_day
  ON membership_events(action, occurred_at DESC) WHERE executed = 1;
CREATE INDEX idx_membership_events_run ON membership_events(run_id);
CREATE INDEX idx_membership_events_deferred ON membership_events(subreddit, occurred_at DESC)
  WHERE action = 'deferred';

• idx_membership_events_sub — "why is this subreddit on probation?" answered in one scan.
• idx_membership_events_action_day — the pacing check counts executed joins in the trailing 24 hours and 7 days; the partial predicate excludes dry-run and skipped rows so the count is correct by construction rather than by remembering a WHERE.
• idx_membership_events_run — assembling the run report.
• idx_membership_events_deferred — an action deferred on three consecutive runs is an alert in Section 20.5; deferred_count carries the streak and this index finds it.

ON DELETE RESTRICT on subreddit is deliberate: a subreddit row is never deleted, only moved to tier left or blocked, and this constraint enforces that the audit trail can never be orphaned. This table is also the tier history — there is no separate tier-history table, and no section should invent one.

-- Harvested Reddit posts and comments. The largest table in the schema.
CREATE TABLE documents (
  id                 TEXT    NOT NULL PRIMARY KEY,     -- Reddit fullname: t3_… or t1_…
  kind               TEXT    NOT NULL CHECK (kind IN ('post','comment')),
  subreddit          TEXT    NOT NULL REFERENCES subreddits(key) ON DELETE RESTRICT,
  parent_id          TEXT,                             -- fullname of the immediate parent (comments)
  link_id            TEXT,                             -- fullname of the root post (comments)
  created_utc        INTEGER NOT NULL CHECK (created_utc > 0),
  created_at_iso     TEXT    NOT NULL,
  fetched_at         TEXT    NOT NULL,
  first_run_id       TEXT    REFERENCES runs(id) ON DELETE SET NULL,
  last_seen_run      TEXT    REFERENCES runs(id) ON DELETE SET NULL,
  observation_count  INTEGER NOT NULL DEFAULT 1 CHECK (observation_count >= 1),
  title              TEXT,
  body               TEXT,                             -- nulled by the retention job at 90 days
  body_hash          TEXT    CHECK (body_hash IS NULL OR length(body_hash) = 64),
  body_chars         INTEGER NOT NULL DEFAULT 0 CHECK (body_chars >= 0),
  body_pruned_at     TEXT,
  score              INTEGER NOT NULL DEFAULT 0,
  num_comments       INTEGER NOT NULL DEFAULT 0 CHECK (num_comments >= 0),
  upvote_ratio       REAL    CHECK (upvote_ratio IS NULL OR upvote_ratio BETWEEN 0.0 AND 1.0),
  permalink          TEXT    NOT NULL,
  flair              TEXT,
  is_self            INTEGER NOT NULL DEFAULT 1 CHECK (is_self IN (0, 1)),
  over_18            INTEGER NOT NULL DEFAULT 0 CHECK (over_18 IN (0, 1)),
  removed            INTEGER NOT NULL DEFAULT 0 CHECK (removed IN (0, 1)),
  removed_by_category TEXT,                            -- moderator, author, automod, …
  locked             INTEGER NOT NULL DEFAULT 0 CHECK (locked IN (0, 1)),
  stickied           INTEGER NOT NULL DEFAULT 0 CHECK (stickied IN (0, 1)),
  lang               TEXT    NOT NULL DEFAULT 'unknown',
  lang_confidence    REAL    NOT NULL DEFAULT 0.0 CHECK (lang_confidence BETWEEN 0.0 AND 1.0),
  author_hash        TEXT    NOT NULL CHECK (length(author_hash) = 64),
  is_op_deleted      INTEGER NOT NULL DEFAULT 0 CHECK (is_op_deleted IN (0, 1)),
  safety_excluded    INTEGER NOT NULL DEFAULT 0 CHECK (safety_excluded IN (0, 1)),
  safety_category    TEXT    CHECK (safety_category IS NULL OR
                       safety_category IN ('self_harm','medical_crisis','legal_jeopardy',
                                           'minor_safety','acute_personal_crisis',
                                           'financial_crisis')),
  safety_confidence  REAL    CHECK (safety_confidence IS NULL
                                    OR safety_confidence BETWEEN 0.0 AND 1.0),
  safety_screened_at TEXT,
  liveness_checked_at TEXT,
  CHECK (kind = 'post' OR link_id IS NOT NULL),
  CHECK (body IS NOT NULL OR body_pruned_at IS NOT NULL OR body_chars = 0),
  CHECK (safety_excluded = 0 OR safety_category IS NOT NULL)
) STRICT;

CREATE INDEX idx_documents_sub_created ON documents(subreddit, created_utc DESC);
CREATE INDEX idx_documents_link ON documents(link_id) WHERE kind = 'comment';
CREATE INDEX idx_documents_fetched ON documents(fetched_at DESC);
CREATE INDEX idx_documents_prune ON documents(created_at_iso)
  WHERE body IS NOT NULL;
CREATE INDEX idx_documents_body_hash ON documents(body_hash) WHERE body_hash IS NOT NULL;
CREATE INDEX idx_documents_run ON documents(first_run_id);
CREATE INDEX idx_documents_safety ON documents(safety_category, safety_screened_at DESC)
  WHERE safety_excluded = 1;

• idx_documents_sub_created — the harvest overlap window ("everything in this subreddit newer than the watermark minus 15 minutes", reddit.overlapMinutes, Section 6.2.3) and the candidate filter both scan this range.
• idx_documents_link — assembling a post plus its comment tree for extraction context.
• idx_documents_fetched — incremental exports and debugging "what did the last run pull?".
• idx_documents_prune — a partial index containing only rows that still hold body text, so the nightly retention sweep touches exactly the rows it may need to null and nothing else. As bodies are pruned, rows drop out of this index and the sweep gets cheaper over time.
• idx_documents_body_hash — near-duplicate detection across subreddits. Duplicates are expressed by body_hash equality and nothing else; there is no duplicate_of column, because a pointer would have to be maintained and a hash does not. The same question crossposted to four communities must not count as four independent pieces of evidence for Breadth (Section 13).
• idx_documents_run — per-run accounting.
• idx_documents_safety — the run report counts excluded documents by category; this is that count without a scan.

Three notes on columns that other sections depend on:

• The table stores body and body_hash. There is no raw_text, no normalized_text, and no content_hash; normalization is not persisted as a second text column, because a second copy of every body would double the largest table in the schema to store something that is cheap to recompute and that changes whenever the normalizer changes.
• Candidacy is not a column here. A document's candidacy for one run is a row in candidates; there is no documents.candidate_passed.
• author_hash is defined once, in RDSR-DAT-008, and is NOT NULL.

-- Incremental harvest cursors, one per (subreddit, listing).
CREATE TABLE harvest_watermarks (
  subreddit          TEXT    NOT NULL REFERENCES subreddits(key) ON DELETE CASCADE,
  listing            TEXT    NOT NULL
                       CHECK (listing IN ('new','hot','rising','top_hour','top_day','top_week',
                                          'top_month','comments')),
  last_seen_fullname TEXT,
  last_created_utc   INTEGER NOT NULL DEFAULT 0 CHECK (last_created_utc >= 0),
  last_run_id        TEXT    REFERENCES runs(id) ON DELETE SET NULL,
  last_success_at    TEXT,
  consecutive_empty  INTEGER NOT NULL DEFAULT 0 CHECK (consecutive_empty >= 0),
  consecutive_errors INTEGER NOT NULL DEFAULT 0 CHECK (consecutive_errors >= 0),
  updated_at         TEXT    NOT NULL,
  PRIMARY KEY (subreddit, listing)
) STRICT, WITHOUT ROWID;

CREATE INDEX idx_watermarks_empty ON harvest_watermarks(consecutive_empty DESC)
  WHERE consecutive_empty >= 3;

• idx_watermarks_empty — a partial index over the small set of listings that keep returning nothing, which is the signal that a subreddit has gone quiet and should be re-evaluated for membership. A full-table read would also work at this size; the partial index costs almost nothing and keeps the query intent explicit.

-- Documents that passed the cheap pre-model filters and are eligible for extraction.
CREATE TABLE candidates (
  id              TEXT    NOT NULL PRIMARY KEY,      -- cnd_<ULID>
  document_id     TEXT    NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  run_id          TEXT    NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
  subreddit       TEXT    NOT NULL REFERENCES subreddits(key) ON DELETE RESTRICT,
  filter_score    REAL    NOT NULL CHECK (filter_score BETWEEN 0.0 AND 1.0),
  reasons_json    TEXT    NOT NULL DEFAULT '[]' CHECK (json_valid(reasons_json)),
  rejected        INTEGER NOT NULL DEFAULT 0 CHECK (rejected IN (0, 1)),
  reject_reason   TEXT,
  context_doc_ids TEXT    NOT NULL DEFAULT '[]' CHECK (json_valid(context_doc_ids)),
  extracted       INTEGER NOT NULL DEFAULT 0 CHECK (extracted IN (0, 1)),
  extracted_at    TEXT,
  unit_count      INTEGER NOT NULL DEFAULT 0 CHECK (unit_count >= 0),
  created_at      TEXT    NOT NULL,
  UNIQUE (run_id, document_id)
) STRICT;

CREATE INDEX idx_candidates_run_score ON candidates(run_id, filter_score DESC)
  WHERE rejected = 0;
CREATE INDEX idx_candidates_document ON candidates(document_id);
CREATE INDEX idx_candidates_pending ON candidates(run_id) WHERE extracted = 0 AND rejected = 0;

• idx_candidates_run_score — the extraction stage takes the top filter.maxCandidatesPerRun rows by score; this index makes that an ordered index scan with no sort.
• idx_candidates_document — "has this document already been a candidate?" during dedupe.
• idx_candidates_pending — crash resume: find the work not yet done in this run.

The UNIQUE (run_id, document_id) constraint is what makes the candidate filter idempotent: a re-run of the stage uses INSERT … ON CONFLICT (run_id, document_id) DO UPDATE and converges.

A document flagged documents.safety_excluded = 1 never becomes a candidate. That screen runs in normalize, before any model call, so an excluded document is never sent to the extraction model (Section 21.8.1 owns the categories; Section 12.4.7 owns the lexicon and the classifier call site). The count per category lands in runs.docs_safety_excluded and, broken out, in the run report.

5.3.3 Meaning: demand units, embeddings, themes #

-- One extracted unmet need. The atomic unit of evidence.
CREATE TABLE demand_units (
  id               TEXT    NOT NULL PRIMARY KEY,     -- du_<ULID>
  document_id      TEXT    NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  candidate_id     TEXT    REFERENCES candidates(id) ON DELETE SET NULL,
  run_id           TEXT    NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
  subreddit        TEXT    NOT NULL REFERENCES subreddits(key) ON DELETE RESTRICT,
  type             TEXT    NOT NULL
                     CHECK (type IN ('unanswered_question','recurring_problem','contested_advice',
                                     'explainer_gap','tooling_gap','decision_paralysis',
                                     'emotional_support','terminology_confusion',
                                     'credibility_dispute')),
  need_statement   TEXT    NOT NULL CHECK (length(need_statement) BETWEEN 12 AND 400),
  audience         TEXT    NOT NULL,
  audience_norm    TEXT    NOT NULL,                 -- lowercased, singularized for grouping
  intensity        REAL    NOT NULL CHECK (intensity BETWEEN 0.0 AND 1.0),
  unmet_confidence REAL    NOT NULL CHECK (unmet_confidence BETWEEN 0.0 AND 1.0),
  answer_deficit   REAL    NOT NULL DEFAULT 0.0 CHECK (answer_deficit BETWEEN 0.0 AND 1.0),
  pillar_affinity  REAL    NOT NULL DEFAULT 0.0 CHECK (pillar_affinity BETWEEN 0.0 AND 1.0),
  candidate_score  REAL    CHECK (candidate_score IS NULL OR candidate_score BETWEEN 0.0 AND 1.0),
  author_hash      TEXT    NOT NULL CHECK (length(author_hash) = 64),
  evidence_span    TEXT    NOT NULL CHECK (length(evidence_span) BETWEEN 12 AND 400),
  evidence_offset  INTEGER NOT NULL DEFAULT 0 CHECK (evidence_offset >= 0),
  evidence_end     INTEGER NOT NULL DEFAULT 0 CHECK (evidence_end >= 0),
  evidence_match_kind TEXT NOT NULL DEFAULT 'exact'
                     CHECK (evidence_match_kind IN ('exact','whitespace_normalized',
                                                    'case_folded','nfkc_normalized')),
  mirror_subreddits TEXT   NOT NULL DEFAULT '[]' CHECK (json_valid(mirror_subreddits)),
  doc_created_utc  INTEGER NOT NULL CHECK (doc_created_utc > 0),
  local_day        TEXT    NOT NULL,                 -- YYYY-MM-DD America/New_York of doc creation
  extracted_at     TEXT    NOT NULL,
  model            TEXT    NOT NULL,
  prompt_version   TEXT    NOT NULL,
  theme_id         TEXT    REFERENCES themes(id) ON DELETE SET NULL,
  theme_similarity REAL    CHECK (theme_similarity IS NULL OR theme_similarity BETWEEN -1.0 AND 1.0),
  assigned_at      TEXT,
  hold_runs        INTEGER NOT NULL DEFAULT 0 CHECK (hold_runs >= 0),
  superseded_by    TEXT    REFERENCES demand_units(id) ON DELETE SET NULL,
  UNIQUE (document_id, need_statement),
  CHECK (evidence_end = evidence_offset + length(evidence_span))
) STRICT;

CREATE INDEX idx_du_theme ON demand_units(theme_id, doc_created_utc DESC);
CREATE INDEX idx_du_day ON demand_units(local_day, subreddit);
CREATE INDEX idx_du_unassigned ON demand_units(run_id) WHERE theme_id IS NULL;
CREATE INDEX idx_du_subreddit_day ON demand_units(subreddit, local_day DESC);
CREATE INDEX idx_du_type ON demand_units(type, local_day DESC);
CREATE INDEX idx_du_run ON demand_units(run_id);
CREATE INDEX idx_du_author_theme ON demand_units(theme_id, author_hash);

• idx_du_theme — evidence listing for a theme, newest first; the Notion writer and the chat digest both need this.
• idx_du_day — the 14-day rolling window that feeds Persistence and Breadth.
• idx_du_unassigned — the clustering stage's work queue.
• idx_du_subreddit_day — per-subreddit yield, the membership input.
• idx_du_type — type mix reporting, and the recommendation stage's format heuristics.
• idx_du_run — per-run accounting and rollback.
• idx_du_author_theme — the author-diversity guard in Section 13 asks "how many distinct people does this theme's evidence come from?", which is a COUNT(DISTINCT author_hash) per theme; this index answers it without touching documents.

UNIQUE (document_id, need_statement) prevents the same document from contributing the identical need twice when extraction is retried after a partial failure. Because need_statement is model-generated and therefore not perfectly stable, this is a cheap guard, not the whole dedupe story — semantic dedupe happens at clustering time in Section 13.

need_statement is 12 to 400 characters, and that is the only length bound the field has. The lower bound admits a genuinely terse need ("no one explains pre-bunking timing"); the upper bound is the same 400 as evidence_span and themes.canonical_need, so the three text fields that carry a need share one ceiling and one storage profile. Section 12's extraction schema and Section 26's schema index state this same 12–400 bound and no other.

Nine columns need their meaning fixed precisely, because other sections read them:

• answer_deficit is the extractor's estimate, in [0, 1], of how far the thread fell short of answering its own question: 0.0 when a highly-upvoted reply resolves it, near 1.0 when the question sits unanswered or the replies contradict each other. It is a separate signal from unmet_confidence, which is the extractor's confidence that a need was expressed at all. Section 13's Unmet component consumes both — the confidence gates whether the unit counts, the deficit sets how much it counts for — which is why both are stored and neither is derived from the other.
• author_hash is denormalized from documents.author_hash under the identical construction in RDSR-DAT-008, and exists so the author-diversity guard is a single-index question rather than a join against the largest table in the schema. It is NOT NULL and 64 characters, and it is the copy that survives when the document body is pruned. It is never selected into an operator-facing projection; see Q2 in Section 5.9.
• candidate_score is a copy of candidates.filter_score for the candidate this unit came from, carried forward because candidates rows are deleted at 90 days while units live for 365. It is nullable for the one case where a unit was produced outside the candidate path — a re-extraction of an already-stored document during quarantine release.
• evidence_offset and evidence_end are the half-open character offsets of evidence_span inside the document body at extraction time, and the table-level CHECK (evidence_end = evidence_offset + length(evidence_span)) makes the pair unable to describe a slice that is not the stored span. This constraint is the schema's half of the anti-hallucination gate: an offset triple that would slice a truncated quotation cannot be written at all, so the gate cannot be defeated by an arithmetic slip in the caller.
• evidence_match_kind records how that gate matched the model's quotation against the stored body: exact when the bytes were identical, whitespace_normalized when only runs of whitespace differed, case_folded when only case differed, nfkc_normalized when Unicode normalization was required. There is deliberately no unmatched value — a unit whose span does not match its document is rejected before insert, so the absence of that value is what makes "every stored unit is grounded" a property of the schema instead of a claim about the code.
• mirror_subreddits is the JSON array of other subreddit keys in which the same documents.body_hash was seen inside the window. It is what the crosspost discount in Section 13 reads, and it is an array rather than a count because the Breadth component needs to know which communities were mirrors in order to avoid counting them as independent breadth. It defaults to [] and is empty for the large majority of units.
• evidence_span is the verbatim excerpt, NOT NULL, between 12 and 400 characters. The column constraint is the storage ceiling; the binding limit is safety.maxEvidenceSpanWords (default 40, and 40 is also its maximum), applied at extraction and again at render time on the way into Notion and chat. Storing up to 400 characters while publishing at most 40 words is deliberate: the stored span keeps enough surrounding context to be re-excerpted if the render rule changes, and the 40-word cap is a platform-terms commitment (Section 21.6.5) that cannot be raised by configuration. The span is never nulled — a demand unit without its evidence cannot be explained, so at the end of its retention window the whole row is deleted instead (Section 5.7).
• pillar_affinity is a per-unit diagnostic: the cosine similarity between the unit's vector and the nearest active lens pillar centroid. It is not L. L is computed once per theme by computeLensFit in Section 7.7, from the theme's in-window evidence, and Section 13 consumes that result directly. Nothing aggregates pillar_affinity into a score. It exists so that "which pillar does this piece of evidence look like?" is answerable in the explain view and in the drift diagnostics, and for no other purpose.
• hold_runs counts how many runs an ambiguous unit has been held without being assigned to a theme; at cluster.holdUnassignedRuns it is either seeded as a new theme or left unassigned permanently.

-- Polymorphic vector store. One row per (owner, model).
CREATE TABLE embeddings (
  id           TEXT    NOT NULL PRIMARY KEY,          -- emb_<ULID>
  owner_type   TEXT    NOT NULL
                 CHECK (owner_type IN ('demand_unit','theme_centroid','lens_pillar',
                                       'corpus_item','document','query')),
  owner_id     TEXT    NOT NULL,
  model        TEXT    NOT NULL,
  dim          INTEGER NOT NULL CHECK (dim BETWEEN 64 AND 8192),
  vector       BLOB    NOT NULL,
  norm         REAL    NOT NULL DEFAULT 1.0 CHECK (norm > 0.0),
  input_hash   TEXT    NOT NULL CHECK (length(input_hash) = 64),
  created_at   TEXT    NOT NULL,
  run_id       TEXT    REFERENCES runs(id) ON DELETE SET NULL,
  UNIQUE (owner_type, owner_id, model),
  CHECK (length(vector) = dim * 4)
) STRICT;

CREATE INDEX idx_embeddings_owner ON embeddings(owner_type, model);
CREATE INDEX idx_embeddings_input_hash ON embeddings(input_hash, model);
CREATE INDEX idx_embeddings_unit_age ON embeddings(created_at)
  WHERE owner_type = 'demand_unit';