Release v0.1.140 · Juli1artha/discovery-media-player

Deux routes qui pouvaient coûter — l'une la disponibilité du serveur, l'autre de l'argent — une
migration qui échouait sur la donnée qu'elle venait réparer, et un assistant qui promettait une voix
que ce paquet ne câble pas.

⚠️ CE TRAIN SORT SOUS L'EXCEPTION « CORRECTIF DE SÉCURITÉ », ET DEUX FOIS PLUTÔT QU'UNE.
docs/RELEASING.md autorise un train par jour ; celui-ci part le lendemain de la 0.1.139, donc la
cadence n'est même pas en cause. Ce qui l'est : bin/serve.js s'arrêtait sur un en-tête Host
malformé — une requête anonyme, sans configuration ni compte — et bot-tts acceptait n'importe
quel texte d'un porteur de lien public, à la facture de l'hôte. Les deux partent dès qu'ils sont
verts, ce que cette page appelle par son nom.

⚠️ ET UNE MIGRATION DÉJÀ PUBLIÉE EST CORRIGÉE SUR PLACE, ce que ce dépôt interdit par écrit.
La 0020 (sortie en 0.1.135) s'arrêtait à mi-chemin sur toute base portant deux colonnes hors plage
dans une même ligne, laissant les contraintes posées et jamais validées. Un fichier 0024 aurait été
plus orthodoxe et inopérant : une base ancienne rejouant la chaîne s'arrête à la 0020 et ne
l'atteint jamais. Pour un hôte où elle est passée, le fichier corrigé est un no-op strict, vérifié
contre un vrai PostgreSQL 16. zones-du-tarball lèvera son alarme « a migration already applied
elsewhere must be immutable » sur cette livraison : c'est le traitement que ce changement mérite,
et il est attendu.

⚠️ LA FRONTIÈRE HÔTE A BOUGÉ TROIS FOIS, toutes additives. wiresVoice conditionne l'affichage
des contrôles de voix ; bot-tts exige un sessionId ; doc_tts_objects devient un point
d'écriture pour l'hôte. Un hôte qui ne touche à rien n'est affecté par aucune : la voix n'était
câblée nulle part, bot-tts n'était appelée par personne, et ne rien écrire dans tts-cache reste
sans conséquence. docs/HOST-CONTRACT.md porte les trois.

Signalé par l'audit CODEX du 26/08, puis par deux hôtes intégrateurs dont l'un a trouvé ce que ni la
forge ni l'audit n'avaient vu.

Security

The voice route could be used as a public, paid API — and the bill was ours. bot-tts accepts
body.text as given: a valid public slug is enough, no session is required, and nothing ties the
text to an answer the bot actually produced. The per-IP rate limit (400/h) bounds a single
address's cadence; it bounds neither the cost per call, nor the number of concurrent outbound
calls, nor the size of what we accept back. Three bounds now exist, none of which changes the
protocol. Reported by the CODEX audit of 26 August.
- ⚠️ A hundred concurrent requests for the same text produced a hundred syntheses. The cache
  check is a HEAD, and a HEAD cannot see what has not been written yet — so a burst missed the
  cache together and paid for the same clip a hundred times. Requests are now grouped by
  fingerprint: one synthesis, one stored object, one bill, all hundred served.
- ⚠️ And the same primitive supplies the ceiling on concurrent paid calls. creerCache's
  admission limit refuses the request past the ceiling with a retryable 503 instead of admitting
  it. 503 tells the caller to wait a second; 500 would tell it to give up.
- ⚠️ The three outbound fetch calls had no deadline at all. A provider that answers slowly —
  or stops answering — held the request, its socket and its admission slot until the platform
  killed the function. appelHote and the file relay already carry this lesson; this was the
  route it had not reached. A real abort (AbortSignal), not a promise race: a race returns
  without cancelling, so it frees nothing.
- ⚠️ The response body is bounded before allocation. gen.json() read whatever arrived,
  base64 audio and alignment array included; the body is a third party's, so its size was not
  ours to assume. It is now read against a ceiling and refused at the first byte past it, without
  reaching storage.
- What is memoised is what is shareable, and nothing more: spoken is composed per caller.
  Two different texts can share one pronunciation — that is what pronFix is for — hence one
  fingerprint, while spoken !== text holds for only one of them. Memoising the whole reply would
  have handed the second caller the first one's spelling, and the karaoke would have aligned on
  the wrong string.
- ⚠️ AND THE BINDING ITSELF IS NOW CLOSED — this bullet said "still open" for a few hours. A
  call must carry a sessionId, that session must belong to the requested slug, and the text
  must match something the assistant said in that session. Refused with session (absent, or
  opened on another document) or texte (never said). No signed ticket was needed: the truth
  comes from the database that produced the message, not from a token the client holds — so there
  is no new secret to rotate, and a secret you never rotate is the one you forget to rotate.
