> ## Documentation Index > Fetch the complete documentation index at: https://docs.tryrekkal.com/llms.txt > Use this file to discover all available pages before exploring further. # Data model proposal # rekkal v1 data model — proposal for review **Status: APPROVED and shipped.** Kept as the design record and the rationale behind the schema — the authoritative DDL is now `supabase/migrations/20260725*.sql`, applied to `dthdxgrrktaavshxjqjr` and verified against the hosted database. Where the two ever disagree, the migrations win. Decisions returned on the four open questions, and what changed as a result: | | Decision | Effect | | ------------------------ | ---------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **T6** competitor limits | Free = **3** tracked competitors; Pro = **unlimited**, no plan cap. Auto-discovered brands stay `suggested` and never count. | `config.limits`. A defensive ceiling of **5,000 brands per workspace** guards the auto-discovery write path; it is a stability guard, not a pricing lever — see [§13](#13-amendments-made-during-implementation). | | **T2** shared corpus | **Global** `domains` / `urls`, so crawl and classification cost amortises across customers. | Shipped, with the shared/not-shared boundary written into the migration header. | | **T8** `config.ts` | Add the machine-readable `limits` block now; leave `plans.*.features` marketing prose to the pricing task. | `features` strings untouched. | | **T7** extraction COGS | Accepted as a known fourth cost line (\~€2/customer/month). | No change; `runs.extraction_status` records when Position is not yet trustworthy. | T1, T3, T4 and T5 were approved as recommended. [§13](#13-amendments-made-during-implementation) records the two changes made during implementation that this document did not originally propose. **Authority:** [`decisions.md`](https://github.com/maximebrmd/rekkal) (captain, 2026-07-25) and the Peec teardown `report.md` §4, §P2.4, §P2.6. Where this proposal departs from the report, it says so and why. This is the schema every other rekkal feature aggregates over. Migrating it later is the expensive mistake, so it is written concretely — real column names, real SQL, real indexes — rather than as a description of possibilities. Every genuine trade-off I could not settle is in [§12](#12-open-trade-offs), with a recommendation. *** ## 1. The one-paragraph summary One workspace is one tracked brand in one market, and it is the tenancy boundary carried on **every** fact row from day one. The atom is `public.runs`: one row is *one prompt, sent to one engine, for one scheduled slot date*, and it is simultaneously the answer record and **the unit the meter counts**. Mentions are stored at occurrence grain with UTF-16 character offsets into the stored answer, so the product can highlight the exact sentence. Brand identity is protected by two unique indexes that make the incumbent's two documented entity-resolution failures structurally impossible. No column anywhere in the schema holds a ratio — every rollup stores sums and counts, so every number the product ever displays can report the `n` it was computed from, and an absent comparison window is `NULL` by construction rather than a delta against zero. Runs and the meter have **no authenticated write policy at all**: they are written only by the scheduled runner through the service-role client. That is necessary but not sufficient on its own — the metering functions are `SECURITY DEFINER`, PostgREST publishes them as RPC endpoints, and Postgres grants EXECUTE to PUBLIC by default, so keeping the meter off the browser also depends on those grants being revoked ([§9](#9-rls-posture)). *** ## 2. Naming and the tenancy boundary The report sketched `organization → project`. This proposal collapses that to a single level, **`workspace`**, and makes it the per-client boundary. | | Report's shape | This proposal | | ------------------------------ | -------------- | ---------------------------------- | | Billing / seat container | `organization` | `workspaces` + `workspace_members` | | Per-client analytics container | `project` | the same `workspaces` row | | Column on fact tables | `project_id` | `workspace_id` | **Why one level.** The thing that is catastrophic to add later is a tenancy column on the fact tables — `runs`, `run_mentions`, `run_citations` and the rollups are the tables that grow without bound. The thing that is cheap to add later is a container *above* the boundary. Decision #3 says the agency layer must be additive, and the additive path is: ```sql theme={null} -- The entire future agency migration, sketched: create table public.organizations (id uuid primary key, name text not null, …); alter table public.workspaces add column organization_id uuid references public.organizations(id); -- billing moves from workspace owner to organization; no fact table is touched. ``` If instead `workspace` were the org and `project` the client, every fact table would need a second FK today for a level v1 has no use for — and `is_workspace_member()` would have to become a two-hop check. One boundary, carried everywhere, is the smaller bet. **Membership from day one, even though v1 has one seat.** `workspace_members` exists now with exactly one `owner` row per workspace, and *every* RLS policy in the schema routes through one helper, `public.is_workspace_member(uuid)`. Adding seats later is an `INSERT`, not a policy rewrite across 12 tables. This is the single highest-leverage forward-compatibility decision in the proposal and it costs one table. *** ## 3. Entities and relations ``` auth.users └── workspace_members ──┐ │ (v1: exactly one 'owner' row per workspace) workspaces ─────────────┘ one tracked brand · one market · one cadence · the tenancy boundary │ ├── brands name, kind('self'|'competitor'), tracking_state, discovery_source, │ │ primary_domain + domain_status, colour │ └── brand_aliases alias → exactly one brand per workspace (unique, normalised) │ ├── topics name, position │ └── prompts text, country_code, language, intent_class, branding_class, is_active │ │ │ └── runs ◄──── THE ATOM. one (prompt × engine × run_date) │ │ answer_text, model_version, status, metered, cost_micros, latency_ms │ ├── run_mentions brand_id, char_start/char_end, sentence span, surface_form, │ │ match_method, is_recommended │ └── run_citations url_id, raw_uri, order_index, resolution_status │ ├── usage_periods period_start/end, plan_id, run_limit, runs_used, cost_micros_used ├── brand_daily_metrics run_count, mention_run_count, mention_count, position_sum/count └── domain_daily_metrics run_count, retrieved_run_count, retrieval_count engines global reference: 'perplexity-sonar' | 'openai-web-search' | 'gemini-grounded' | 'fixture' domains ── urls global reference corpus (see §6 and trade-off T2) ``` **Twelve workspace-scoped tables, three global reference tables.** Every workspace-scoped table carries `workspace_id` directly — never "reachable by join". Consistency between the denormalised `workspace_id` and the parent's is not left to convention: child tables use a **composite foreign key** `(parent_id, workspace_id) → parent(id, workspace_id)`, so a row physically cannot claim a workspace its parent does not belong to. **What is deliberately absent from v1**, with reasons: | Not modelled | Why | Cost to add later | | -------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- | | `sentiment` | Report §5.2: a 0–100 scale whose documented real range is 65–85 is noise in a precision costume. Not in the decisions doc. | One additive table `run_brand_sentiment(run_id, brand_id, score)`. Needs the raw answer, so add it **before** archival kicks in (§5). | | `prompt_tags` (many-to-many) | Report §4.2 trap #2 — "never sum across tags" — is a bug class that only exists if tags are many-to-many. Modelling topic as a single-valued FK makes the over-count **structurally impossible**. | A separate `prompt_tags` table, with an explicit rule that aggregates never sum across it. | | `workspace_engines` | Enabled engines are a function of the plan (`config.ts`). Per-workspace overrides are speculative. Which engine a historical run used is already recorded on `runs.engine_id`. | One additive table. See trade-off **T5**. | | Materialised per-run brand ranks | Position is `rank() over (partition by run_id order by min(char_start))`, computed once in the daily rollup. A second denormalised copy is a consistency liability for no read benefit at v1 scale. | Derivable at any time from `run_mentions`. See **T3**. | | Source page-content crawling | Report §4.5 #4: a second crawler with its own storage and robots posture. Out of scope. | `urls` already has the FK slot for it. | *** ## 4. The atom: `public.runs` ### 4.1 What one row means > **One row in `public.runs` is one prompt, sent to one engine, for one scheduled slot date, in one workspace. It carries the answer text exactly as that engine returned it, the execution parameters it actually ran under, and it is the single unit the run meter counts.** Three consequences follow from that sentence, and they are the whole design: 1. **`(prompt_id, engine_id, run_date)` is unique.** Re-running is an upsert, never an insert. This is the idempotency guarantee the brief requires. 2. **`run_date` is the *scheduled slot*, not the wall clock.** For weekly cadence it is the Monday of the ISO week in the workspace timezone. A run that fails on Monday and is retried on Tuesday **reuses the same row and the same slot** — which is precisely why retries cannot double-count. Actual execution time lives in `started_at` / `completed_at`, and `attempt_count` records how many tries it took. 3. **Quota unit = row count. COGS = `sum(cost_micros)`.** These are deliberately different numbers. A customer is charged one unit for a slot no matter how many times we retry it; the business still sees the true spend. The meter invariant is checkable in one query (§8.4). ### 4.2 Grain, keys, indexes | | | | ------------------------------- | -------------------------------------------------------------------------------- | | **Grain** | prompt × engine × scheduled slot date | | **Primary key** | `id uuid` (surrogate — children FK to it) | | **Natural key** | `unique (prompt_id, engine_id, run_date)` → `runs_atom_key` | | **Tenancy key** | `unique (id, workspace_id)` → the FK target children use to inherit the boundary | | **Read path: timeline** | `(workspace_id, run_date desc)` | | **Read path: per-engine slice** | `(workspace_id, engine_id, run_date desc)` | | **Write path: runner queue** | partial `(workspace_id, status) where status in ('pending','running')` | | **Meter path** | partial `(workspace_id, run_date) where metered` | Country and language are **on the prompt**, not a run-grain dimension: the same question in three countries is three prompt rows, which is also the correct pricing (three countries genuinely is 3× the runs). They are then *copied onto the run at dispatch* — along with `model_version` — because a run is an immutable record of what actually executed. Editing a prompt's country must not silently rewrite history. `answer_text` is nullable with `answer_archived_at` / `answer_storage_key` alongside it, so the §4.5 archival plan (hot for 30–90 days, then object storage) is a background job rather than a migration. A `CHECK` forbids a `succeeded` run with neither an answer nor an archive pointer. ### 4.3 Answer text and character offsets — the encoding contract Mention offsets are **UTF-16 code-unit offsets into `runs.answer_text` exactly as the adapter returned it**, because the UI slices that string in JavaScript (`answerText.slice(sentenceStart, sentenceEnd)`). This matters and is easy to get silently wrong: Postgres `substring()` counts *characters*, JavaScript counts *UTF-16 code units*, and the two diverge the moment an answer contains an emoji or any astral-plane character. Fixing the convention now is free; discovering it after 200k mention rows means re-deriving every offset against archived text. The contract is: **offsets are produced in TypeScript, consumed in TypeScript, and Postgres only ever stores and compares them.** A unit test asserts an emoji-bearing answer round-trips. `answer_char_length` is stored so offset bounds can be sanity-checked without loading the text. *** ## 5. Mentions and entity resolution ### 5.1 Storage `run_mentions` is **occurrence grain** — one row per textual occurrence, not one per (run, brand). That single choice yields every metric the report confirmed arithmetically in P2.3: | Metric | Query | Confirmed by | | ---------------------- | ------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------ | | Visibility | `count(distinct run_id)` ÷ total runs | P2.3: 95 ÷ 351 = 27.1% ✓ | | Share of Voice | `count(*)` ÷ `count(*)` over **all** brands | P2.3: two brands at SoV 34% with different visibility — only a mention-count numerator produces that ✓ | | Position | `rank() over (partition by run_id order by min(char_start))` | §4.3 — ranks over every detected brand, not the tracked list | | "Show me the sentence" | `sentence_start` / `sentence_end` | the brief's requirement | Each row stores `char_start`/`char_end` (the brand name itself, for highlighting) **and** `sentence_start`/`sentence_end` (the containing sentence, for the quote card), with a `CHECK` that the sentence span contains the mention span. `surface_form` records the literal text matched, so "Efrei Paris" is visible in the UI even though it resolved to brand *Efrei*. `unique (run_id, char_start, char_end, brand_id)` makes **re-extraction idempotent** at mention grain, matching the run-grain idempotency. Re-running the extractor over an existing answer converges instead of duplicating. ### 5.2 How a mention is attributed to a brand Two stages, deliberately separable so the pipeline runs with **no provider keys at all**: **Stage 1 — deterministic alias match (always runs, free, unit-testable).** Every brand has a set of aliases in `brand_aliases`. Each alias is normalised by `public.rekkal_normalize()` — lowercase, accent-folded, whitespace-collapsed — and matched with word-boundary awareness against the answer. Produces exact offsets, `match_method = 'alias'`, `match_confidence = 1.00`, and a `matched_alias_id` so every mention is traceable to the rule that created it. Accent folding is not optional in this market: *Télécom Paris* and *Telecom Paris* are the same school and P2.6's ranking bug was about exactly that school. **Stage 2 — open-ended LLM extraction (optional, requires a key).** Report §4.3 is unambiguous: Position cannot be computed by matching a list of tracked names, because an untracked brand appearing ahead of you changes your position. That requires NER over every answer to find *all* brands in mention order. Stage 2 writes mentions with `match_method = 'llm'` for brands not in the tracked set, creating `brands` rows with `discovery_source = 'auto'`, `tracking_state = 'suggested'`. **The honesty hook.** `runs.extraction_status` records which stage actually ran (`'alias_only'` | `'llm'`). An aggregate computed over `alias_only` runs has a *knowably incomplete* brand set, so Position over it is not trustworthy — and the schema says so rather than hiding it. This is the same failure the incumbent ships silently (P2.6: visibility rank says #2, position says #5.4, nothing reconciles them). Recording the extraction mode is what lets the read layer refuse to render Position until Stage 2 has run. ### 5.3 Avoiding the incumbent's two documented resolution failures Report P2.6 records two precision failures. Both are prevented by DDL, not by application care. **Failure 1 — "Efrei" and "Efrei Paris" as two brands sharing `efrei.fr`.** ```sql theme={null} create unique index brands_unique_domain_per_workspace on public.brands (workspace_id, normalized_primary_domain) where normalized_primary_domain is not null and domain_status <> 'rejected'; ``` A domain identifies at most one brand per workspace. The second candidate cannot become a brand; it becomes an **alias** of the first. Reinforced at the alias level: ```sql theme={null} create unique index brand_aliases_unique_per_workspace on public.brand_aliases (workspace_id, normalized_alias); ``` One normalised alias resolves to exactly one brand, so the mention resolver is **deterministic** rather than first-match-wins. This is what makes Stage 1 reproducible: the same answer text always produces the same mentions. **Failure 2 — "ECE" resolved to `ece-ehlers.de`, a German company, not the French school `ece.fr`.** The root cause is that an auto-discovered brand's domain is a *guess* that gets promoted to fact. So the schema refuses the promotion: ```sql theme={null} domain_status text not null default 'unverified' check (domain_status in ('unverified','corroborated','confirmed','rejected')), domain_evidence_run_count integer not null default 0, ``` * `unverified` — an LLM or heuristic guessed it. **Never used for attribution and never joined to `domains`/Sources.** It is a display hint on the suggestions screen and nothing else. * `corroborated` — the domain has actually appeared as a cited source in runs that mention the brand. `domain_evidence_run_count` records how many. Evidence, not assertion. * `confirmed` — a human said yes. * `rejected` — a human said no; excluded from the unique index so the correct brand can claim the domain. `ece-ehlers.de` never co-occurs with mentions of the French school, so it never corroborates, so it never affects a number. The wrong answer stays inert instead of poisoning the Sources table. **Failure 3, which the report identifies as a correctness bug (P2.6 / decision #7) — ranking against a set that excludes the customer's biggest rival.** Untracked brands still get `brands` rows and still get `run_mentions` rows, so the comparison "is any brand out-mentioning us?" is a query over data we already hold, not a feature that needs new acquisition: ```sql theme={null} select b.id, b.name, b.tracking_state, count(*) as mentions from public.run_mentions m join public.brands b on b.id = m.brand_id where m.workspace_id = $1 group by b.id having count(*) > (select count(*) from public.run_mentions m2 join public.brands s on s.id = m2.brand_id where m2.workspace_id = $1 and s.kind = 'self') and b.tracking_state <> 'tracked'; -- Any row returned is a rival out-mentioning the customer while sitting outside the ranking. ``` ### 5.4 Backfill Report §4.3: editing a brand's name, aliases, or regex must trigger a backfill across historical runs, which requires the raw answer text. This is why `answer_archived_at` exists rather than a hard delete — the archive is retrievable, and the alias-edit path re-extracts. The `unique (run_id, char_start, char_end, brand_id)` index makes that re-extraction safe to run repeatedly. *** ## 6. Sources and citations `run_citations` carries the tenancy boundary; `domains` and `urls` are a **global reference corpus** shared across workspaces (see trade-off **T2**). A URL and its title are not customer data; *which run cited it* is, and that lives on `run_citations`. **The Gemini redirect gotcha (report §4.4), handled in DDL.** Gemini returns source URIs as `vertexaisearch` redirect wrappers. Storing them unresolved makes every domain in the database Google's. So: ```sql theme={null} raw_uri text not null, -- exactly what the provider returned url_id uuid references public.urls(id), -- null until resolved resolution_status text not null default 'resolved' check (resolution_status in ('resolved','unresolved','redirect_unresolved','invalid')), check (resolution_status <> 'resolved' or url_id is not null) ``` An unresolved wrapper is stored as `redirect_unresolved` with `url_id = NULL`. It is preserved for later re-resolution and it is **visibly missing** from the Sources rollup rather than silently misattributed. Resolution failure becomes a countable metric instead of a corrupt corpus. `domain_daily_metrics` stores both denominators P2.3 distinguishes: * `retrieval_count` — total citations of the domain * `retrieved_run_count` — **distinct runs** that retrieved it Citation rate is `retrieval_count / retrieved_run_count` (P2.3 confirmed values 0.4 → 2.9, consistent with the per-retrieving-chat denominator; dividing by retrieval count is the deprecated metric). Storing both means the correct one is always computable and the wrong one is never the only option. *** ## 7. Sample size as a first-class property This is the captain's positioning (decisions #6, #7) and the report's strongest finding (P2.4: every per-prompt percentage in the incumbent is quantised to ninths, and a displayed "11%" has a 95% CI of roughly 0.3%–48%). Four mechanisms, all structural. ### 7.1 No column in this schema holds a ratio Every rollup stores **sums and counts**: ``` brand_daily_metrics(workspace_id, brand_id, engine_id, bucket_date, run_count, -- ← the n, always present mention_run_count, -- visibility numerator mention_count, -- SoV numerator position_sum, position_count) ``` Because the components are stored, every ratio is recomputable at **any** aggregation level — by engine, by topic, by week, by all-engines-combined — and `n` comes along for free at each one. Report §4.1 and Peec's own API docs describe the trap this avoids: *"ratio metrics must be recombined from their underlying sums, not averaged."* A `visibility numeric` column would make that arithmetic impossible to get right and it will not exist. The naming convention is the enforcement: **every rollup column ends in `_count` or `_sum`.** Anything else is a review failure. ### 7.2 Every aggregate can report its `n`, because `n` is the same column every time `run_count` is the denominator for every brand metric, at every grain. The read contract in TypeScript is a single generic that makes `n` structurally impossible to drop: ```ts theme={null} /** A measured value that always travels with the sample it was computed from. */ export interface Measure { /** Successes — mentions, retrievals, whatever the numerator is. */ readonly successes: number; /** Trials — the sample size this was computed from. Never inferred, never assumed. */ readonly n: number; /** Point estimate, or `null` when `n === 0`. There is no `0` fallback. */ readonly value: number | null; /** Clopper–Pearson 95% interval, or `null` when `n === 0`. */ readonly interval: { low: number; high: number } | null; /** `false` when `n` is below the workspace's minimum-sample threshold. */ readonly reportable: boolean; } ``` `value: number | null` is the point: TypeScript will not let a caller render a number it does not have. Intervals are computed in `lib/stats.ts`, in TypeScript, unit-tested — not in SQL. For a product whose positioning *is* the statistics, the statistics must be the most tested code in the repo, and Postgres has no incomplete beta function. **Clopper–Pearson, not the normal approximation**, because the normal approximation gives a zero-width interval at 0/9 — exactly the case P2.4 shows the incumbent rendering as a confident "0%". Clopper–Pearson reproduces the report's published figures exactly (1/9 → 0.28%–48.2%; 0/9 → 0%–33.6%), is conservative rather than optimistic, and conservative is the correct direction of error for this product. See **T4**. ### 7.3 An absent comparison period is `NULL` by construction, never zero P2.5 is a shipped bug in the incumbent: with one day of history the KPI strip renders `Visibility 27.1% +27.1% · Position #5.4 +5.4`, because the prior period is empty and the comparison is `current − 0`. The Position delta renders as green-flavoured improvement while meaning "we had no data yesterday". Three layers prevent it: 1. **`brand_daily_metrics` has `check (run_count > 0)`.** A row exists only where runs actually happened. There is no such thing as a zero-`n` row, so "no data" is *row absence*, which SQL already models as `NULL`. 2. **Every ratio divides by `nullif(denominator, 0)`.** Division by an empty window yields `NULL`, not `0`, without any application-level care. 3. **The comparison type has no numeric fallback:** ```ts theme={null} export interface Comparison { readonly current: Measure; /** `null` when the prior window contains no runs at all. */ readonly previous: Measure | null; /** `null` whenever `previous` is `null`. Never `current - 0`. */ readonly delta: number | null; /** Why there is no comparison — rendered as copy, not as a number. */ readonly absence: "no_prior_window" | "insufficient_prior_n" | null; } ``` `delta: number | null` with `previous: Measure | null` means "no comparison" is the only thing the UI *can* render. There is deliberately no `?? 0` anywhere in the read path, and that is a lint-able invariant. ### 7.4 Distinguishing "measured zero" from "not measured" These are different facts and the schema separates them: * **Measured zero** — `brand_daily_metrics` row exists, `run_count = 12`, `mention_run_count = 0`. Visibility is genuinely 0% with n = 12. The rollup **zero-fills every `tracked` brand** for every (engine, date) that had runs, precisely so this case has a row. * **Not measured** — no row. `n` is 0. Nothing is rendered but an explanation. Untracked/auto-discovered brands are *not* zero-filled (that would be one row per suggestion per engine per day for no benefit); they get rows only where they were mentioned. Presence data for discovery, true zeros for the tracked set. ### 7.5 Where the threshold lives `workspaces.min_sample_n integer not null default 30` — per workspace, so it is auditable and adjustable, and so the *product* has a stated threshold rather than the inconsistent internal one P2.4 caught the incumbent applying (Ironhack rendered Sentiment 57 / Position #2.0 on essentially no mentions, while ETNA and Supinfo rendered blank — same product, same page, two different rules). Default 30: at weekly cadence, 10 prompts × 3 engines = 30 runs per slot, so a Pro workspace clears the bar in its first week at the aggregate level while individual *prompts* (3 runs/week) correctly stay unreportable until they accumulate. The number is a workspace column, not a constant, so changing it is configuration rather than a deploy. *** ## 8. Run metering ### 8.1 Where the meter lives **`public.usage_periods` — one row per workspace per billing period — and it is only ever moved by `public.claim_run_slot()`.** Nothing else in the codebase writes `runs_used`. ```sql theme={null} usage_periods(id, workspace_id, period_start, period_end, plan_id, run_limit, runs_used, cost_micros_used, …) unique (workspace_id, period_start) ``` Counting `runs` directly would also work at v1 scale, but a materialised period row buys three things a `count(*)` cannot: a place to lock so concurrent dispatchers serialise (§8.2), a record that survives the answer-archival plan in report §4.5, and an auditable **snapshot of the limit that was actually in force** — so raising Pro's quota next quarter does not retroactively rewrite last quarter's enforcement. `run_limit` is snapshotted from `config.ts` when the period opens, and on upgrade it moves only upward (`greatest(existing, new)`), so a mid-period plan change never retroactively puts a customer over quota. ### 8.2 The atomic claim ``` claim_run_slot(workspace_id, prompt_id, engine_id, run_date) → (run_id, created, reason) ``` One `SECURITY DEFINER` function, one transaction: 1. Does a run already exist for this `(prompt, engine, run_date)`? → return it, `created = false`, **meter untouched**. This is the idempotency guarantee. 2. `SELECT … FOR UPDATE` the workspace's open period. Concurrent dispatchers serialise on that row — per workspace, so there is no global contention. 3. `runs_used >= run_limit` → return `reason = 'quota_exhausted'`, insert nothing. 4. `INSERT … ON CONFLICT DO NOTHING`. Increment `runs_used` **only if the insert actually created a row**. There is no window in which a run exists without having been metered, or is metered twice. Quota is consumed at *dispatch*, not on success, because a dispatched run is a committed intent to spend. ### 8.3 Not charging customers for our own failures `runs.metered boolean not null default true`, plus: ``` release_run_slot(run_id) → boolean ``` Sets `metered = false` and decrements, guarded by `where metered` so it is idempotent. Called only when a run failed **before the provider billed us** (connection refused, 429 with no completion, our own crash). The row survives for audit; it just stops counting. A provider 500 *after* generation still counts, because it still cost money. ### 8.4 The invariant, and how to verify it on the hosted DB ```sql theme={null} select p.workspace_id, p.period_start, p.runs_used, (select count(*) from public.runs r where r.workspace_id = p.workspace_id and r.metered and r.run_date >= p.period_start and r.run_date < p.period_end) as actual from public.usage_periods p where p.runs_used <> (…same subquery…); -- Must return zero rows. Run it after every backfill. ``` Per `data/learnings.md` (2026-07-23), a green deploy does not prove the hosted DB has the migration. Phase B verifies schema objects by querying `dthdxgrrktaavshxjqjr` directly, and this invariant query is part of that check. ### 8.5 Quota values Decision #5 replaces the current `config.ts` quotas (Free 25 prompts/weekly; Pro €99 for 500 prompts/daily/all engines — roughly 10–30× what €99 can fund). The proposed machine-readable block: ```ts theme={null} limits: { free: { brands: 1, prompts: 10, engines: ["perplexity-sonar"], runsPerMonth: 50 }, pro: { brands: 1, prompts: 100, engines: ["perplexity-sonar", "openai-web-search", "gemini-grounded"], runsPerMonth: 1500 }, } ``` `runsPerMonth` is **derived, not chosen**: `prompts × engines × 5`, because a calendar month contains at most five weekly slots. Free = 10 × 1 × 5 = 50 (decision #5's "\~43 runs/month" is the expected value; 50 is the ceiling that stops a five-Monday month failing). Pro = 100 × 3 × 5 = 1500 (decision #5's "\~1,300 runs/month" expected). The number is written out literally rather than computed so the enforced limit is greppable and auditable. Competitor-brand limits are **not settled by the decisions doc** — see trade-off **T6**. *** ## 9. RLS posture Every table has RLS enabled. Every workspace-scoped policy is `using (public.is_workspace_member(workspace_id))` — one helper, so a future seats/roles change is one function rather than twelve policies. ```sql theme={null} create or replace function public.is_workspace_member(p_workspace_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select exists (select 1 from public.workspace_members m where m.workspace_id = p_workspace_id and m.user_id = (select auth.uid())); $$; ``` `SECURITY DEFINER` is required: without it the helper would query `workspace_members` under that table's own RLS policy, which calls the helper — infinite recursion. `search_path = ''` with fully-qualified references guards against search-path hijacking, matching the existing `handle_new_user` pattern. | Table | `authenticated` SELECT | `authenticated` writes | Written by | | ---------------------- | ----------------------------- | ------------------------------- | -------------- | | `workspaces` | member | owner may UPDATE | user | | `workspace_members` | member | **none** | service-role | | `brands` | member | member INSERT / UPDATE / DELETE | user + runner | | `brand_aliases` | member | member INSERT / UPDATE / DELETE | user + runner | | `topics` | member | member INSERT / UPDATE / DELETE | user | | `prompts` | member | member INSERT / UPDATE / DELETE | user | | `engines` | all authenticated (reference) | **none** | migration seed | | `domains` | all authenticated (reference) | **none** | runner | | `urls` | all authenticated (reference) | **none** | runner | | **`runs`** | member | **none** | runner only | | **`run_mentions`** | member | **none** | runner only | | **`run_citations`** | member | **none** | runner only | | **`usage_periods`** | member | **none** | runner only | | `brand_daily_metrics` | member | **none** | runner only | | `domain_daily_metrics` | member | **none** | runner only | ### How a scheduled job writes rows the user can only read The runner uses `lib/supabase/admin.ts` — the existing service-role client, which bypasses RLS. This is the pattern already established in this repo for the Stripe webhook, extended rather than reinvented. Entry point is a Route Handler at `app/api/cron/run-prompts/route.ts` invoked by Vercel Cron and gated on `CRON_SECRET`; the handler is server-only and the service-role key never enters a module with `"use client"`. **The security property this buys, and its limit:** the six bolded tables have *no* INSERT, UPDATE, or DELETE policy for `authenticated` — not a restrictive one, none at all. Postgres denies by default. So a user with a valid session token, calling PostgREST directly with a hand-crafted request against those *tables*, cannot fabricate a run, forge a mention, or move the meter. There is no policy to exploit, which is a stronger statement than "the API doesn't expose it". **But absent write policies are necessary, not sufficient — and this proposal originally claimed otherwise.** The functions that move the meter (`claim_run_slot`, `ensure_usage_period`, `release_run_slot`) and refresh the rollups are `SECURITY DEFINER`, so they execute with their owner's privileges and route straight around the RLS on those tables. Postgres grants EXECUTE to PUBLIC on every newly created function, Supabase does not revoke it, and PostgREST exposes everything in schema `public` as `/rest/v1/rpc/`. As first shipped, a browser holding only the publishable anon key could therefore call `ensure_usage_period(..., p_run_limit => 2147483647)` and lift its own quota without bound, `release_run_slot()` to unmeter a run, or `claim_run_slot()` to move the meter directly. `20260725120600_function_grants.sql` revokes those grants. `public.is_workspace_member(uuid)` keeps its `authenticated` grant, deliberately and by name: every workspace-scoped policy calls it and a policy is evaluated as the *calling* role, so revoking it would fail every policy closed; it is safe because it is read-only, takes only a workspace id, and returns a boolean the caller could already derive from `workspace_members` under its own RLS. The guarantee holds **only while those grants stay revoked**, so it is not a property to restate unconditionally. Any new `SECURITY DEFINER` function must ship with its `REVOKE` in the same migration; `supabase/migrations/function-grants.test.ts` fails the suite if one does not. Users retain full write access to what they legitimately curate — brands, aliases, topics, prompts — because that is their input to the pipeline, not its output. *** ## 10. Migrations Six files, following the existing `YYYYMMDDHHMMSS_name.sql` convention. Full SQL below; this is what Phase B would apply verbatim to `dthdxgrrktaavshxjqjr`. > **Validation status, stated plainly:** this SQL has been reviewed by hand but **not yet executed against a Postgres engine** — there is no local Postgres on this machine and the Docker daemon (OrbStack) is down, and Phase A is explicitly design-only. Two defects were already caught and fixed in review (a schema-qualified `ON CONFLICT` target, and the `unaccent` immutability caveat below). Phase B runs the whole set against a throwaway database *before* touching the hosted project, then verifies the hosted objects by querying them. Review the **shape** here; treat exact syntax as pending verification. ``` supabase/migrations/ 20260725120000_workspaces.sql workspaces, workspace_members, is_workspace_member(), normalisers 20260725120100_brands.sql brands, brand_aliases, entity-resolution constraints 20260725120200_prompts.sql topics, prompts, engines (+ seed) 20260725120300_runs.sql runs (the atom), run_mentions, domains, urls, run_citations 20260725120400_metering.sql usage_periods, claim_run_slot(), release_run_slot(), ensure_usage_period() 20260725120500_rollups.sql brand_daily_metrics, domain_daily_metrics, refresh functions ``` ### `20260725120000_workspaces.sql` ```sql theme={null} -- Workspaces: the tenancy boundary. One workspace is one tracked brand in one -- market. v1 has exactly one member (the owner); the agency layer adds an -- organizations table above this one without touching any fact table. create extension if not exists unaccent with schema extensions; -- Immutable text normaliser used by generated columns and unique indexes. -- -- CAVEAT, deliberate: BOTH unaccent() overloads are declared STABLE, not -- IMMUTABLE, because the accent dictionary is a file on disk. Declaring this -- wrapper IMMUTABLE is the standard documented workaround for indexing -- unaccented text; Postgres trusts the declaration. The two-argument form with -- an explicit regdictionary is used so the dictionary is pinned at parse time -- rather than resolved through search_path. The residual risk is that changing -- the unaccent rules file under a live database would silently invalidate the -- indexes below and require a REINDEX. We never change that file. create or replace function public.rekkal_normalize(value text) returns text language sql immutable parallel safe set search_path = '' as $$ select nullif( lower(btrim(regexp_replace( extensions.unaccent('extensions.unaccent'::regdictionary, coalesce(value, '')), '\s+', ' ', 'g'))), '') $$; comment on function public.rekkal_normalize(text) is 'Case-, accent- and whitespace-insensitive normal form. Immutable so it can back generated columns and unique indexes. Accent folding is mandatory: "Telecom Paris" and "Telecom Paris" must collide.'; -- Domain normaliser: strips scheme, www., port, path, query, fragment and any -- trailing dot, so efrei.fr, https://www.efrei.fr/ and EFREI.FR/ all collide. create or replace function public.rekkal_normalize_domain(value text) returns text language sql immutable parallel safe set search_path = '' as $$ select nullif( regexp_replace( regexp_replace( regexp_replace( regexp_replace(lower(btrim(coalesce(value, ''))), '^[a-z][a-z0-9+.\-]*://', ''), '[/:?#].*$', ''), '^www\.', ''), '\.$', ''), '') $$; create table if not exists public.workspaces ( id uuid primary key default gen_random_uuid(), owner_user_id uuid not null references auth.users (id) on delete cascade, name text not null, -- The customer's own domain. Seeds the self brand at onboarding. brand_domain text, -- The single market this workspace measures (decision #3: one brand, one market). country_code text not null default 'FR', language text not null default 'fr', timezone text not null default 'Europe/Paris', -- Decision #6: weekly on both tiers. Column, not constant, so the free audit -- ('once') and any future cadence are configuration. run_cadence text not null default 'weekly' check (run_cadence in ('once', 'weekly')), -- Minimum sample size below which an aggregate is not reportable (section 7.5). -- Per workspace so the threshold is auditable and never applied inconsistently. min_sample_n integer not null default 30 check (min_sample_n > 0), first_run_completed_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); comment on table public.workspaces is 'Tenancy boundary. One tracked brand in one market. Every fact table carries workspace_id directly.'; create index if not exists workspaces_owner_idx on public.workspaces (owner_user_id); create trigger workspaces_set_updated_at before update on public.workspaces for each row execute function public.set_updated_at(); create table if not exists public.workspace_members ( workspace_id uuid not null references public.workspaces (id) on delete cascade, user_id uuid not null references auth.users (id) on delete cascade, role text not null default 'owner' check (role in ('owner', 'admin', 'member')), created_at timestamptz not null default now(), primary key (workspace_id, user_id) ); comment on table public.workspace_members is 'Membership. v1 writes exactly one owner row per workspace, but every RLS policy in the schema routes through is_workspace_member() so adding seats is an INSERT, not a policy rewrite.'; -- Supports the membership lookup in is_workspace_member(). create index if not exists workspace_members_user_idx on public.workspace_members (user_id, workspace_id); -- The single membership predicate for the entire schema. -- SECURITY DEFINER is mandatory: without it this would read workspace_members -- under that table's own policy, which calls this function -> infinite recursion. create or replace function public.is_workspace_member(p_workspace_id uuid) returns boolean language sql stable security definer set search_path = '' as $$ select exists ( select 1 from public.workspace_members m where m.workspace_id = p_workspace_id and m.user_id = (select auth.uid()) ); $$; comment on function public.is_workspace_member(uuid) is 'The one tenancy predicate. Every workspace-scoped RLS policy calls it, so seat/role changes are a single-function change.'; alter table public.workspaces enable row level security; alter table public.workspace_members enable row level security; create policy "Members can view their workspaces" on public.workspaces for select to authenticated using (public.is_workspace_member(id)); create policy "Owners can update their workspace" on public.workspaces for update to authenticated using ((select auth.uid()) = owner_user_id) with check ((select auth.uid()) = owner_user_id); create policy "Members can view workspace membership" on public.workspace_members for select to authenticated using (public.is_workspace_member(workspace_id)); -- No write policy on workspace_members: rows are created server-side at -- onboarding via the service-role client. ``` ### `20260725120100_brands.sql` ```sql theme={null} -- Brands: the tracked customer brand (kind='self'), its competitors, and every -- brand auto-discovered in an answer. Untracked brands are stored too, because -- Position ranks over every detected brand (report 4.3) and because a rival -- out-mentioning the customer must be detectable (report P2.6). create table if not exists public.brands ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete cascade, name text not null, normalized_name text generated always as (public.rekkal_normalize(name)) stored, kind text not null default 'competitor' check (kind in ('self', 'competitor')), -- 'tracked' counts toward the ranking and is zero-filled in the rollups -- 'suggested' auto-discovered, present in mentions, not yet in the ranking -- 'ignored' the user dismissed it tracking_state text not null default 'tracked' check (tracking_state in ('tracked', 'suggested', 'ignored')), discovery_source text not null default 'user' check (discovery_source in ('self', 'user', 'auto')), primary_domain text, normalized_primary_domain text generated always as (public.rekkal_normalize_domain(primary_domain)) stored, -- report P2.6 failure 2: an auto-discovered domain is a guess. It is never -- used for attribution or joined to Sources until it is corroborated by -- evidence (the domain actually cited in runs mentioning this brand) or -- confirmed by a human. domain_status text not null default 'unverified' check (domain_status in ('unverified', 'corroborated', 'confirmed', 'rejected')), domain_evidence_run_count integer not null default 0 check (domain_evidence_run_count >= 0), colour text, first_seen_run_date date, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), constraint brands_id_workspace_key unique (id, workspace_id) ); comment on table public.brands is 'Every brand known to a workspace: the customer (kind=self), tracked competitors, and auto-discovered brands. Untracked brands still accrue mentions so Position and rival detection are correct.'; comment on column public.brands.domain_status is 'unverified -> guessed, never used for attribution. corroborated -> the domain was cited in runs mentioning this brand. confirmed -> a human said yes. rejected -> a human said no (and the domain is freed for the correct brand).'; -- Exactly one self brand per workspace (decision #3: one workspace, one brand). create unique index if not exists brands_one_self_per_workspace on public.brands (workspace_id) where kind = 'self'; -- report P2.6 failure 1: "Efrei" and "Efrei Paris" cannot both exist against -- efrei.fr. The second candidate becomes an alias of the first. create unique index if not exists brands_unique_domain_per_workspace on public.brands (workspace_id, normalized_primary_domain) where normalized_primary_domain is not null and domain_status <> 'rejected'; create unique index if not exists brands_unique_name_per_workspace on public.brands (workspace_id, normalized_name); create trigger brands_set_updated_at before update on public.brands for each row execute function public.set_updated_at(); create table if not exists public.brand_aliases ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete cascade, brand_id uuid not null, alias text not null, normalized_alias text generated always as (public.rekkal_normalize(alias)) stored, source text not null default 'user' check (source in ('user', 'auto', 'seed')), created_at timestamptz not null default now(), -- Composite FK: a child physically cannot claim a workspace its parent is not in. foreign key (brand_id, workspace_id) references public.brands (id, workspace_id) on delete cascade ); comment on table public.brand_aliases is 'Alternate surface forms for a brand. The unique index below makes the mention resolver deterministic instead of first-match-wins.'; -- One normalised alias resolves to exactly one brand per workspace. create unique index if not exists brand_aliases_unique_per_workspace on public.brand_aliases (workspace_id, normalized_alias); create index if not exists brand_aliases_brand_idx on public.brand_aliases (brand_id); alter table public.brands enable row level security; alter table public.brand_aliases enable row level security; create policy "Members can view brands" on public.brands for select to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can insert brands" on public.brands for insert to authenticated with check (public.is_workspace_member(workspace_id)); create policy "Members can update brands" on public.brands for update to authenticated using (public.is_workspace_member(workspace_id)) with check (public.is_workspace_member(workspace_id)); create policy "Members can delete brands" on public.brands for delete to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can view brand aliases" on public.brand_aliases for select to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can insert brand aliases" on public.brand_aliases for insert to authenticated with check (public.is_workspace_member(workspace_id)); create policy "Members can update brand aliases" on public.brand_aliases for update to authenticated using (public.is_workspace_member(workspace_id)) with check (public.is_workspace_member(workspace_id)); create policy "Members can delete brand aliases" on public.brand_aliases for delete to authenticated using (public.is_workspace_member(workspace_id)); ``` ### `20260725120200_prompts.sql` ```sql theme={null} -- Topics, prompts, and the engine reference table. -- -- A prompt belongs to exactly one topic. This is deliberate: report 4.2 trap #2 -- ("never sum across tags") is a bug class that only exists with a many-to-many -- tag model. A single-valued FK makes the over-count structurally impossible. create table if not exists public.topics ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete cascade, name text not null, normalized_name text generated always as (public.rekkal_normalize(name)) stored, position integer not null default 0, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), constraint topics_id_workspace_key unique (id, workspace_id) ); create unique index if not exists topics_unique_name_per_workspace on public.topics (workspace_id, normalized_name); create trigger topics_set_updated_at before update on public.topics for each row execute function public.set_updated_at(); create table if not exists public.prompts ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete cascade, topic_id uuid not null, text text not null, normalized_text text generated always as (public.rekkal_normalize(text)) stored, -- The market this prompt measures. The same question in three countries is -- three prompt rows, which is also the correct pricing: three countries -- genuinely is 3x the runs (report 4.5 #1). country_code text not null, language text not null, intent_class text, branding_class text, is_active boolean not null default true, source text not null default 'user' check (source in ('user', 'auto', 'seed')), archived_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), foreign key (topic_id, workspace_id) references public.topics (id, workspace_id) on delete cascade, constraint prompts_id_workspace_key unique (id, workspace_id) ); comment on table public.prompts is 'A monitored question. Exactly one topic per prompt (report 4.2 trap #2). Country and language live here, not on the run, so the same question in two markets is two prompts and two quota lines.'; -- The same question cannot be tracked twice in one market: a duplicate would -- silently double the run meter for no extra information. create unique index if not exists prompts_unique_text_per_market on public.prompts (workspace_id, normalized_text, country_code) where archived_at is null; create index if not exists prompts_workspace_active_idx on public.prompts (workspace_id) where is_active and archived_at is null; create index if not exists prompts_topic_idx on public.prompts (topic_id); create trigger prompts_set_updated_at before update on public.prompts for each row execute function public.set_updated_at(); -- Engines: a stable surface id, decoupled from model version (report 4.1). -- Text primary key so the adapter registry keys off it and migrations can seed it. create table if not exists public.engines ( id text primary key, display_name text not null, provider text not null, status text not null default 'active' check (status in ('active', 'beta', 'disabled')), supports_citations boolean not null default true, -- Indicative list price per run, for budgeting only. Actual spend is -- runs.cost_micros, recorded per call. list_cost_micros integer not null default 0, created_at timestamptz not null default now() ); comment on table public.engines is 'Stable answer-engine surface ids, decoupled from model version. Global reference data; readable by any authenticated user, written only by migration.'; insert into public.engines (id, display_name, provider, status, supports_citations, list_cost_micros) values ('perplexity-sonar', 'Perplexity Sonar', 'perplexity', 'active', true, 6000), ('openai-web-search', 'OpenAI (web search)', 'openai', 'active', true, 12000), ('gemini-grounded', 'Gemini (Google Search)', 'google', 'active', true, 35000), -- Test-only. status='disabled' so the runner refuses to dispatch it for a -- real workspace; the fixture adapter is selected explicitly by the test suite. ('fixture', 'Fixture (recorded answers)', 'rekkal', 'disabled', true, 0) on conflict (id) do nothing; alter table public.topics enable row level security; alter table public.prompts enable row level security; alter table public.engines enable row level security; create policy "Members can view topics" on public.topics for select to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can insert topics" on public.topics for insert to authenticated with check (public.is_workspace_member(workspace_id)); create policy "Members can update topics" on public.topics for update to authenticated using (public.is_workspace_member(workspace_id)) with check (public.is_workspace_member(workspace_id)); create policy "Members can delete topics" on public.topics for delete to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can view prompts" on public.prompts for select to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can insert prompts" on public.prompts for insert to authenticated with check (public.is_workspace_member(workspace_id)); create policy "Members can update prompts" on public.prompts for update to authenticated using (public.is_workspace_member(workspace_id)) with check (public.is_workspace_member(workspace_id)); create policy "Members can delete prompts" on public.prompts for delete to authenticated using (public.is_workspace_member(workspace_id)); -- Reference data: readable by anyone signed in, writable by no one. create policy "Engines are readable by authenticated users" on public.engines for select to authenticated using (true); ``` ### `20260725120300_runs.sql` ```sql theme={null} -- THE ATOM. -- -- One row in public.runs is one prompt, sent to one engine, for one scheduled -- slot date, in one workspace. It carries the answer exactly as that engine -- returned it, the execution parameters it actually ran under, and it is the -- single unit the run meter counts. -- -- run_date is the SCHEDULED SLOT, not the wall clock. For weekly cadence it is -- the Monday of the ISO week in the workspace timezone. A run that fails on -- Monday and is retried on Tuesday reuses this row and this slot, which is -- exactly why retries cannot double-count. Wall-clock execution is -- started_at / completed_at; attempt_count records how many tries it took. create table if not exists public.runs ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete cascade, prompt_id uuid not null, engine_id text not null references public.engines (id), run_date date not null, status text not null default 'pending' check (status in ('pending', 'running', 'succeeded', 'failed', 'skipped')), -- false once release_run_slot() has refunded a failure that never reached the -- provider. The row survives for audit; it just stops counting. metered boolean not null default true, attempt_count integer not null default 0 check (attempt_count >= 0), -- Execution parameters, frozen at dispatch. Copied from the prompt so that -- editing a prompt never silently rewrites history. country_code text not null, language text not null, model_version text, answer_text text, answer_char_length integer check (answer_char_length is null or answer_char_length >= 0), -- Report 4.5 #3: raw answers stay hot for 30-90 days, then move to object -- storage. Nullable from day one so archival is a background job, not a migration. answer_archived_at timestamptz, answer_storage_key text, -- Which extraction stage actually ran. 'alias_only' means the brand set is -- knowably incomplete, so Position over this run is not trustworthy and the -- read layer must refuse to render it (report 4.3, P2.6). extraction_status text not null default 'pending' check (extraction_status in ('pending', 'alias_only', 'llm', 'failed')), extracted_brand_count integer, latency_ms integer, cost_micros bigint not null default 0 check (cost_micros >= 0), error_code text, error_message text, started_at timestamptz, completed_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), foreign key (prompt_id, workspace_id) references public.prompts (id, workspace_id) on delete cascade, constraint runs_id_workspace_key unique (id, workspace_id), constraint runs_answer_present_when_succeeded check (status <> 'succeeded' or answer_text is not null or answer_archived_at is not null) ); comment on table public.runs is 'THE ATOM: one prompt x one engine x one scheduled slot date. Unique on (prompt_id, engine_id, run_date), which is both the idempotency key and the unit the meter counts. Written only by the runner via the service-role client.'; comment on column public.runs.run_date is 'The scheduled slot this run belongs to (weekly cadence: the Monday of the ISO week in the workspace timezone), NOT the wall-clock execution date. Retries reuse the slot.'; -- The idempotency key. A re-run is an upsert, never an insert. create unique index if not exists runs_atom_key on public.runs (prompt_id, engine_id, run_date); create index if not exists runs_workspace_date_idx on public.runs (workspace_id, run_date desc); create index if not exists runs_workspace_engine_date_idx on public.runs (workspace_id, engine_id, run_date desc); create index if not exists runs_dispatch_queue_idx on public.runs (workspace_id, status) where status in ('pending', 'running'); create index if not exists runs_meter_idx on public.runs (workspace_id, run_date) where metered; create trigger runs_set_updated_at before update on public.runs for each row execute function public.set_updated_at(); -- Mentions: occurrence grain, one row per textual occurrence. -- -- char_start/char_end are UTF-16 CODE-UNIT offsets into runs.answer_text, as -- produced and consumed by TypeScript. Postgres stores and compares them but -- never derives them: substring() counts characters, JavaScript counts UTF-16 -- code units, and the two diverge on any astral-plane character. create table if not exists public.run_mentions ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete cascade, run_id uuid not null, brand_id uuid not null, char_start integer not null check (char_start >= 0), char_end integer not null, -- The containing sentence, so the product can show the exact quote. sentence_start integer not null check (sentence_start >= 0), sentence_end integer not null, -- The literal text matched, so "Efrei Paris" is visible even though it -- resolved to brand "Efrei". surface_form text not null, matched_alias_id uuid references public.brand_aliases (id) on delete set null, match_method text not null check (match_method in ('alias', 'regex', 'llm')), match_confidence numeric(3, 2) not null default 1.00 check (match_confidence >= 0 and match_confidence <= 1), -- Report 5.7: mention order conflates "recommended" with "merely listed". -- Nullable, populated only by the LLM stage. Kept now rather than added later -- because deriving it needs the raw answer, which archival will move away. is_recommended boolean, created_at timestamptz not null default now(), check (char_end > char_start), check (sentence_end > sentence_start), check (sentence_start <= char_start and sentence_end >= char_end), foreign key (run_id, workspace_id) references public.runs (id, workspace_id) on delete cascade, foreign key (brand_id, workspace_id) references public.brands (id, workspace_id) on delete cascade ); comment on table public.run_mentions is 'One row per textual occurrence of a brand in an answer. Occurrence grain yields both metrics: count(distinct run_id) = visibility numerator, count(*) = share-of-voice numerator (confirmed arithmetically in report P2.3).'; -- Re-extraction idempotency at mention grain: re-running the extractor over an -- existing answer converges instead of duplicating. create unique index if not exists run_mentions_span_key on public.run_mentions (run_id, char_start, char_end, brand_id); create index if not exists run_mentions_brand_idx on public.run_mentions (workspace_id, brand_id, run_id); create index if not exists run_mentions_run_order_idx on public.run_mentions (run_id, char_start); -- Sources: a global reference corpus. A URL and its title are not customer -- data; WHICH RUN cited it is, and that lives on run_citations. create table if not exists public.domains ( id uuid primary key default gen_random_uuid(), domain text not null, normalized_domain text generated always as (public.rekkal_normalize_domain(domain)) stored, classification text, first_seen_at timestamptz not null default now(), created_at timestamptz not null default now() ); create unique index if not exists domains_normalized_key on public.domains (normalized_domain); create table if not exists public.urls ( id uuid primary key default gen_random_uuid(), domain_id uuid not null references public.domains (id) on delete cascade, url text not null, -- URLs can exceed the btree row limit, so the uniqueness key is a hash, -- scoped by domain so a collision would also have to collide on domain. url_hash text generated always as (md5(url)) stored, title text, classification text, first_seen_at timestamptz not null default now(), created_at timestamptz not null default now() ); create unique index if not exists urls_domain_hash_key on public.urls (domain_id, url_hash); create index if not exists urls_domain_idx on public.urls (domain_id); -- Citations: which run cited which URL. Carries the tenancy boundary. create table if not exists public.run_citations ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete cascade, run_id uuid not null, url_id uuid references public.urls (id) on delete set null, -- Exactly what the provider returned, before resolution. Report 4.4: Gemini -- returns vertexaisearch redirect wrappers; storing them unresolved would make -- every domain in the database Google's. raw_uri text not null, order_index integer not null check (order_index >= 0), resolution_status text not null default 'resolved' check (resolution_status in ('resolved', 'unresolved', 'redirect_unresolved', 'invalid')), -- Where in the answer the engine attached this source, when it says. char_start integer, char_end integer, created_at timestamptz not null default now(), foreign key (run_id, workspace_id) references public.runs (id, workspace_id) on delete cascade, -- An unresolved redirect is kept for later re-resolution and is visibly -- missing from the Sources rollup, rather than silently misattributed. constraint run_citations_resolved_has_url check (resolution_status <> 'resolved' or url_id is not null) ); create unique index if not exists run_citations_order_key on public.run_citations (run_id, order_index); create index if not exists run_citations_url_idx on public.run_citations (url_id); create index if not exists run_citations_workspace_idx on public.run_citations (workspace_id, run_id); alter table public.runs enable row level security; alter table public.run_mentions enable row level security; alter table public.run_citations enable row level security; alter table public.domains enable row level security; alter table public.urls enable row level security; -- SELECT only. There are deliberately no INSERT/UPDATE/DELETE policies for -- authenticated users on runs, run_mentions or run_citations: Postgres denies by -- default, so a valid session token calling PostgREST directly cannot fabricate -- a run, forge a mention, or move the meter. Rows are written exclusively by the -- runner via the service-role client (lib/supabase/admin.ts), which bypasses RLS. create policy "Members can view runs" on public.runs for select to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can view run mentions" on public.run_mentions for select to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can view run citations" on public.run_citations for select to authenticated using (public.is_workspace_member(workspace_id)); create policy "Domains are readable by authenticated users" on public.domains for select to authenticated using (true); create policy "Urls are readable by authenticated users" on public.urls for select to authenticated using (true); ``` ### `20260725120400_metering.sql` ```sql theme={null} -- The run meter. One row per workspace per billing period, and it is only ever -- moved by claim_run_slot() / release_run_slot(). Nothing else writes runs_used. create table if not exists public.usage_periods ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete cascade, period_start date not null, period_end date not null, -- exclusive -- Snapshot of the plan and the limit that was actually in force. Raising a -- plan's quota next quarter must not retroactively rewrite past enforcement. plan_id text not null, run_limit integer not null check (run_limit >= 0), runs_used integer not null default 0 check (runs_used >= 0), cost_micros_used bigint not null default 0 check (cost_micros_used >= 0), created_at timestamptz not null default now(), updated_at timestamptz not null default now(), check (period_end > period_start) ); comment on table public.usage_periods is 'The run meter. runs_used is moved only by claim_run_slot()/release_run_slot(). run_limit is snapshotted from config.ts when the period opens, so config.ts stays the single source of truth for pricing while the DB records what was enforced.'; create unique index if not exists usage_periods_workspace_period_key on public.usage_periods (workspace_id, period_start); create trigger usage_periods_set_updated_at before update on public.usage_periods for each row execute function public.set_updated_at(); -- Open (or refresh) the period covering p_period_start. The limit moves only -- upward, so a mid-period upgrade grants headroom and a mid-period downgrade -- never retroactively puts a customer over quota. create or replace function public.ensure_usage_period( p_workspace_id uuid, p_period_start date, p_period_end date, p_plan_id text, p_run_limit integer ) returns uuid language plpgsql security definer set search_path = '' as $$ declare v_id uuid; begin insert into public.usage_periods (workspace_id, period_start, period_end, plan_id, run_limit) values (p_workspace_id, p_period_start, p_period_end, p_plan_id, p_run_limit) -- The conflict target is referenced by bare relation name, not schema- -- qualified: `public.usage_periods.run_limit` here is a parse error even -- though the INSERT target is schema-qualified. on conflict (workspace_id, period_start) do update set period_end = excluded.period_end, plan_id = excluded.plan_id, run_limit = greatest(usage_periods.run_limit, excluded.run_limit), updated_at = now() returning id into v_id; return v_id; end; $$; -- Atomically claim one metered slot for (prompt, engine, run_date). -- -- Returns reason: -- 'claimed' a new run row was created and the meter moved by exactly 1 -- 'exists' the slot was already claimed; the meter did NOT move -- 'quota_exhausted' runs_used >= run_limit; nothing was inserted -- 'no_open_period' no usage period covers run_date; call ensure_usage_period first -- 'unknown_prompt' the prompt does not exist in this workspace -- -- There is no window in which a run exists unmetered, or is metered twice. create or replace function public.claim_run_slot( p_workspace_id uuid, p_prompt_id uuid, p_engine_id text, p_run_date date ) returns table (run_id uuid, created boolean, reason text) language plpgsql security definer set search_path = '' as $$ declare v_period_id uuid; v_runs_used integer; v_run_limit integer; v_run_id uuid; v_country text; v_language text; begin -- 1. Already claimed? Idempotent no-op. This is the whole re-run guarantee. select r.id into v_run_id from public.runs r where r.prompt_id = p_prompt_id and r.engine_id = p_engine_id and r.run_date = p_run_date; if found then return query select v_run_id, false, 'exists'::text; return; end if; -- 2. Serialise concurrent dispatchers on the workspace's period row. select p.id, p.runs_used, p.run_limit into v_period_id, v_runs_used, v_run_limit from public.usage_periods p where p.workspace_id = p_workspace_id and p_run_date >= p.period_start and p_run_date < p.period_end for update; if not found then return query select null::uuid, false, 'no_open_period'::text; return; end if; -- 3. Quota. if v_runs_used >= v_run_limit then return query select null::uuid, false, 'quota_exhausted'::text; return; end if; -- Freeze the execution parameters from the prompt. select pr.country_code, pr.language into v_country, v_language from public.prompts pr where pr.id = p_prompt_id and pr.workspace_id = p_workspace_id; if not found then return query select null::uuid, false, 'unknown_prompt'::text; return; end if; -- 4. Insert, and move the meter only if the insert actually created a row. insert into public.runs (workspace_id, prompt_id, engine_id, run_date, status, country_code, language) values (p_workspace_id, p_prompt_id, p_engine_id, p_run_date, 'pending', v_country, v_language) on conflict (prompt_id, engine_id, run_date) do nothing returning id into v_run_id; if v_run_id is null then -- Lost a race; the winning transaction already metered it. select r.id into v_run_id from public.runs r where r.prompt_id = p_prompt_id and r.engine_id = p_engine_id and r.run_date = p_run_date; return query select v_run_id, false, 'exists'::text; return; end if; update public.usage_periods set runs_used = runs_used + 1, updated_at = now() where id = v_period_id; return query select v_run_id, true, 'claimed'::text; end; $$; -- Refund a slot whose engine call never reached the provider (connection -- refused, 429 with no completion, our own crash). Idempotent: the `where -- metered` guard means a second call is a no-op. A provider 500 AFTER -- generation still counts, because it still cost money. create or replace function public.release_run_slot(p_run_id uuid) returns boolean language plpgsql security definer set search_path = '' as $$ declare v_workspace_id uuid; v_run_date date; begin update public.runs set metered = false, updated_at = now() where id = p_run_id and metered returning workspace_id, run_date into v_workspace_id, v_run_date; if not found then return false; end if; update public.usage_periods set runs_used = greatest(runs_used - 1, 0), updated_at = now() where workspace_id = v_workspace_id and v_run_date >= period_start and v_run_date < period_end; return true; end; $$; alter table public.usage_periods enable row level security; -- Read-only for members. No write policy: the meter is unforgeable from a -- browser because there is no policy to exploit. create policy "Members can view their usage periods" on public.usage_periods for select to authenticated using (public.is_workspace_member(workspace_id)); ``` ### `20260725120500_rollups.sql` ```sql theme={null} -- Precomputed rollups. Sums and counts only -- no column in this schema holds a -- ratio (report 4.1). Every ratio is recomputable from its components at any -- aggregation level, and the n comes along for free at each one. -- -- The naming convention is the enforcement: every metric column ends in _count -- or _sum. create table if not exists public.brand_daily_metrics ( workspace_id uuid not null references public.workspaces (id) on delete cascade, brand_id uuid not null, engine_id text not null references public.engines (id), bucket_date date not null, -- THE n. Denominator for every brand metric, at every grain. -- The > 0 check is what makes "no data" row absence rather than a zero-n row, -- so an empty comparison window is NULL by construction (report P2.5). run_count integer not null check (run_count > 0), mention_run_count integer not null default 0 check (mention_run_count >= 0), mention_count integer not null default 0 check (mention_count >= 0), position_sum integer not null default 0 check (position_sum >= 0), position_count integer not null default 0 check (position_count >= 0), updated_at timestamptz not null default now(), primary key (workspace_id, brand_id, engine_id, bucket_date), foreign key (brand_id, workspace_id) references public.brands (id, workspace_id) on delete cascade, check (mention_run_count <= run_count) ); comment on table public.brand_daily_metrics is 'Sums and counts, never ratios. run_count is the sample size for every brand metric. A row exists only where runs actually happened (check run_count > 0), so an absent comparison window is NULL rather than a delta against zero.'; create index if not exists brand_daily_metrics_window_idx on public.brand_daily_metrics (workspace_id, bucket_date desc, brand_id); create table if not exists public.domain_daily_metrics ( workspace_id uuid not null references public.workspaces (id) on delete cascade, domain_id uuid not null references public.domains (id) on delete cascade, engine_id text not null references public.engines (id), bucket_date date not null, run_count integer not null check (run_count > 0), -- Distinct runs that retrieved this domain. This is the citation-rate -- denominator (report P2.3); dividing by retrieval_count is the deprecated -- metric, so both are stored and the correct one is always available. retrieved_run_count integer not null default 0 check (retrieved_run_count >= 0), retrieval_count integer not null default 0 check (retrieval_count >= 0), updated_at timestamptz not null default now(), primary key (workspace_id, domain_id, engine_id, bucket_date), check (retrieved_run_count <= run_count) ); create index if not exists domain_daily_metrics_window_idx on public.domain_daily_metrics (workspace_id, bucket_date desc, domain_id); -- Recompute one workspace-day of brand metrics. Idempotent (delete + insert). -- -- Position ranks over EVERY detected brand, tracked or not (report 4.3), which -- is why untracked brands still accrue mention rows. -- -- Tracked brands are zero-filled for every (engine, date) that had runs, so -- "measured zero with n=12" has a row and is distinguishable from "not -- measured". Untracked brands get rows only where they were mentioned. create or replace function public.refresh_brand_daily_metrics( p_workspace_id uuid, p_bucket_date date ) returns integer language plpgsql security definer set search_path = '' as $$ declare v_rows integer; begin delete from public.brand_daily_metrics where workspace_id = p_workspace_id and bucket_date = p_bucket_date; with scoped as ( select r.id as run_id, r.engine_id from public.runs r where r.workspace_id = p_workspace_id and r.run_date = p_bucket_date and r.status = 'succeeded' ), totals as ( select s.engine_id, count(*)::integer as run_count from scoped s group by s.engine_id ), per_run as ( select m.run_id, m.brand_id, s.engine_id, count(*)::integer as mention_count, min(m.char_start) as first_char_start from public.run_mentions m join scoped s on s.run_id = m.run_id where m.workspace_id = p_workspace_id group by m.run_id, m.brand_id, s.engine_id ), positioned as ( select p.*, rank() over (partition by p.run_id order by p.first_char_start)::integer as position from per_run p ), agg as ( select p.brand_id, p.engine_id, count(distinct p.run_id)::integer as mention_run_count, sum(p.mention_count)::integer as mention_count, sum(p.position)::integer as position_sum, count(*)::integer as position_count from positioned p group by p.brand_id, p.engine_id ) insert into public.brand_daily_metrics (workspace_id, brand_id, engine_id, bucket_date, run_count, mention_run_count, mention_count, position_sum, position_count) select p_workspace_id, b.id, t.engine_id, p_bucket_date, t.run_count, coalesce(a.mention_run_count, 0), coalesce(a.mention_count, 0), coalesce(a.position_sum, 0), coalesce(a.position_count, 0) from totals t cross join public.brands b left join agg a on a.brand_id = b.id and a.engine_id = t.engine_id where b.workspace_id = p_workspace_id and (b.tracking_state = 'tracked' or a.brand_id is not null); get diagnostics v_rows = row_count; return v_rows; end; $$; -- Recompute one workspace-day of source metrics. Only resolved citations count: -- an unresolved Gemini redirect wrapper is visibly missing rather than -- misattributed to google.com. create or replace function public.refresh_domain_daily_metrics( p_workspace_id uuid, p_bucket_date date ) returns integer language plpgsql security definer set search_path = '' as $$ declare v_rows integer; begin delete from public.domain_daily_metrics where workspace_id = p_workspace_id and bucket_date = p_bucket_date; with scoped as ( select r.id as run_id, r.engine_id from public.runs r where r.workspace_id = p_workspace_id and r.run_date = p_bucket_date and r.status = 'succeeded' ), totals as ( select s.engine_id, count(*)::integer as run_count from scoped s group by s.engine_id ), cited as ( select u.domain_id, s.engine_id, c.run_id from public.run_citations c join scoped s on s.run_id = c.run_id join public.urls u on u.id = c.url_id where c.workspace_id = p_workspace_id and c.resolution_status = 'resolved' ) insert into public.domain_daily_metrics (workspace_id, domain_id, engine_id, bucket_date, run_count, retrieved_run_count, retrieval_count) select p_workspace_id, c.domain_id, c.engine_id, p_bucket_date, t.run_count, count(distinct c.run_id)::integer, count(*)::integer from cited c join totals t on t.engine_id = c.engine_id group by c.domain_id, c.engine_id, t.run_count; get diagnostics v_rows = row_count; return v_rows; end; $$; alter table public.brand_daily_metrics enable row level security; alter table public.domain_daily_metrics enable row level security; create policy "Members can view brand metrics" on public.brand_daily_metrics for select to authenticated using (public.is_workspace_member(workspace_id)); create policy "Members can view domain metrics" on public.domain_daily_metrics for select to authenticated using (public.is_workspace_member(workspace_id)); ``` *** ## 11. TypeScript surface ### The engine adapter contract — one interface, four implementations ```ts theme={null} /** A stable answer-engine surface id. Matches public.engines.id. */ export type EngineId = | "perplexity-sonar" | "openai-web-search" | "gemini-grounded" | "fixture"; /** A source an engine cited, normalised across providers. */ export interface NormalizedSource { /** Fully-resolved absolute URL. Gemini vertexaisearch wrappers resolved. */ readonly url: string | null; /** Exactly what the provider returned, before resolution. Always present. */ readonly rawUri: string; readonly title: string | null; /** 0-based order the engine listed it in. */ readonly orderIndex: number; readonly resolutionStatus: | "resolved" | "unresolved" | "redirect_unresolved" | "invalid"; /** UTF-16 offsets into answerText, when the engine attributes the source. */ readonly charStart: number | null; readonly charEnd: number | null; } export interface EngineResult { /** The answer, verbatim. Mention offsets are UTF-16 indexes into this string. */ readonly answerText: string; readonly sources: readonly NormalizedSource[]; readonly modelVersion: string; readonly costMicros: number | null; readonly latencyMs: number; } /** * One contract, three real implementations plus a fixture. A fourth engine is * a new file and one `engines` row -- no change to the runner. */ export interface EngineAdapter { readonly id: EngineId; /** False for the fixture adapter, so the suite runs with no provider keys. */ readonly requiresApiKey: boolean; isConfigured(): boolean; run(request: EngineRequest): Promise; } ``` **Testing with no provider keys.** There are no provider API keys on this machine, so the three real adapters are unit-tested against **recorded fixtures**: each adapter's response-parsing is a pure function over a captured provider payload (`lib/engines/__fixtures__//*.json`), tested directly — including the Gemini redirect-wrapper case and a provider-error case. The `fixture` adapter implements the same interface over the same recordings, so the runner, extractor, meter and rollups are exercised end-to-end with zero spend. `pnpm test` passes with an empty `.env`. ### The runner ``` resolveDuePrompts(workspaceId, slotDate) active prompts x plan engines -> ensureUsagePeriod(workspaceId, slotDate) snapshot plan + limit from config.ts -> for each (prompt, engine): claimRunSlot(...) 'exists' -> skip, meter untouched 'quota_exhausted' -> stop, report adapter.run(...) on pre-billing failure: releaseRunSlot() persistAnswer(...) status, answer_text, cost, latency extractMentions(...) stage 1 always; stage 2 if configured persistCitations(...) resolve redirects, upsert domains/urls -> refreshBrandDailyMetrics(workspaceId, slotDate) -> refreshDomainDailyMetrics(workspaceId, slotDate) ``` Slot resolution (`slotDateFor(cadence, now, timezone)`) is TypeScript and unit-tested, including the DST boundary and the five-Monday month. *** ## 12. Open trade-offs *These are the trade-offs as originally proposed. All of them have since been decided — the decision table at the top of this document records what was returned and what changed as a result.* Each has a recommendation. **T1**, **T2** and **T6** are the ones I would most want the captain to look at. ### T1 — One tenancy level (`workspace`) or two (`organization → project`)? * **A (recommended): one level.** `workspace_id` on every fact table; `organizations` added above later, touching only the `workspaces` table. Simpler RLS (one hop), fewer joins, and the expensive column is already in place. * **B: two levels now**, matching report §4.1. Agency billing needs no migration at all — but every fact table carries a second FK for a level v1 cannot use, and `is_workspace_member()` becomes a two-hop check on the hot path. **Why A:** the irreversible decision is the column on the fact tables, and A already makes it. B pays a real cost today to avoid a cheap `ALTER TABLE workspaces` later. ### T2 — Are `domains` / `urls` global or per-workspace? * **A (recommended): global reference corpus**, readable by any authenticated user; `run_citations` carries the boundary. Report P2.9 shows the incumbent's source corpus is global and identifies it as *"a large, compounding cost advantage that grows with customer count"*. rekkal starts with none of it; making it global on day one is how it starts accruing. * **B: per-workspace `urls`.** Zero cross-tenant inference possible, but the same URL is stored and re-crawled per customer and the corpus advantage never exists. **Why A, and the honest caveat:** the theoretical leak is that a user who could enumerate `urls` might infer *someone* tracks a niche URL. No per-workspace metadata lives on the global rows, the app exposes no enumeration endpoint, and the inference reveals nothing about *whom*. If the captain judges even that unacceptable, B is a one-line change now (add `workspace_id`) and a painful one later. ### T3 — Materialise per-run brand ranks? * **A (recommended): derive.** Position is `rank() over (partition by run_id order by min(char_start))`, computed once per day in `refresh_brand_daily_metrics`. * **B: a `run_brand_ranks` table.** Faster per-run reads, but a second copy of a derived fact that must be kept in sync with re-extraction and alias backfills. **Why A:** at v1 scale (\~1,500 runs/workspace/month) the window function is cheap, and B adds a consistency liability with no read the product currently needs. ### T4 — Clopper–Pearson or Wilson intervals? * **A (recommended): Clopper–Pearson (exact).** Reproduces the report's published figures exactly (1/9 → 0.28%–48.2%), never gives a zero-width interval at 0/9 — the precise case P2.4 catches the incumbent rendering as a confident "0%". * **B: Wilson score.** Closed form, computable in SQL, narrower (1/9 → 2.0%–43.5%). **Why A:** Clopper–Pearson is conservative, and for a product whose position is statistical honesty, conservative is the correct direction of error. It costs \~60 lines of unit-tested TypeScript for the incomplete beta function. ### T5 — A `workspace_engines` table? * **A (recommended): no table.** Enabled engines are a function of the plan in `config.ts`. Which engine a historical run used is on `runs.engine_id`. * **B: a table**, allowing a Pro customer to disable Gemini (the expensive one) to stretch quota. **Why A:** B is a real product idea but nothing in the decisions doc asks for it, and it is one purely additive table whenever it is wanted. ### T6 — Competitor-brand limits are not settled *(captain input wanted)* Decision #5 sets Free = 1 brand / 10 prompts / 1 engine and Pro = \~100 prompts / 3 engines. "1 brand" is the customer's **own** brand; how many **competitors** each tier may track is unstated, and the product is inert with zero competitors. My recommendation, to be overridden freely: **Free = 3 tracked competitors, Pro = 15.** Competitors cost nothing per run (mentions are extracted from answers that were already paid for), so the limit is a packaging choice, not a COGS one. Auto-*discovered* brands should not count against the limit — they are `tracking_state = 'suggested'` and are the mechanism that fixes P2.6. ### T7 — Per-run LLM extraction is a COGS line the decisions doc does not price Report §4.3 makes open-ended NER mandatory for Position, and §4.5 #2 flags it as the second-worst-scaling cost: it is per *run*, not per prompt. Decision #5 prices Pro at \~1,300 runs/month ≈ €20–30 COGS, which covers the *answer* engines only. A Haiku-class extraction pass at \~1.5k input tokens/run adds roughly **2M tokens ≈ €2/month/customer** — small, but not zero, and it is a fourth provider dependency. Not a blocker; flagged so the margin number is complete. The schema already handles the no-key case: `extraction_status = 'alias_only'` records that Position is not yet trustworthy rather than rendering it anyway. ### T8 — `config.ts` marketing copy vs. enforced limits Decision #5 explicitly replaces the current `config.ts` quotas. This task needs the **machine-readable** `limits` block (§8.5) to enforce anything. The `plans.*.features` strings are *marketing copy* ("500 monitored prompts", "Daily refresh", "including Claude & Copilot") and are now wrong on every line. **Recommendation:** add `limits` in this task (the code needs it); leave `features` prose to the pricing/marketing task, since `config.features.preLaunch` is `true` and the copy is not publicly indexed. Flagged rather than silently changed, because the two blocks disagreeing in `main` is exactly the kind of thing that ships wrong. *** ## 13. Amendments made during implementation Changes the original proposal did not contain. A and B both follow from the T6 decision that Pro tracks an **unlimited** number of competitors — a limit this document had assumed would be a small number. C came later, from the throughput the schedule actually needs. ### A. `workspace_daily_runs`, a fifteenth table The proposed rollup zero-filled every `tracked` brand for every (engine, date) that had runs, so that "0 mentions out of n=12" had a row and was distinguishable from "not measured". With an unlimited competitor set that multiplies out: 5,000 competitors × 3 engines × 365 days is millions of empty rows per workspace per year. `workspace_daily_runs(workspace_id, engine_id, bucket_date, run_count, attempted_run_count, failed_run_count)` holds the sample size **once per slice**, because `run_count` is a property of the slice and not of a brand. The zero-fill is then bounded to brands that have ever been mentioned in the workspace (`brands.first_seen_run_date is not null`) plus the self brand, and a brand with no row is still reportable as "0 out of n" against this table. Two things fall out of it that were not the motivation but are worth having: all three rollups now derive `run_count` from one place, so every surface agrees on `n` by construction; and `attempted_run_count` / `failed_run_count` make a slice whose `n` was *depressed by engine failures* visible, which is a sample-size fact the product should be able to state. ### B. The defensive brand ceiling: 5,000 per workspace `config.limits.maxTrackedBrandsPerWorkspace = 5000`, applied to every brand row regardless of plan or `tracking_state`. This is **not a pricing limit** and is deliberately far above anything a real customer would reach — the largest tracked set in the competitive teardown was 8 brands with 11 suggestions. It exists because auto-discovery writes brand rows from model output, so one malformed extraction emitting thousands of spurious names would otherwise grow the table without bound. Hitting it is an incident to investigate, not an upsell. The related instruction — that extraction degrade sanely with a large competitor set — is handled in `lib/runner/extract.ts`: matching tokenises the answer once and looks up n-grams in a hash map, which is `O(answer tokens × longest alias)` and **independent of alias count**. A test asserts a workspace with 20,000 aliases extracts in the same time as one with 8. A per-alias loop, which is the obvious implementation, would have been 20,000× the work. ### C. The runner's trigger: hourly, one endpoint, per-engine concurrency, workspace rotation (2026-07-25) §9 above describes a single Route Handler at `app/api/cron/run-prompts/route.ts` and left the schedule implicit. Both were settled during implementation, and the shape that shipped is worth recording because it is not what a first reading of this document would assume. The proposal's sequential dispatch on a weekly cron does not deliver the Pro plan: a 240s window at one call at a time clears \~24 grounded calls against the 300 a full Pro workspace needs per slot, so it falls further behind every week. What shipped instead is **bounded concurrency of 4 in flight per engine** (per engine, not one shared pool, because the providers have separate rate limits) on an **hourly** cron. Hourly is the trigger, not the measurement: `run_date` still buckets by ISO week, so a prompt × engine is still measured exactly once per week, and the extra fires drain the slot progressively. Two intermediate shapes were implemented and then removed, and both are worth recording so they are not re-proposed. A **two-endpoint split** — a frequent `/api/cron/maintain` for the zero-spend phases — was dropped because the one dispatch pass already runs reclaim, extraction recovery and orphan close-out, so the second endpoint bought only recovery latency and cost a schedule that could drift. **Dividing the dispatch window evenly across workspaces** was dropped because a divided window drains nobody: at ten workspaces each slice is \~24s. The window now goes to one workspace at a time in full, and one new column, `workspaces.last_dispatch_started_at` (`20260725121100_dispatch_rotation.sql`), rotates which workspace leads each fire — data-derived rather than a stored cursor, so adding or removing a workspace cannot skip one. Rotation and interval fix different things, which is the part most likely to be lost: rotation is *allocation*, the hourly interval is *capacity*. Total dispatch is window × fires, so rotating full slices on a daily cron gives ten workspaces the same \~101 calls a week that the even split did, and equally fails to clear a Pro slot. The live documentation for all of this is [`analytics-pipeline.md`](./analytics-pipeline.md) and [`deployment.md`](./deployment.md), which carry the interval, the concurrency, the rotation, and the arithmetic tying them to the quota — including the ceiling this design has and the fan-out that lifts it. *** ## 14. What this proposal deliberately does not include Out of scope per the brief, and not designed for here: dashboard UI, the free no-signup audit, recommendations/Actions, the MCP server, sentiment, source page-content crawling, agent/crawler analytics, and any change to `config.features.preLaunch` (which stays `true`). The rollup tables **are** included, against a narrow reading of the brief, because "store sums and counts, never ratios" is the decision that is expensive to reverse and it belongs in the same review as the atom. The captain confirmed this. This file is deliberately **not** listed in `llms.txt` and not folded into `llms-full.txt`. Those index the durable product docs; this is a design record. [`docs/supabase.md`](./supabase.md) carries the shipped schema and is the file the index points at. *** ## 15. Definition of done for Phase B, and how it was verified Every item verified by querying the hosted database, never by trusting a command's exit code — a green deploy does not prove the hosted DB has the migration. The test counts below are the **Phase B snapshot**, not a running total: the review rounds that followed added tests and migrations without invalidating any row here. Run `pnpm test` for the current count, and see [supabase.md](./supabase.md) for which migrations are applied to the hosted project today. | Requirement | Evidence | | -------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Migrations applied to `dthdxgrrktaavshxjqjr` | Dry-run inside a rolled-back transaction first (with a deliberate bad-SQL canary to prove errors surface), then applied. **18 public tables, RLS enabled on every one.** | | RLS posture correct | Queried `pg_policy`: `runs`, `run_mentions`, `run_citations`, `usage_periods` and all three rollups expose **SELECT only**; `brands`, `prompts`, `topics` expose full CRUD. | | Functions present and correct | All 10 present with expected volatility/security. `rekkal_normalize(' Télécom PARIS ')` returns `telecom paris` **on the live engine** — the riskiest assumption in the proposal (declaring an `unaccent` wrapper `IMMUTABLE`) confirmed rather than assumed. | | Entity resolution enforced | Live: inserting "Efrei Paris" against `efrei.fr` raises `unique_violation`; one normalised alias cannot resolve to two brands. | | Idempotency | Live: a second `claim_run_slot` for the same slot returns `exists`, the same `run_id`, and `runs_used` unchanged. Also asserted end-to-end against the real database in `store-supabase.integration.test.ts`. | | Quota enforcement | Live: at the limit the next slot returns `quota_exhausted` and inserts nothing. A downgrade cannot strand a workspace over quota (`run_limit` only rises). | | Meter invariant | `usage_periods.runs_used` equals `count(runs where metered)` for every period — **0 drifted rows**, checked after every exercise above. | | Three real adapters + fixture behind one interface | 28 tests. Offsets are asserted by slicing the answer back to the expected substring, so a mis-shifted citation cannot pass. | | Runner idempotent, metered, quota-enforced | 51 tests against an in-memory store, plus 2 against the hosted database. | | Suite green with **no provider API key** | `141 passed, 2 skipped` with the provider keys explicitly unset; `eslint` clean; `next build` clean, 13 routes. The 2 skips are the hosted-database integration test, which is opt-in via `RUN_SUPABASE_INTEGRATION=1`. | One defect was found this way that no unit test could have caught: **PostgREST unifies the column set across a batch insert**, so a column present in one row but omitted from another is sent as an explicit `NULL` and the column default never applies. Production code is unaffected because it builds uniform rows, but it is a live-database behaviour worth knowing before the next batch insert is written.