- The comparison is on the spoken form, not the written one, and that is stronger. pronFix
  can map two spellings onto the same pronunciation, and the pronunciation is what makes the cache
  fingerprint. So an accepted text is either a real message, or one whose clip is already paid
  for. Comparing spellings would refuse legitimate cases and admit billable ones. The idea came
  from an integrating host and was better than ours.
- It refuses by default. A message's shape comes from the host's plugin, which no contract
  described. An unrecognised role is not treated as the assistant's, so an unreadable set yields
  an empty one and everything is refused. On a route that spends money, "I could not verify"
  must read as no. A read that fails answers 503 indisponible and is recorded — an
  operator does not look in the same place for a broken read and a rejected text.
- ⚠️ The order of the guards is itself a property. Placed before the rate limit, the binding
  offered a database read per request to a caller with no session at all — unbounded work
  triggered under the limiter, which is what the limiter exists to prevent. Found by the existing
  ceiling bench, which required 429 "before any call" and got 500. It was already guarding
  the property; we were not seeing it.
A host's messages carry their text in body, and the reader would have refused all of them.
bot-tts read the text from text or content. An integrating host reported — before hitting
it — that its messages use body and nothing else, with a correct role: "bot". The reader
would have returned an empty string for every message, yielding an empty set, so every request
refused with 400 texte: refuse-by-default doing exactly what it must, against a perfectly correct
integration. The list is now text, content, body, first non-empty wins — a present but
empty field no longer masks the next one.
- The host offered to project body into text in its own plugin instead. We widened the reader:
  listMessages is the host's, and asking every host to rename columns for an undocumented
  preference moves the transformation into all of them, forever, where forgetting it means a total
  silent refusal. The field name carries no security — the role filter does, and it is unchanged.
doc_tts_objects is documented as a host write point, not an internal table. The sweep removes
an object only when its fingerprint has a row, and only the player's route wrote one — so anything a
host puts in the tts-cache bucket itself was invisible to retention permanently, not just for
what was already there. The same host measured 908 objects it had written, under the player's
exact naming (same digest, same two files, same root) — a parity its own code says was deliberate,
so that one clip serves both surfaces. Nothing but the missing row distinguished them.
docs/HOST-CONTRACT.md now carries the fingerprint recipe and the idempotent insert. No schema
change, no grant: RLS is on with no policy, and the service_role key the db capability already
uses bypasses it.
The sweep's report says why fichiersErreur can be high without anything having failed. The
code claimed the missing alignment .json came from pre-v2 extracts. True, and not the main
cause: the provider does not always return an alignment. Measured on that host's bucket, 552
.mp3 for 356 .json — 196 audio files with no companion to remove, a live case rather than a
relic. The count stays unmasked; an operator finding two hundred "errors" on a first sweep would
otherwise hunt a failure that does not exist.
Setting ELEVENLABS_API_KEY made voice controls appear that nothing in this package wires.
The key proves the server can synthesise; it says nothing about what happens on click — and this
package wires none of the sixty-four controls in the assistant, which is markup it ships and
behaviour the host ships. Three voice buttons and the audio-consent step were the only ones whose
appearance was driven by a server secret, so the key read as "the feature is on" and a visitor
who clicked got silence. Your bot plugin must now declare wiresVoice: true; absent — or merely
truthy rather than exactly true — the four controls are not rendered. Reported on 26 August by
an integrating host who went looking for the caller of bot-tts and found none.
- ⚠️ This file's own bench already carried the rule and looked at the wrong side. It says, word
  for word, "a door that leads to silence is a broken promise" — and checked it without the
  key, the one case where the door could not exist.
- ⚠️ And docs/CONFIGURATION.md claimed the opposite, twice: "the browser asks this
  instance". A host reading it set a paid API key and concluded voice worked. It has never
  been wired, in the whole history of the repository — the route and the buttons both shipped from
  the first commit, the handler never did. bot-tts is documented as what it is: an integration
  point, not a feature.

Fixed

A malformed Host header could stop the standalone server. One anonymous request, no
configuration, no account. bin/serve.js built the request URL as
new URL(req.url, "http://" + req.headers.host). Host: [ yields the base http://[, which
new URL rejects — and the throw happened in an async listener whose promise http never
awaits, so it became an unhandled rejection and Node exited the process with code 1. Reproduced
on v0.1.139 before the fix: the connection is cut, the port closes, every later request is
refused. Reported by the CODEX audit of 26 August; the defect predates 0.1.139.
- ⚠️ Nothing here ever read the host. Only pathname and searchParams are used, so the
  header contributed its failure and nothing else. The URL is now parsed against a fixed internal
  base, and a malformed request-target answers 400 instead of raising.
- ⚠️ The correct form was already written one file away, and this was the diverging copy.
  server/handler.js parses against a fixed base, and the re-share email link left
  req.headers.host for PLAYER_PUBLIC_URL some versions ago. Twice the same lesson: a public
  origin is declared, never guessed from a header.
- ⚠️ The catch now wraps the whole request, not just player.handler. The old try covered
  the handler only; URL parsing, the folder-mode home page and the JSON body read sat outside it.
  Whatever is added above the handler next is covered by construction.
- Benched in a real child process, because the defect does not exist anywhere else: called
  directly the function merely rejects and the caller learns of it — what kills is that nobody
  awaits the promise. Restoring the old line turns the bench red.

Added

The bounds of an internal reading session, in one place — and a guard for the class, not the
case. upsertInternalSession redefined num, borne and the whole pages_time loop at the
top of its body, while bornerNombre and bornerPagesTime sat forty lines above doing
character-for-character the same thing. The behaviour was identical — which is exactly what makes
such a duplicate dangerous: nothing flagged it, and nothing would have flagged the day one of the
two copies moved. Reported by the CODEX audit of 26 August, P3.
- Migration 0023 gives commercial_doc_internal_sessions the constraints migration 0020 gave
  the other two tables. It carries exactly the same columns, written by the same bounded code, and
  had received nothing — nobody noticed for a day, because an absence writes itself nowhere.
- ⚠️ So CI now guards the rule rather than the table. A probe requires every visitor-reported
  measurement column — in any table — to carry a validated constraint. A fourth table added
  tomorrow with a max_page goes red until it is bounded, and nobody has to remember. It checks
  convalidated, not mere existence: a constraint left not valid protects new writes and lets
  history through — a legitimate state during a migration, never an arrival state.
- What the probe does not watch is written down rather than implied: current_page, written
  by the presenter and read by no aggregation, is not bounded in the database today. A recorded
  decision, not an oversight — the day an aggregation reads it, it joins the list.
- ⚠️ And closing that duplicate opened a bigger one, so it is guarded too. 10000 and 86400
  — the values of BORNES in server/shares.js — are now copied into the SQL constraints: 18
  times in migration 0020, 9 in 0023, 16 in init.sql. Raising BORNES without the database
  makes it refuse writes the code believes valid; lowering it makes the database laxer than
  the code, and the "last line of defence" the migrations claim stops being one. Neither goes red.
  A bench now reads BORNES at its source and requires every ceiling in those three files to
  be one of its values — ceilings, not occurrences: only a check … <= comparison and the
  least(greatest(col, 0), N) repair express one.
Analytics aggregation moved into the database — and the JavaScript stays, on purpose.
listSharesForDoc and overview read commercial_doc_views over a rolling 24-month window. The
window bounds time, not the number of rows: the index serves the filter and does nothing else
— it removes neither the PostgREST → Node transfer, nor the full pagination, nor the arrays in
memory, nor the cost of aggregating in JavaScript. On a very active document, 24 months can be
millions of events for a reply of a few dozen lines. Migration 0022 adds five aggregating
functions; the reply is unchanged. Reported by the CODEX audit of 26 August, §3.
- ⚠️ Today this hurts nobody, and that is written down so it stays true. The worst measured
  document carries 662 rows. This is not an incident fix — it is changing the structure before
  the volume forces it, while both paths can still be compared line by line.
- ⚠️ The in-memory path is not dead code, it is the reference definition. A host does not
  necessarily apply the latest migration, so it is also the real fallback. A bench against a real
  PostgreSQL runs both on the same rows and requires an exactly identical result — two texts
  written separately that cannot be wrong the same way, like the retention purge and its census.
  It does not assert that the numbers are right: it requires them to be the same, which is
  stronger — an error has to be made twice, separately, in SQL and in JavaScript.
- ⚠️ The fallback is narrow, and that is all that makes it safe. Only PGRST202 ("no function
  of that name") falls back. An unreachable database, a missing grant or a timeout propagate —
  falling back on those would produce numbers computed over whatever answered, and "wrong
  statistics" reads exactly like "statistics". Four benches hold that, mutation-checked; a
  fifth holds that an empty result is not an absent function.
- The read-time clamp is reproduced in SQL, including what the JavaScript does not do —
  seconds is not capped on read. Reproducing faithfully means reproducing the silences too.
  Since migration 0020 the database refuses an out-of-range page, so the legacy row that used to
  trigger the analytics DoS can no longer be seeded: the SQL function is confronted with the
  JavaScript directly, on the values a pre-0020 base can still hold.
- signatureAbsente moves from presentations.js to erreurs-base.js — "this function does not
  exist here" belongs with "this conflict is a conflict". Three modules need it now; copying it
  would have created two definitions of one fact, which is what that file exists to prevent.
  presentations.js re-exports it: its public surface does not move.
An endurance bench: what a burst cannot show — duration. Every load bench in this repository
is instantaneous: N calls fired together, one reading, done. chargeReelle already covers
concurrent heartbeats, twenty simultaneous presentations, the file relay, a slowed database, a
dying database and cost linearity; coutParGeste counts round trips; multiProcessus holds the
advisory lock against real system parallelism. None of them can answer "and if it lasts?" — a
slowly rising memory, a cache that only saturates after minutes, an event loop that slips when
gestures are mixed. Three failures invisible in three seconds, and exactly the ones an
operator meets in production. Asked for by the CODEX audit of 26 August, §3.
- It runs short by default, on purpose. A bench that only runs by hand is a dead bench — this
  repository left a publication guard dead for nineteen hours without anyone noticing. Twenty-five
  seconds on every CI run prove the scenario stands up; the real campaign is
  PLAYER_ENDURANCE_SECONDES=1800 npm run test:endurance.
- A mixed scenario, not one gesture repeated — weighted heartbeats, state reads, chat reads,
  card reads and refusals. The mix is what puts the paths in contention for the same database.
- ⚠️ The instrument is confronted with reality. The mesures reading was born hours before
  this bench, and a counter that is wrong is worse than no counter: it grants a confidence no
  measurement carries. The bench keeps its own independent tally and requires the two to agree
  exactly.
- ⚠️ The saturation ceiling is finally measured — the one thing both integrating hosts said
  they could not produce. Two hundred distinct keys against a ceiling of 128, under a database at
  +400 ms: the margin is structural, not chronometric. The bench requires that saturation
  happened — otherwise a ceiling that became unreachable would leave it green having observed
  nothing, claiming refusals are clean without ever having seen one.
- Thresholds are drift detectors, wide on purpose, same doctrine as coutParGeste. What is
  absolute is no 5xx — a server error under nominal load is a defect, not a tolerance.
mesures on the contract card: what this instance has actually lived through. lectureSaturee
(0.1.139) answers exactly one question. Is a route slow? which ones? us or the database? how many
5xx? is the event loop slipping? had no observable answer at all — and deciding to optimise
without them is guessing. Both integrating hosts confirmed independently that they cannot produce
these numbers from their side. Per family of work: n, p50/p95/p99, maxMs; plus response
classes (ok, refus4xx, debit429, occupe503, erreur5xx), rss/heap/arrayBuffers,
event-loop delay, and the latency of the db capability. Asked for by the CODEX audit of
26 August, §2.
- ⚠️ Buckets, not samples. Keeping durations to compute a true percentile would mean a table
  that grows with traffic — a memory leak driven from outside, the trap server/cache.js already
  documents. The ladder is fixed, so this module's memory is bounded by construction. There is a
  bench: one call versus five thousand differ by the length of the digits.
- ⚠️ A percentile over buckets is a bound, not a value. p95sousMs: 250 reads "95% of calls
  under 250 ms", never "the 95th is 250 ms" — hence the key name, and hence seauxMs shipping
  beside the numbers: without the ladder you cannot judge how precise what you are reading is.
- ⚠️ "Not measured" is never rendered as zero. A family never exercised is absent; an
  event-loop histogram with no samples reports moyen: null. A 0 there would read as healthy.
  And the loop delay has its sampler resolution subtracted — otherwise a perfectly idle instance
  would report a permanent 20 ms and send someone hunting a fault that does not exist.
- ⚠️ The database is measured at the seam, not at 67 call sites. The capability is supplied by
  the host; remembering to time each call would mean never forgetting once, and the first lapse
  would go unnoticed. Wrapped at init, it covers calls nobody has written yet. Benched to return
  the same values and the same rejections: a decorator that changes the contract measures
  something other than production.
- ⚠️ No slug, no address, no text — counters and durations only, checked structurally by a
  bench that walks every leaf. That is what makes it publishable on a card a host reads without
  ceremony. Process-local and reset by every deployment, like lectureSaturee.
- ⚠️ The wrapper delegates to the live object; it does not photograph its methods. The first
  version captured db.request at wrap time, so anything replacing it after init stopped
  being called — silently. Not a hypothetical: CI went red on it. A bench installs its probe after
  init, and a host has exactly the same right (a retry wrapper, a lazily wired client,
  instrumentation). The context itself is inherited rather than copied, for the same reason.
  A measurement that changes what runs is not a measurement.
- The event-loop sampler is enabled at import, and the graceful-shutdown bench is what guards
  it: bin/serve.js loads this module, so if the histogram held the loop open, SIGTERM would
  stop exiting 0 and that bench would go red.
The voice cache was unpurgeable by construction — not for want of a policy. Every synthesis
writes two objects to the public tts-cache bucket, <fingerprint>.mp3 and
<fingerprint>.json. The fingerprint is a digest of voice + model + spoken text, and it tied back
to no row anywhere. The retention sweep erases rows, and for files it erases the ones a row
points at — the host storage capability exposes put and remove, never list. There was
literally nothing to walk: no window, no setting and no policy could reach that bucket. The CODEX
audit of 26 August costed this at "half a day of policy"; what was missing was not a policy, it
was the trace.
- Migration 0021 adds doc_tts_objects: a fingerprint and a date, and never the text —
  writing the text there would recreate, inside the database, whatever personal data the bucket
  may already hold, and make it queryable, which is strictly worse than not having it. RLS on with
  no policy: under RLS an absent policy refuses everyone, service role aside.
- The sweep now purges the voice cache 13 months after created_at, through the same single
  destruction door the whole engine uses — retirerFichier takes its bucket as an argument rather
  than gaining a twin, which is exactly what retentionUnePorte.test.js exists to prevent.
- ⚠️ The row leaves after the objects, never before. Erasing the trace first would leave both
  objects permanently unreachable — we would have purged the only means of purging them.
- ⚠️ The trace is never blocking, and never silent. A failed write must not leave a
  presentation mute, so the voice wins; but a silent rejection is indistinguishable from an empty
  cache — the lesson of the internal session dropped without a word, which cost a host weeks. Said
  once an hour, with what it costs: those objects will sit outside every window.
- Stated rather than simulated, in docs/RETENTION.md: objects written before migration 0021
  have no trace and never will. The sweep only reaches what a row points at. The census counts
  rows, so it can say "no trace past the window survives" — never "the bucket is clean".
AGENTS.md records the one thing no guard in this repository can check: who the work was for.
Eleven rules were added to tools/ between 23 and 26 August; every one of them compares a file to
a property, and none can say that correct work was addressed to nobody.
- ⚠️ The incident is the 0.1.139 headline itself. Removing dumb-init was the better call and
  remains one; the graceful shutdown that made it possible was right and benched against real
  signals. Both integrating hosts run serverless and consume no image. Nothing was wrong with
  the work — the question "who runs this?" was never asked, and a defect that was hiding and a
  question that was not asked are not found by the same means. The first yields to a probe.
- ⚠️ And the topology turns out not to be derivable — measured, not assumed. An integrating
  session tried to remove the need rather than remember it: lectureSaturee.fenetreS is
  process.uptime(), so a function process should stay young while a container ages. Five
  readings 25 s apart on their production: 224 249 274 300 325 — +25 for 25, five times. The
  same warm lambda answers every call and ages exactly like a container. The remedy is therefore
  not discipline but necessity: an integrator has to declare their topology.
- The dead idea is recorded with its numbers, on purpose: three paragraphs now against half a day
  for whoever re-derives it in three months without knowing it was tried.

What changed in the package, by zone — `0.1.139` → `0.1.140`

Measured on the two tarballs, by the release workflow. Not written by hand.

Zone	What it is	Added	Changed
`documents`	what a human reads	0	2
`manifest`	package.json — version, exports, dependencies	0	1
`server`	the code the host executes	1	8
`context`	the injected-context implementations	0	0
`browser-types`	the declarations the host's tsc reads for « ./bridge » — breaks a build, never a page	0	0
`browser`	what the visitors' page executes	0	0
`cli`	the command-line entry point	0	1
`types`	declarations for the server and context entry points — breaks a build, never runtime	0	0
`database`	the schema and the migrations the host applies itself	3	3

The files themselves

~ docs/HOST-CONTRACT.md
~ docs/RETENTION.md
~ package.json
+ server/mesures.js
~ server/cache.js
~ server/erreurs-base.js
~ server/gabarit-agent.js
~ server/handler.js
~ server/presentations.js
~ server/retention.js
~ server/routes-agent.js
~ server/shares.js
~ bin/serve.js
+ supabase/migrations/0021-empreintes-de-voix.sql
+ supabase/migrations/0022-statistiques-agregees.sql
+ supabase/migrations/0023-bornes-des-sessions-internes.sql
~ supabase/init.sql
~ supabase/migrations/0020-mesures-bornees.sql
~ supabase/recensement-retention.sql

⚠️ `database` — 3 file(s) changed, not added

a migration already applied elsewhere must be immutable. A changed file here is not a count — it is a stop. Check the objects listed below against your live database: a migration registry only records what went through one particular path, so absence from it does not mean the migration was never applied.

supabase/init.sql
objects it touches — table public.commercial_doc_shares — create, alter, index cds_doc_id_idx — create, index cds_parent_idx — create, index idx_doc_shares_brand_key — create, index idx_shares_revoked_at — create, table public.commercial_doc_views — create, alter, index cdv_slug_idx — create, index cdv_doc_idx — create, index commercial_doc_views_at_idx — create, table public.commercial_doc_sessions — create, alter, index cds_sess_slug_idx — create, index cds_sess_doc_idx — create, index idx_cds_last_at — create, table public.commercial_doc_internal_sessions — create, alter, index cdis_doc_idx — create, index commercial_doc_internal_sessions_last_at_idx — create, table public.doc_presentations — create, alter, index doc_presentations_active_idx — create, index doc_presentations_last_seen_idx — create, index doc_presentations_owner_active_idx — create, index doc_presentations_owner_email_active_idx — create, table public.doc_presentation_messages — create, alter, index dpm_slug_idx — create, function public.player_bump_message_seq — create, trigger dpm_bump_modseq — drop, create, index idx_dpm_slug_modseq — create, table public.doc_presentation_attendees — create, alter, index doc_presentation_attendees_slug_idx — create, index idx_attendees_slug_creator — create, table public.doc_bot_sessions — create, alter, index idx_bot_last_at — create, index doc_bot_sessions_share_idx — create, table public.player_rate_limits — create, alter, index player_rate_limits_expires_idx — create, table public.doc_tts_objects — create, alter, index doc_tts_objects_created_idx — create, function public.player_rate_limit_bump — create, function public.player_attendance_bump — drop, create, function public.player_archive_scellee — create, trigger dpm_archive_scellee — drop, create, trigger dpa_archive_scellee — drop, create, table public. — alter, function public.player_page_lue — create, function public.player_stats_overview — create, function public.player_stats_overview_internes — create, function public.player_stats_doc — create, function public.player_stats_doc_funnel — create

@@ -145,9 +145,9 @@
   doc_id        text,
   user_email    text,
   user_name     text,
-  num_pages     integer,
-  max_page      integer,
-  total_seconds integer default 0,
+  num_pages     integer constraint ck_internes_num_pages_borne check (num_pages is null or (num_pages >= 0 and num_pages <= 10000)),
+  max_page      integer constraint ck_internes_max_page_borne check (max_page is null or (max_page >= 0 and max_page <= 10000)),
+  total_seconds integer default 0 constraint ck_internes_total_seconds_borne check (total_seconds is null or (total_seconds >= 0 and total_seconds <= 86400)),
   pages_time    jsonb   default '{}'::jsonb,
   device        text,
   os            text,
@@ -341,6 +341,31 @@
   'Compteurs de débit partagés entre instances. Une ligne par clé et par fenêtre ; les lignes '
   'périmées sont écrasées à la première demande suivante, il n''y a rien à purger.';
 
+-- ── LA TRACE DU CACHE DE VOIX ───────────────────────────────────────────────────────────────────
+-- ⚠️ SANS ELLE, LE BUCKET `tts-cache` EST IMPURGEABLE PAR CONSTRUCTION. Chaque synthèse y écrit
+-- `<empreinte>.mp3` et `<empreinte>.json` ; l'empreinte est un condensat (voix + modèle + texte
+-- prononcé) qui ne se rattache à aucune ligne. Le balayage de rétention efface des LIGNES, et pour
+-- les fichiers il efface ceux dont une ligne porte le chemin — la capacité `storage` du contrat
+-- expose `put` et `remove`, jamais `list`. Sans trace, il n'a rien à parcourir. Voir la migration
+-- 0021 et docs/RETENTION.md.
+--
+-- On note l'empreinte et la date, JAMAIS le texte : l'écrire ici recréerait dans la base la donnée
+-- personnelle que le bucket contient peut-être déjà, en la rendant cette fois interrogeable.
+create table if not exists public.doc_tts_objects (
+  hash        text primary key,
+  created_at  timestamptz not null default now()
+);
+create index if not exists doc_tts_objects_created_idx
+  on public.doc_tts_objects (created_at);
+-- RLS activée SANS politique : sous RLS, l'absence de politique refuse tout — seul le rôle de
+-- service passe. Même posture que `player_rate_limits`, et pour la même raison : ni un visiteur ni
+-- l'équipe n'ont de raison de lire cette table.
+alter table public.doc_tts_objects enable row level security;
+comment on table public.doc_tts_objects is
+  'Trace des objets écrits dans le bucket public tts-cache : une empreinte, une date, jamais le '
+  'texte. Sans elle le bucket serait impurgeable — la capacité storage du contrat n''expose pas '
+  'de listage. Voir docs/RETENTION.md.';
+
 -- ⚠️ ET LIRE PUIS ÉCRIRE N'EST PAS ATOMIQUE. Deux appels simultanés lisent le même compte et
 -- écrivent la même valeur : la limite laisse passer le double. L'incrément se fait donc en UNE
 -- instruction, côté serveur.
@@ -673,6 +698,16 @@
 -- Même ordre que la migration, et pour la même raison : `not valid`, on répare, puis on valide.
 -- Une contrainte validée d'emblée échoue sur une table déjà empoisonnée, et une réparation qui
 -- échoue sur la donnée qu'elle vient réparer ne se relance plus.
+--
+-- ⚠️ ET L'ENTRELACEMENT DE CETTE BOUCLE N'EST PAS UN DÉTAIL DE STYLE : NE LA DÉGROUPEZ PAS.
+-- Chaque tour pose UNE contrainte, répare SA colonne, puis la valide — donc au moment où un tour
+-- réécrit une ligne, les seules contraintes en vigueur portent sur des colonnes DÉJÀ réparées.
+-- Sortir les `add constraint` de la boucle pour les faire tous d'abord — la forme qu'avaient les
+-- migrations 0020 et 0023 jusqu'au 26/08 — casse ça : une contrainte `not valid` laisse passer
+-- l'historique mais contrôle TOUTE LIGNE RÉÉCRITE, donc réparer `page` échouerait sur une ligne
+-- dont `max_page` est encore hors plage. C'est exactement ce qui rendait la 0020 incapable de
+-- réparer la ligne de l'incident du 25/08. Ce fichier-ci était correct ; il l'était sans que
+-- personne l'ait écrit, et c'est la raison de ce paragraphe.
 do $$
 declare
   c record;
@@ -684,7 +719,17 @@
       ('commercial_doc_views',    'ck_views_seconds_borne',          'seconds',       86400),
       ('commercial_doc_sessions', 'ck_sessions_max_page_borne',      'max_page',      10000),
       ('commercial_doc_sessions', 'ck_sessions_num_pages_borne',     'num_pages',     10000),
-      ('commercial_doc_sessions', 'ck_sessions_total_seconds_borne', 'total_seconds', 86400)
+      ('commercial_doc_sessions', 'ck_sessions_total_seconds_borne', 'total_seconds', 86400),
+      -- ⚠️ LES TROIS DE LA 0023 ÉTAIENT DÉCLARÉES DANS LE CORPS DE LA TABLE, ET NULLE PART AILLEURS —
+      -- donc une base VIERGE les recevait et une base DÉJÀ INSTALLÉE jamais. C'est mot pour mot le
+      -- défaut que le paragraphe ci-dessus décrit, recommis un jour plus tard sur la table suivante.
+      -- Refusé par la forge : le scénario « base 0.1.64 → rejeu de l'init actuel » a rendu une forme
+      -- amputée de ces trois lignes. Un oubli ici ne casse RIEN et ne se voit nulle part : la table
+      -- accepte simplement n'importe quelle valeur, comme avant, chez les hôtes déjà installés — les
+      -- seuls qui comptent.
+      ('commercial_doc_internal_sessions', 'ck_internes_max_page_borne',      'max_page',      10000),
+      ('commercial_doc_internal_sessions', 'ck_internes_num_pages_borne',     'num_pages',     10000),
+      ('commercial_doc_internal_sessions', 'ck_internes_total_seconds_borne', 'total_seconds', 86400)
     ) as t(tab, nom, col, maxi)
   loop
… 136 more line(s) not shown — a diff this long is itself the answer

supabase/migrations/0020-mesures-bornees.sql
objects it touches — table public.commercial_doc_views — alter, table public.commercial_doc_sessions — alter

@@ -73,24 +73,36 @@
 -- ── 2. L'historique ramené dans la plage ───────────────────────────────────────────────────────
 -- `least`/`greatest` plutôt qu'une suppression : la ligne dit qu'une lecture a eu lieu, et ça reste
 -- vrai. C'est son AMPLEUR qui était fausse. Effacer la mesure effacerait aussi l'événement.
+--
+-- ⚠️ UNE SEULE ÉCRITURE PAR LIGNE, TOUTES COLONNES ENSEMBLE — ET C'EST LA CORRECTION D'UN DÉFAUT
+-- QUI FAISAIT ÉCHOUER CETTE MIGRATION SUR LA DONNÉE MÊME QU'ELLE VIENT RÉPARER. Une contrainte
+-- `not valid` ne contrôle pas l'historique, mais elle contrôle TOUTE LIGNE RÉÉCRITE. En trois
+-- `update` séparés, réparer `page` réécrivait la ligne pendant que son `max_page` était encore
+-- hors plage : `ck_views_max_page_borne` refusait, la migration s'arrêtait, la base restait à
+-- moitié migrée — contraintes posées mais jamais validées, historique intact.
+--
+-- Ce n'est pas un cas de laboratoire : `pageLue` déduit `max_page` de `page`, donc la ligne de
+-- l'incident du 25/08 (`page: 2147483647`) portait DEUX colonnes hors plage. Reproduit contre un
+-- vrai PostgreSQL 16 le 26/08 — la 0020 telle qu'elle était fusionnée échouait dessus.
+--
+-- ⚠️ LE `case` N'EST PAS DÉCORATIF : `greatest(null, 0)` vaut `0` en PostgreSQL, pas `null`. Écrire
+-- les trois colonnes d'un coup sans lui transformerait chaque mesure INCONNUE en zéro mesuré, sur
+-- des lignes dont une seule colonne était fautive. Les `where` par colonne protégeaient les `null`
+-- par construction ; l'écriture groupée doit le faire explicitement.
 update public.commercial_doc_views
-   set page = least(greatest(page, 0), 10000)
- where page is not null and (page < 0 or page > 10000);
-update public.commercial_doc_views
-   set max_page = least(greatest(max_page, 0), 10000)
- where max_page is not null and (max_page < 0 or max_page > 10000);
-update public.commercial_doc_views
-   set seconds = least(greatest(seconds, 0), 86400)
- where seconds is not null and (seconds < 0 or seconds > 86400);
-update public.commercial_doc_sessions
-   set max_page = least(greatest(max_page, 0), 10000)
- where max_page is not null and (max_page < 0 or max_page > 10000);
-update public.commercial_doc_sessions
-   set num_pages = least(greatest(num_pages, 0), 10000)
- where num_pages is not null and (num_pages < 0 or num_pages > 10000);
+   set page     = case when page     is null then null else least(greatest(page, 0), 10000) end,
+       max_page = case when max_page is null then null else least(greatest(max_page, 0), 10000) end,
+       seconds  = case when seconds  is null then null else least(greatest(seconds, 0), 86400) end
+ where (page     is not null and (page     < 0 or page     > 10000))
+    or (max_page is not null and (max_page < 0 or max_page > 10000))
+    or (seconds  is not null and (seconds  < 0 or seconds  > 86400));
 update public.commercial_doc_sessions
-   set total_seconds = least(greatest(total_seconds, 0), 86400)
- where total_seconds is not null and (total_seconds < 0 or total_seconds > 86400);
+   set max_page      = case when max_page      is null then null else least(greatest(max_page, 0), 10000) end,
+       num_pages     = case when num_pages     is null then null else least(greatest(num_pages, 0), 10000) end,
+       total_seconds = case when total_seconds is null then null else least(greatest(total_seconds, 0), 86400) end
+ where (max_page      is not null and (max_page      < 0 or max_page      > 10000))
+    or (num_pages     is not null and (num_pages     < 0 or num_pages     > 10000))
+    or (total_seconds is not null and (total_seconds < 0 or total_seconds > 86400));
 
 -- ── 3. Validation, maintenant que la table est propre ──────────────────────────────────────────
 alter table public.commercial_doc_views       validate constraint ck_views_page_borne;

supabase/recensement-retention.sql

@@ -18,6 +18,7 @@
 \if :{?journaux_mois}\else \set journaux_mois 13 \endif
 \if :{?presentations_mois}\else \set presentations_mois 12 \endif
 \if :{?liens_mois}\else \set liens_mois 13 \endif
+\if :{?voix_mois}\else \set voix_mois 13 \endif
 select 'commercial_doc_views' as perimetre, count(*) as restantes
   from public.commercial_doc_views
   where at < now() - make_interval(months => :journaux_mois)
@@ -60,4 +61,13 @@
   where not exists (
     select 1 from public.doc_presentations p
     where p.slug = a.slug
-      and (p.active or p.updated_at >= now() - make_interval(months => :presentations_mois)));
+      and (p.active or p.updated_at >= now() - make_interval(months => :presentations_mois)))
+union all
+-- ⚠️ CE RECENSEMENT COMPTE DES LIGNES, ET LE PÉRIMÈTRE RÉEL EST UN BUCKET. Il dit qu'aucune trace
+-- hors fenêtre ne subsiste ; il ne peut PAS dire que les deux objets correspondants ont quitté
+-- `tts-cache`, parce que rien en SQL ne voit le stockage. Ce qui reste vrai : sans trace hors
+-- fenêtre, il ne reste plus rien que la purge saurait viser — un objet écrit avant la migration
+-- 0021, lui, n'a jamais eu de trace et n'en aura pas. `docs/RETENTION.md` le dit comme une limite.
+select 'doc_tts_objects', count(*)
+  from public.doc_tts_objects
+  where created_at < now() - make_interval(months => :voix_mois);

Navigation Menu

v0.1.140

Choose a tag to compare

Sorry, something went wrong.

Sorry, something went wrong.

Uh oh!

No results found

Security

Fixed

Added

What changed in the package, by zone — `0.1.139` → `0.1.140`

⚠️ `database` — 3 file(s) changed, not added

Uh oh!

v0.1.140

Security

Fixed

Added

What changed in the package, by zone — 0.1.139 → 0.1.140

⚠️ database — 3 file(s) changed, not added

Uh oh!

What changed in the package, by zone — `0.1.139` → `0.1.140`

⚠️ `database` — 3 file(s) changed, not added