# SQL Rollout Runbook (Bertel3.0)

## Scope

This runbook covers two paths:
- **Fresh install** — build a new database from scratch. Follow the *complete ordered manifest* below; it is the authoritative apply order.
- **Incremental update** — push SQL changes to an already-deployed database (perf/policy/function refresh). Follow *Incremental Update Order* further down.

Top-level SQL file inventory (in `Base de donnée DLL et API/`):
- **Fresh-install core:** `schema_unified.sql`; `migration_sustainability_v5.sql`, `migration_room_type_ref.sql`, `migration_tag_link_position.sql`; `api_views_functions.sql`; `rls_policies.sql`; `object_workspace_safe_write_rpcs.sql`; `object_workspace_gap_rpcs.sql`; `ui_whitelabel_branding.sql`; `media_bucket.sql`; `seeds_data.sql`.
- **Post-seed / post-import fixups:** `migration_legal_siret_canonical.sql`, `migration_object_location_address1_dedupe.sql`.
- **Maintenance and benchmarks:** `maintenance.sql`, `test_performance.sql`.
- **Upgrade-only patch:** `branding_admin_profile_role_patch.sql` for databases that ran an older branding migration; not part of a fresh install.
- **Local / pilot-only inserts:** `lot1_pilot_inserts.sql` is marked `LOCAL ONLY`; use only for the Lot 1 pilot data path after prerequisites are applied.

## Pre-Deployment Checklist

1. Confirm backup/restore path is valid for the target Supabase project.
2. Confirm migration window and rollback owner.
3. Confirm expected API compatibility (no signature or JSON shape breaks).
4. Confirm required extensions are enabled (`postgis`, `pgcrypto`, `uuid-ossp`, `pg_trgm`, `btree_gist`, `unaccent`, `pg_cron` if used).

## Fresh Database — Complete Ordered Manifest (authoritative)

A fresh database MUST be built in this exact order; each step depends on the previous. Verified 2026-06-02 against live PROD (every object below exists there) — see `bertel-tourism-ui/claude_brief/lot1_mapping_decisions.md` §24 (P0.1). Skipping the migrations/RPCs/bucket (as the old order did) leaves a fresh DB that **cannot save tags or room-types and errors on the V5 sustainability seeds**.

0. **Extensions:** `uuid-ossp`, `postgis`, `unaccent` (schema `extensions`), `pg_trgm`, `btree_gist`, `pgcrypto`; `pg_cron` optional.
1. `schema_unified.sql`
2. `migration_sustainability_v5.sql` — DDL; **prerequisite for the V5 sections of `seeds_data.sql`** (creates `ref_sustainability_action_group` + equivalence tables/views). **R3 (audit 2026-06-30):** its 2 coverage views (`v_object_classification_coverage`, `v_object_classification_or_equivalent_scheme`) carry `WITH (security_invoker = true)` — a fresh apply gets it from this file. For an **already-deployed** DB, also run `migration_coverage_views_security_invoker.sql` (the 2 `ALTER VIEW … SET (security_invoker=true)`; idempotent) — without it those views run SECURITY DEFINER and leak `draft` object ids to `anon`. The fresh-apply gate does NOT catch this (confidentiality, not correctness).
3. `migration_room_type_ref.sql` — DDL; adds `object_room_type.room_type_id`.
4. `migration_tag_link_position.sql` — DDL; adds `tag_link.position` (required at runtime by `api.save_object_workspace_tags`).
4b. `migration_iti_duration_elevation.sql` — DDL; greenfield ITI retype `object_iti.duration_hours` → `duration_min INTEGER` + adds `elevation_loss INTEGER`. **Before** `api_views_functions.sql` (its `get_object_resource` / `get_filtered_object_ids` / `get_itinerary_track_geojson` reference `duration_min`). Idempotent (`schema_unified.sql` already ships the new shape, so this is a no-op on a fresh DB). ⚠️ BREAKING for live — apply in lockstep with the frontend build that reads `duration_min`/`elevation_loss`.
4c. `migration_open_status_timezone_perf.sql` — **`refresh_open_status` perf fix (§37)**: rewrites `api.get_local_now_for_timezone` to validate the timezone via a cheap `AT TIME ZONE` try/catch (catches `22023`, falls back to `Indian/Reunion`) instead of `EXISTS (SELECT 1 FROM pg_timezone_names …)`, which enumerated ~1200 zones (~253 ms) on every call. That function is `CROSS JOIN LATERAL`'d once per published object by the every-15-min `api.refresh_open_status()` cron, so the scan was **92.4% of all DB exec time** (`pg_stat_statements`); measured live **21.9 s → 56 ms** with the open-state byte-identical (`mismatches = 0` vs a direct inline computation). Behaviour preserved (valid→that zone; empty/blank/NULL→`Indian/Reunion`; invalid→fallback). Folded into `schema_unified.sql` ⇒ idempotent no-op on a fresh build. No PostgREST reload (signature unchanged; internal callers only).
4d. `migration_open_status_tristate.sql` — **Explorer open/closed pill → TRI-STATE (§133)**: `CREATE OR REPLACE api.refresh_open_status` wraps its open verdict in a `CASE` so `object.cached_is_open_now` becomes genuinely tri-state — **`NULL` when the object has no `opening_period`** (no opening data ⇒ the Explorer renders NO pill), `TRUE` when open (incl. the §93 "open day without hours" arm), `FALSE` when it has hours but is currently closed. Fixes 277/362 published objects that had **no** opening data yet showed "Fermé" (a bare boolean `FALSE` was indistinguishable from "currently closed"), and the pill was gated to HEB/RES only. The column is already `BOOLEAN NULLABLE` ⇒ no schema change; every `open_now` exposure (`get_object_cards_batch`, `list_object_markers`) reads `cached_is_open_now` directly ⇒ they inherit the tri-state; the "Ouvert maintenant" filter (`= TRUE`) already excludes `NULL`. Frontend renders the pill iff `open_now !== null`, for **all** types (the HEB/RES `showOpenStatus` gate removed). **After application: `SELECT api.refresh_open_status();`** to flip existing no-data rows `FALSE → NULL` (else the next 15-min cron tick does it; MV refresh not required — card data reads base `object`, and the filter's `= TRUE` excludes `NULL`/`FALSE` identically). Folded into `schema_unified.sql` ⇒ idempotent no-op on a fresh build. No PostgREST reload (signature unchanged; internal cron caller only). Live-applied 2026-07-01 (verified: 277 NULL / 62 FALSE / 23 TRUE). Covered by `tests/test_open_status_tristate.sql`. Frontend (`ResultCardView` tri-state pill + `explorer-card-display` gate removal) hors manifest. Decision log §133.
5. `api_views_functions.sql`
6. `rls_policies.sql` — defines `api.is_object_owner` (needed by step 7).
7. `object_workspace_safe_write_rpcs.sql` — creates schema `internal` + the write gate `internal.workspace_assert_can_write_object`.
8. `object_workspace_gap_rpcs.sql` — depends on step 7 and on the columns from steps 2 & 4.
8b. `migration_permission_write_paths.sql` — **SP-1 canonical-write authorization**: additive `api.user_can_write_object_canonical` substituted into the workspace gate + 23 write policies, plus the `object.status` guard trigger. After the workspace RPCs (depends on `rls_policies.sql` helpers + the gate); before branding.
8c. `migration_permission_write_paths_b.sql` — **SP-1b**: additive companion `canonical_write_*` policies completing canonical coverage for ~25 more editor-write tables (incl. `object_taxonomy`/`object_classification`, which had no write policy at all). After 8b.
8d. `migration_rls_read_gate_p03.sql` — **P0.3 RLS read gate**: replaces the `USING(true)` SELECT policy on 40 object-child tables with `api.can_read_object(...)` (= parent object `published` OR `api.can_read_extended`), closing the anon draft-read leak; adds 2 missing FK indexes (`object_price_period.price_id`, `object_place_description.place_id`). After 8c (only needs `api.can_read_extended` from `rls_policies.sql`).
8e. `migration_sp4_list_org_members.sql` — **SP-4 — roster read RPC `api.rpc_list_org_members`** (member identities for the Team admin page): `SECURITY DEFINER` function bridging `auth.users.email` (not client-readable) to an ORG admin's team view; gated to platform superuser or an active admin of the requested ORG. After 8d (needs `api.is_platform_superuser` from `rls_policies.sql`).
8f. `migration_object_status_lifecycle.sql` — **Object status state-machine RPC `api.rpc_set_object_status`** (publish / unpublish / archive / restore) + `api.rpc_publish_object` rewritten as a thin wrapper over it. Gated by `api.user_can_publish_object`; the transition is re-checked by `trg_guard_object_status_change`. After 8b (needs `api.user_can_publish_object` from `rls_policies.sql` + the status guard trigger from `migration_permission_write_paths.sql`).
8g. `migration_object_act_rls.sql` — **object_act RLS security fix**: `object_act` (the ACT type-extension) shipped with RLS off + 0 policies while anon/authenticated held full table grants, so it was directly readable/writable via PostgREST, bypassing the publication gate. Enables RLS + `read_object_act` (`api.can_read_object`) + `canonical_write_object_act` (`api.user_can_write_object_canonical`), and re-asserts anon/authenticated EXECUTE on the write predicate (P0.3 gotcha). After 8d (needs `api.can_read_object`).
8h. `migration_rls_ref_and_bak_cleanup.sql` — housekeeping from the 2026-06-04 audit: enables RLS (pub-read / admin-write) on 3 `ref_*` tables that shipped RLS-off (`ref_classification_equivalent_action`, `ref_classification_equivalent_group`, `ref_sustainability_action_group`) and drops 5 leftover `*_bak_20260519_082607z` backup tables (no-op on a fresh DB). After step 6 (needs `api.is_platform_superuser`) and step 2 (the 3 ref tables).
8i. `migration_explorer_rls_setbased.sql` — **Explorer `statement_timeout` fix**: replaces the per-row `api.can_read_extended(id)` in the `object` SELECT policy `extended_objects_org_actor` with a hashed-set membership test (`id IN (SELECT api.current_user_extended_object_ids())`), so the read predicate is hoisted to a single InitPlan instead of being evaluated once per draft row (the editor Explorer requests `['published','draft']`, bypasses the published-only MV, and scans the full `object` table). Adds the SECURITY DEFINER set fn (`anon`/`authenticated`/`service_role` EXECUTE; the object policy — role `public` — references it) and re-points `api.can_read_extended` to delegate to it. Visibility unchanged (byte-equivalent to the 4 paths; live equivalence-verified). After 8d (needs `api.can_read_extended`). Folded into `rls_policies.sql` ⇒ idempotent no-op on a fresh build.
8j. `migration_cards_batch_authorize_definer.sql` — **Explorer cards-batch perf fix (§36)**: the residual after 8i. `api.get_object_cards_batch` was `SECURITY INVOKER`, so its child-table reads ran under the caller's RLS — and those tables' SELECT is the per-row OR of three `SECURITY DEFINER` predicates (read policy + the two `FOR ALL` write policies that also apply to SELECT), with `is_object_owner` short-circuiting first for the editor (a platform superuser), so a set-based read-policy rewrite would not help. This makes cards_batch `SECURITY DEFINER` and **authorize-once**: it filters `p_ids` to the caller's readable set (`published ∪ api.current_user_extended_object_ids()` = the `object` table's own SELECT visibility, via the new `api.current_user_readable_object_ids()` set fn — one InitPlan) and reads child data RLS-free for only those ids. Self-authorization is mandatory because the function is PUBLIC-executable. Measured live: 1290 ms → 74 ms (cards_batch); the editor list RPC `list_object_resources_filtered_page` 1.37 s → 168 ms. Visible set byte-identical per persona (live equivalence-verified: editor hash unchanged; anon = published only). Also re-applies the `object_description.visibility` field-level gate the DEFINER body bypasses. Advisors flag it under `0028/0029_*_security_definer_function_executable` — **intentional** (a public read RPC that self-authorizes, like the existing `can_read_*` family). After 8i (needs `api.current_user_extended_object_ids`). NOT folded into `api_views_functions.sql` (step 5 cannot forward-reference the step-8i helper); that file keeps the `SECURITY INVOKER` baseline, overridden here. **§38 (2026-06-04):** the `distinct_ids` authorize-once gate gained a PUBLISHED-ONLY FAST-PATH — split into `EXISTS(published) OR id IN (extended)` so the per-user extended-scope scan is skipped entirely for all-published pages (read-only / public card-deck + map); set-equivalent ⇒ the §36 no-leak guarantee is unchanged (re-verified per persona).
8k. `migration_rls_initplan_sweep.sql` — **RLS auth-initplan sweep (§39)**: wraps the raw `auth.role()`/`auth.uid()` calls as `(select auth.x())` in 18 object-family RLS policies (object + object_external_id / object_membership / object_origin / object_place_description / object_private_description / object_relation / object_review / object_room_type[+_amenity,+_media] / object_sustainability_action[+_label]) so the planner hoists them to a single InitPlan instead of re-evaluating per scanned row (Supabase `auth_rls_initplan` lint; these `FOR ALL` write policies also gate SELECT and the tables grow with object count). Semantics-identical — a scalar subquery returns the same value; live-verified byte-equivalent modulo the wrap, with owner/stranger UPDATE gating intact. `ALTER POLICY` ⇒ runs after `rls_policies.sql` (step 6) + the SP write-auth fns; NOT folded into `rls_policies.sql` (its bare-auth `CREATE POLICY` is overridden here, per the 8j precedent). Deferred (documented, low value): object_version audit partitions, media_tag, and the ~120 ref_*/RBAC/audit tiny-table policies (bounded rows, admin-only, off the hot path).
8l. `migration_ref_commune.sql` — **§41 (T1b zones)**: seeds `ref_commune` (the 24 communes of La Réunion, INSEE 97401–97424) with the standard `ref_*` pub-read / admin-write RLS pair, and adds the FK `object_zone.insee_commune → ref_commune.insee_code` (greenfield-safe — `object_zone` was empty on live 2026-06-05). Backs the §16 "communes desservies" multi-select. After step 6 (needs `api.is_platform_superuser`) and step 1 (`object_zone`). Idempotent; listed in the manifest (not folded into `schema_unified.sql`).
8m. `migration_facet_applicability.sql` — **§46 type→facet applicability registry**: creates `ref_facet_registry` + `ref_facet_applicability` (single machine-readable source of truth for which object_type may carry which type-specific facet table), attaches `trg_assert_facet_applicable` to the 13 enrolled tables (object_iti family ×7, object_fma ×2, object_act, object_room_type, object_meeting_room, object_menu), guards `object.object_type` changes (`trg_guard_object_type_change`), adds the ops report `api.facet_applicability_violations()`, and side-fixes `rpc_create_object`'s hardcoded (pre-ACT) valid-type message. The editor consumes the same registry (module gating); legacy violating rows are reported, never auto-deleted. After step 6 (needs `api.is_platform_superuser`) and step 1 (facet tables). Idempotent; listed in the manifest (not folded into `schema_unified.sql`).
8n. `migration_object_fma_write_policy.sql` — **§47 object_fma write-policy gap fix**: `object_fma` shipped with RLS enabled but ONLY SELECT policies (`pub_fma_published`/`ext_fma_org_actor`), so the editor's direct PostgREST upsert was denied for every non-service role (mirror of the `object_act` gap fixed by 8g). Adds the per-command canonical write triple (`canonical_ins/upd/del_object_fma`, predicate `api.user_can_write_object_canonical(object_id)`) — per-command (§47) so write predicates never apply to SELECT (no P0.3 EXECUTE grant needed). After 8b (`api.user_can_write_object_canonical`) and 8m. Idempotent.
8o. `migration_write_policy_percommand.sql` — **§47 write-path convergence**: collapses the 4 overlapping `FOR ALL` write-policy families (`owner_*` SP-1, `workspace_*`, `canonical_write_*` SP-1b, legacy admin) into ONE per-command triple per table (`canonical_ins/upd/del_<table>`; admin-only tables use `admin_ins/upd/del_<table>`; predicate `api.user_can_write_object_canonical` + documented carve-outs: §20 description, membership/tag_link admin arms, media/location object-XOR-place dual path, +CREATEDBY legacy legs on the room-type trio and media_tag) across 57 object-child tables. Per-command policies no longer apply to SELECT ⇒ ends the P0.3 write-predicate-pollutes-read gotcha class and unblocks set-based read gates (8p). Also closes the SP-1b holes (`opening_time_period`/`_weekday` had only `is_object_owner` workspace policies — permission-based canonical writers failed mid-RPC). Effective permissions unchanged (canonical ⊇ owner; admin arms preserved); live before/after persona-probe + snapshot verified (93 FOR ALL → 0, 174 per-command). After 8b/8c/8g/8n. Idempotent; NOT folded (supersedes policy blocks across 5 source files — see header). ⚠ See the Incremental Update caveat below.
8p. `migration_child_read_gate_setbased.sql` — **§47/§38 set-based child read gates**: rewrites the 25 FLAT child read policies (`read_<t> USING api.can_read_object(<col>)` — the 24 from 8d + `read_object_act` from 8g) to the §38 split form `EXISTS(published) OR <col> IN (SELECT api.current_user_extended_object_ids())`. Set-equivalent to `can_read_object` (= published OR extended) but hoists the per-user extended scope to one InitPlan instead of a per-row `SECURITY DEFINER` scalar over the full child scan (§35). EXPLAIN-verified on live: the per-row `can_read_object` filter becomes two hashed SubPlans (published index set + extended set), no per-row function call; anon byte-equivalent (published-only, no leak). Scope is flat-keyed policies only — NOT `object_iti` (separate pub/ext pair) nor the nested-EXISTS policies (menu items, opening sub-tree, media_tag, place_description, tag_link, location), logged as deferred remainder. After 8d/8g + 8o (write policies out of SELECT). Idempotent.
8q. `migration_object_act_asc_applicability.sql` — **§48 object_act↔ASC**: adds ('object_act','ASC') to `ref_facet_applicability`. The §46 baseline (ACT-only) had orphaned the activity editor — ASC rendered the UI but could not save; ACT could save but had no UI. Paired with the frontend ACT→ASC archetype remap. After 8m. Idempotent; covered by `test_facet_applicability.sql`.
8r. `migration_actor_links_editor.sql` — **§48 actor-link write path**: converges `actor_object_role` writes to the §47 per-command canonical triple (`canonical_ins/upd/del_actor_object_role`, predicate `api.user_can_write_object_canonical`) and retires the legacy admin `FOR ALL` (canonical subsumes admin/superuser via `is_object_owner` — see 8o's predicate note); rewrites the read policy `ext_actor_object_role_read` in the §38/§39 form (set-based extended scope, `(select auth.x())` wraps, actor-self arm preserved — NO published-read arm added, the "read under-exposure" item stays deferred); gives `api.save_object_relations` a real `actors` branch (delete-all + re-insert of `actor_object_role`; role by id or `ref_actor_role.code`; visibility mirror of the table CHECK; ≤1 primary per role via `uq_actor_object_role_primary`; `actor_channel`/`actor_consent` stay OUT of the contract — same body folded into `object_workspace_safe_write_rpcs.sql`, fresh == live); and adds the gated picker `api.search_actors(p_query)` (`SECURITY DEFINER`, gated on `api.current_user_can_edit_objects()` so read-only members cannot enumerate actor PII, scoped to the caller-readable actor set (ext_actor_read mirror) with LIKE-wildcard escaping — the picker only offers actors the INVOKER save path will accept; the advisor's DEFINER flag is expected, §36 precedent). After steps 6/7 + 8b. Idempotent; covered by `test_actor_links_editor.sql`. ⚠ See the Incremental Update caveat below.
8s. `migration_contact_channel_read_gate.sql` — **§49 contact_channel read gate**: `contact_channel` predated the 8p sweep and kept its pre-§38 SELECT pair — `pub_contacts_public USING (is_public IS TRUE)` (no publication gate ⇒ anon could direct-PostgREST read the PUBLIC contact rows of draft/hidden/archived objects) + per-row `ext_contacts_org_actor` (`api.can_read_extended`). Replaces both with the single §38 split form `read_contact_channel`: `(EXISTS(published parent) AND is_public IS TRUE) OR object_id IN (SELECT api.current_user_extended_object_ids())` — the public arm keeps the field-level `is_public` gate, the extended arm folds `ext_contacts_org_actor` in set-based (semantics-preserving: `can_read_extended` delegates to the same set fn; one InitPlan per §35, EXPLAIN-verified live). Consumers unaffected: the detail/drawer RPCs are `SECURITY DEFINER` and re-apply `is_public`/`published` internally; the two INVOKER deep-data fns join through `object` whose RLS already hides non-published rows from anon; the editor §03 loader reads under the extended arm. Folded into `rls_policies.sql` (8i precedent) ⇒ idempotent no-op on a fresh build and NO incremental caveat (re-applying `rls_policies.sql` recreates the new form, not the leaky pair). After step 6 (`api.current_user_extended_object_ids`) + 8o. Idempotent; covered by `test_contact_channel_read_gate.sql`.
8t. `migration_media_description_read_gate.sql` — **§51 media + object_description read gates** (the §49 deferred siblings): both tables kept pre-§38 SELECT pairs whose public arm is a bare FIELD-LEVEL flag — `pub_media_published USING (is_published IS TRUE)` and `pub_descriptions_public USING (visibility='public')` — with **no parent-publication gate**, so anon direct PostgREST could read the flagged rows of draft/hidden/archived objects (same class as 8s). Replaces each pair with one §38 split-form policy (`read_media` / `read_object_description`): published arm keeps the field-level gate, extended arm folds the per-row `ext_*_org_actor` (`api.can_read_extended`) in set-based (one InitPlan, §35). `media` is object-XOR-place keyed (`chk_media_target_present`), so BOTH its arms carry a place leg probed through `object_place` (rides 8p's `read_object_place`); the old ext arm was NULL-dead on place rows, so this also FIXES an under-exposure — org members now read their own unpublished place-keyed media (the §16 sub-place media loader). `media.visibility` is deliberately NOT folded into the gate (unchanged semantics; deferred — see decision log §51). Consumers verified: DEFINER RPCs bypass+re-gate; `get_media_for_web` (INVOKER, no parent check) stops leaking draft media through RLS; the other INVOKER readers join through `object`. After step 6 (`api.current_user_extended_object_ids`) + 8o + 8p. Folded into `rls_policies.sql` (8i/8s precedent) ⇒ idempotent no-op on fresh, NO incremental caveat. Covered by `test_media_description_read_gate.sql`.
8u. `migration_object_type_spu.sql` — **§53 new object type SPU « Service public »**: standalone public-service POIs (toilettes publiques, point d'eau potable, borne de recharge) get their own type — PSV stays « Prestataire de services » (the 18 live transport/rental providers deliberately retyped ORG→PSV on 2026-05-14; the contrary `PSV = Service public` legend in `docs/index.html` §1.7 was a stale 2025-10-04 doc error, fixed in the same pass). Adds the enum value (DO/ADD VALUE IF NOT EXISTS — **two transactions on live**: a value added to an enum cannot be USED in the same transaction; psql autocommit applies the single file safely) + `taxonomy_spu` registry row (position 85) + technical root + 3 assignable sub-categories (`public_toilets`/`drinking_water`/`electric_charging` — codes deliberately mirror the `ref_amenity` codes: an amenity describes equipment OF an object, an SPU object IS the facility). NO `ref_facet_applicability` rows (generic modules only, same class as PCU/PNA/VIL) and no capacity rows; `rpc_create_object` validates types dynamically (§46) and `api.generate_object_id` is generic — no function change. Frontend paired in the same pass: `ObjectType` union, `TYPE_LABEL`/`TYPE_ARCHETYPES` (SRV archetype), drawer labels, explorer SRV family, dashboard filter. After step 1 (enum) + the `ref_code_domain_registry`/`ref_code` DDL; live-applied as MCP migrations `object_type_spu_enum` + `object_type_spu_taxonomy`. Idempotent; covered by `test_object_type_spu.sql`.
8v. `migration_room_type_read_gate.sql` — **§55 object_room_type trio read gates + 8o link-write repair**: the trio kept the ORIGINAL `rls_policies.sql` SELECT policies — public arm = the row's OWN `is_published` flag (which **defaults TRUE**) with **no parent-publication gate** (anon could direct-PostgREST read room types, incl. `base_price`, of draft/hidden/archived objects — same class as 8s/8t), and the two link tables had NO extended arm at all (org members could not read links of their own `is_published=FALSE` rooms — the §05 editor loader reads them directly). Replaces each with ONE §38 split-form policy (`read_object_room_type[_amenity|_media]`); the link tables follow through the parent room (anon arm: `EXISTS(rt is_published + parent published)`; ext arm: `room_type_id IN (rooms of the extended set)` — the rt probes also ride `read_object_room_type` itself, RLS applies inside policy subqueries). **ALSO repairs a deny-all write bug found in this pass**: 8o §§43–44 wrote the link-table predicates with an UNQUALIFIED `room_type_id` inside `EXISTS (SELECT 1 FROM object_room_type rt …)`; `object_room_type` already had its own `room_type_id` column (step 3, `migration_room_type_ref.sql`), so the reference bound to the INNER table (`rt.id = rt.room_type_id` — never true) ⇒ INSERT/UPDATE/DELETE on `object_room_type_amenity`/`_media` denied for every PostgREST role (latent only because the trio had 0 rows; the §05 room saver would have hard-failed on first authoring). Re-created with the SAME §47 semantics (canonical OR +CREATEDBY), outer column qualified; **8o's source is fixed in place too**, so the incremental-caveat "re-run 8o" no longer re-breaks them. Consumers: `get_object_room_types` (INVOKER) stops leaking draft rooms; `get_object_resource` authorizes-once + field-gates its (§54-pending-deploy) `room_types` emission — consistent by construction. After step 3 + step 6 + 8o. Reads folded into `rls_policies.sql` (8s/8t precedent) ⇒ idempotent no-op on fresh, NO incremental caveat. Live-applied as MCP migration `room_type_read_gate_and_link_write_fix`. Covered by `tests/test_room_type_read_gate.sql`.
8w. `migration_object_review_read_gate.sql` — **§56 object_review read gate** (the LAST member of the 8s/8t/8v bare-flag class): `object_review` kept the ORIGINAL `rls_policies.sql` SELECT policy `"Lecture publique des avis" USING (is_published = true)` — the row's OWN flag (**defaults TRUE**) with **no parent-publication gate** (anon could direct-PostgREST read flagged reviews of draft/hidden/archived objects) and NO extended arm (org members could not read the unpublished/moderated-out reviews of their own objects). Replaced with ONE §38 split-form policy `read_object_review`: `(is_published IS TRUE AND EXISTS(parent published)) OR object_id IN (SELECT api.current_user_extended_object_ids())`. 0 rows live (review import deferred, decision log §43) ⇒ forward-looking, closed BEFORE the import lands. The per-command `admin_ins/upd/del_object_review` write family (8o) is untouched. Consumers: `api.get_object_reviews` (INVOKER, already filters `is_published`) stops returning draft-object reviews to anon — the fn itself was repaired in the same pass (pre-existing 42803 nested-aggregate bug in `by_source` + LIMIT/OFFSET-outside-aggregate pagination no-op; fixed in `api_views_functions.sql`, live-applied as MCP migration `fix_get_object_reviews_aggregates`, RPC-probed in the test); the `update_object_cached_rating_metrics` trigger recomputes via service_role (BYPASSRLS) on the import path — unaffected. After step 6 (`api.current_user_extended_object_ids`) + 8o. Folded into `rls_policies.sql` (8s/8t/8v precedent) ⇒ idempotent no-op on fresh, NO incremental caveat. Live-applied as MCP migration `object_review_read_gate`. Covered by `tests/test_object_review_read_gate.sql`.
8x. `migration_object_type_prd.sql` — **§57 new object type PRD « Producteur »** (the ONE genuine type gap found by the 2026-06-11 typology analysis, `docs/research/type-gap-analysis-2026-06-11.md`): producteurs ouverts au public (agritourisme/dégustation/vente directe — thé, vanille, curcuma, miel, distilleries) were split WITHOUT an arbitration rule across 4 `taxonomy_loi` nodes + `taxonomy_res` « Distillerie - sucrerie » + COM (Apidae has a whole DEGUSTATION/« Producteur » type; DATAtourisme has TastingProvider). Adds the enum value (**two transactions on live** — enum-use rule, same as 8u) + `taxonomy_prd` registry (position 22) + technical root + 6 assignable sub-categories (plantation / exploitation_agricole / agrotourisme / produits_terroir / distillerie_brasserie / apiculture — the first 5 mirror the migrated nodes 1:1). NO facet rows (generic modules; an `object_degustation` facet stays addable later via `ref_facet_registry`, 1 INSERT). Arbitration rule (§57, LOCKED): production+accueil → PRD · repas → RES · revente seule → COM · prestation encadrée → ACT. Frontend paired in the same pass (type union, labels, VIS archetype, explorer family, filters). Live-applied as MCP migrations `object_type_prd_enum` + `object_type_prd_taxonomy`. Idempotent; covered by `tests/test_object_type_prd.sql`.
8y. `migration_taxonomy_seeds_coverage.sql` — **§57 taxonomy coverage seeds**: only 10 of the taxonomy domains existed — PNA, PCU, VIL, ITI, FMA, HPA, RVA, ASC had NO registry row and NO nodes (the §50 import-candidate fiches and the editor §01 sub-category picker depend on them). Seeds the 8 registries + technical roots + 45 assignable nodes (PNA: plage/bassin/cascade/belvédère/forêt/site volcanique/littoral/arbre remarquable/site géologique · PCU: musée/édifice religieux/MH/patrimoine industriel/architecture créole/site historique · VIL ·  ITI (incl. `scenic_drive` route touristique) · FMA (incl. `sports_event` + `seasonal_market` — éditions datées only, the perennial market PLACE is COM per the §57 rule) · HPA (incl. `motorhome_area`) · RVA · ASC), plus the SPU « services & équipements au public » extensions (11 nodes: picnic_area — ~300 aires ONF importable, tourist_info_office (BIT as POI, linked to the ORG via org_link), public_pool, public_sports_facility, playground, tourist_parking, bus_station, carpool_area, marina, motorhome_services, public_library), the COM market-place nodes (weekly_market/covered_market/flea_market — recurrence carried by opening hours, NOT object_fma occurrences), the LOI wellness branch (3 children) + `conference_venue` (MICE), the `ref_facet_applicability` (object_meeting_room, LOI) row (the §46 nominal loosening, closing the backlog item), and the `sur_le_parcours_de` relation role (POI ↔ itinerary). After 8x (taxonomy_prd registry uses 'PRD') and the `ref_code_domain_registry`/`ref_code` DDL. Live-applied as MCP migration `taxonomy_seeds_coverage`. Idempotent; covered by `tests/test_object_type_prd.sql`.
8z. `migration_crm_module.sql` — **§61 CRM module (P2.2), actor-centered (design v2)**: one file resorbs the CRM import debt and ships vocabularies + access layer + the actor-centered model. (1) Import debt: `demand_topic_id`/`request_mood_id` were NULL on every imported interaction (resolved topics parked in `extra.oti_demand_topic_id`, raw emoji moods in `extra.humeur_raw`) — the backfills land 1 344 topics + 3 175 sentiments (100 % of carriers; the `extra` sources are preserved). (2) Topic merge: the 20 sujets OTI move from the misplaced `crm_demand_topic_oti` domain (DEFAULT partition) into `demand_topic` (automatic row movement, stable UUIDs ⇒ `extra.oti_demand_topic_id` stays resolvable); the never-referenced 11 generic topics + 22 `demand_subtopic` codes + their 33 translations are removed behind a fail-closed reference re-check (one source of truth — the `demand_subtopic` partition/column/FK stay as the EMPTY slot for a future sous-sujets vocabulary). (3) Sentiment: new `crm_sentiment` domain — dedicated partition + uniques `(id)`/`(code)` + the house ref_* RLS pair (parent policies do NOT cover direct partition access) — 6 codes mapped from the import emojis (😃 tres_positif · 🙂/ok positif · 🤔 interrogatif · 😨 inquiet · 😡 mecontent · 😭 tres_mecontent); `request/response_mood_id` renamed `*_sentiment_id` (0 consumers) with the FKs re-pointed to `ref_code_crm_sentiment` (`ref_code_mood` keeps its tourism-envies consumers). (4) Access: RPC-only authorize-once + DEFINER (§36) — read = publisher-ORG members (`api.current_user_crm_object_ids`, set-based §35; `user_can_read_crm`), write = `write_crm_notes` OR ORG admin OR superuser (`user_can_write_crm`); the crm_* tables are locked to per-command admin write families with wrapped predicates (§47/§39), so PII is NEVER reachable via direct PostgREST — the `security_definer` advisor WARNs on the RPCs are expected (§36 class); + 2 `crm_interaction` indexes. (5) ACTOR-CENTERED (user design correction, v2): the interaction is anchored on the ACTOR — `crm_interaction.object_id` goes NULLABLE + `chk_crm_interaction_anchor` (≥1 anchor actor/object) + a partial actor index; the actor scope is DERIVED from the publisher object scope (`api.current_user_crm_actor_ids` = actors linked via `actor_object_role` to an in-scope object ∪ actors carrying an interaction on one; `user_can_read/write_crm_actor`); `list_crm_directory` REWRITTEN per actor (696 actors, 61 ms live), NEW `list_actor_crm` (360° actor sheet), `list_object_crm` gains the `actors` key (roles via `actor_object_role`/`ref_actor_role`) — bidirectional navigation; `save_crm_interaction` anchors actor-first (object context optional; arms the object predicate when a context exists, else the actor predicate; adding a context NULL→object is allowed, re-parenting object→object refused 22023); `list_crm_timeline` (keyset composite, 7 args) LEFT JOINs `object` and arms the actor predicate for context-free interactions; `crm_task` stays object-anchored (`list_crm_tasks`/`save_crm_task`). `rls_policies.sql` is fixed IN PLACE (the retired crm_* FOR ALL family no longer exists at the source) ⇒ NO incremental caveat, and `seeds_data.sql` was edited in the same pass (topics seeded directly under `demand_topic`, generic blocks removed) ⇒ fresh == live; on a fresh DB the merge/backfills are guarded no-ops. After step 1 (crm_* tables) + step 4 (`user_has_permission`/`is_platform_superuser`/`current_user_admin_rank`). Idempotent, transaction-wrapped. Live-applied as MCP migrations `crm_module` + `crm_sentiment_partition_rls` + `crm_actor_centered`. Covered by `tests/test_crm_module.sql` (executed on live in ROLLBACK, PASS). Decision log §61.
9. `ui_whitelabel_branding.sql` — defines `api.is_platform_admin` (a fresh install uses this full file, not the patch).
10. `media_bucket.sql` — `media` storage bucket + RESTRICTIVE anon/authenticated write-deny.
11. `seeds_data.sql` — depends on `ref_sustainability_action_group` from step 2.
12. `migration_legal_siret_canonical.sql` — **data fixup; run AFTER seeds** (updates seeded `ref_legal_type` rows; safe no-op on `object_legal` when empty).
13. `migration_object_location_address1_dedupe.sql` — post-import data hygiene (no-op on a fresh DB; re-run after each bulk import).
13b. `migration_taxonomy_assignable_cleanup.sql` — **§01 sous-catégorie review (2026-06-08)**: post-seed data fixup. Sets every non-root `ref_code` taxonomy node `is_assignable=true` (each domain's technical `root` stays the only non-assignable node) so an object can be assigned at **any level** of an arbitrary-depth tree and the **full breadcrumb** renders (the root is filtered out by the same `is_assignable` predicate the read functions already use — so **no SQL read/write change is needed**); capitalizes 4 lowercase leaf names (`bulle`/`chambre`/`cottage` in `taxonomy_hlo`, `atelier` in `taxonomy_loi`); merges redundant HLO `chambre_d_hote` assignments into `chambre_d_hotes`; moves/removes the misplaced HLO `table_d_hote` node into `chambre_d_hotes`; flattens the empty PSV `leisure_equipment_rental` wrapper by promoting its only child `cycle_scooter_rental` (« Location de vélos et trottinettes ») to the domain root; and refreshes `object.cached_taxonomy_codes` for the 5 affected domains. Idempotent, pure DML; **before step 14** (the cache feeds `mv_filtered_objects`). ⚠️ The hlo/loi/org/psv/res taxonomy nodes themselves currently live only in `old_data_enrichment_20260512/01_enrich_imported_old_data.sql` (NOT in this manifest), so parts of this fixup no-op on those domains in a *fresh* build until that data is folded into the seed — a **separate, pre-existing deploy-integrity gap**. **CLOSED 2026-07-01** by `migration_taxonomy_trees_seed.sql` (the `taxo` step below) which versions the full live taxonomy trees and runs last; a fresh build now reproduces the hierarchy and this 13b fixup lands on populated domains.
13c. `migration_capacity_applicability_seed.sql` — **§46 companion**: seeds `ref_capacity_applicability` (metric → object_type), which existed since the base schema (`schema_unified.sql`) but had **0 rows** — so the Explorer's `bucketCapacityOptions` filtered capacity facets against an empty set (dead HOT/RES capacity facets). 54 rows: `max_capacity` for every enum type + the per-family matrix (beds/bedrooms/pitches/campers/tents/vehicles for the HEB family; seats/standing_places for RES + leisure; bikes; floor_area_m2…). **AMENDED 2026-06-11 (§07 review): PRD/SPU coverage** — live ran 13c before the PRD (8x) / SPU (8u) enum values existed, so the `max_capacity` cross-join missed them; the amended file adds the PRD extras (seats, standing_places) + SPU extras (seats, vehicles) and was re-run on live (idempotent; MCP migration `capacity_applicability_prd_spu`; 60 rows, max_capacity × 19 types verified). Editor §07 now filters its metric options by this table (same review); the Explorer bucketing consumed it since 13c. **After step 11 (`seeds_data.sql` — needs `ref_capacity_metric`)**; runs as a post-seed data fixup. Idempotent (`ON CONFLICT DO NOTHING`); reversible (`DELETE FROM ref_capacity_applicability`).
13d. `migration_loi_prd_cleanup_retype.sql` — **§57 LOI/RES → PRD/PCU/COM/SPU data pass** (post-seed fixup; no-op on a fresh DB): re-routes the 43 live taxonomy assignments and retypes their objects per the §57 arbitration — 34 → PRD (33 LOI agro + Vakoa Distil ex-RES), 2 → PCU (Musée incl. La Cité du Volcan — kills the LOI/PCU « Musée » duplicate concept), 5 → COM (Boutique/Souvenirs), 1 → SPU (« Médiathèque », which had been mis-assigned to the LOI « Souvenirs » node). ⚠ Order is FORCED by `validate_object_taxonomy_assignment` (an assignment's domain must match the object's CURRENT type): capture → DELETE old links → retype → re-INSERT under the new domain, all in ONE transaction (a naive cross-UPDATE of `object_taxonomy` is rejected with 23514 — learned on the first live apply). The emptied source nodes (4 agro + Musée + Boutique + Souvenirs in `taxonomy_loi`, « Distillerie - sucrerie » in `taxonomy_res`) are DELETED behind a no-assignments/no-children guard; `api.refresh_object_filter_caches` re-runs for every touched object (13b precedent; refresh the MV CONCURRENTLY afterwards on live). Live result verified: PRD=34, LOI 142→101, RES 137→136, COM 1→6, PCU 0→2 (+2 §50 fiches pending), SPU 1→2. Found & flagged (NOT auto-fixed): a pre-existing duplicate « La Cité du Volcan » (`LOIRUN000000000A` published/empty vs `LOIRUN000000010V` published/documented, now PCU) — merge/archive decision pending. After 8x + 8y (+ 13b/13c order preserved). Idempotent. Live-applied as MCP migration `loi_prd_cleanup_retype`.

13e. `migration_taxonomy_label_hygiene.sql` — **Taxonomy label hygiene (2026-07-03; live remediation, no-op on a fresh DB)**: (A) deletes the junk `taxonomy_loi/loi` node — it reused the domain's own type code as a child category (name `LOI`) and had been dead until 13b's blanket `is_assignable=true` flip surfaced it as a **selectable** sub-category at the top of the Loisir tree (`buildTaxonomyNodeOptions` re-parents root's children to top-level); untags its lone carrier (`LOIRUN00000001A0`, draft — the tag = the type code echoed ⇒ zero information) and refreshes `object.cached_taxonomy_codes` for `taxonomy_loi`. (B) humanizes the 19 technical `taxonomy_*/root` labels from the raw type acronym (LOI/PCU/PNA…) to the canonical FR label (mirrors `TYPE_LABEL` in `archetypes.ts`) + normalizes `name_i18n`/`description`/`description_i18n` (drops the lone stale `name_i18n.en:"ACT"`). Roots are `is_assignable=false` and filtered out of the picker (`buildTaxonomyNodeOptions` drops `code='root'`) ⇒ invisible in the editor, but this keeps raw acronyms off any ref_code/admin surface. **Both fixes are also baked into the `taxo` snapshot below** (junk `loi` node + edge removed; 19 root lines humanized) so a fresh build converges identically without this file — 13e is the delta for the already-deployed DB. Idempotent, pure DML. After 8y/13b (roots + assignability exist); before `taxo`. Live-applied 2026-07-03 via MCP (verified: junk node 1→0, junk tags 1→0, roots acronym 19→0, 0 orphan closure rows).

taxo. `migration_taxonomy_trees_seed.sql` — **Versions the FULL live taxonomy trees** (211 `ref_code` nodes / 192 parent links across the 19 `taxonomy_*` domains). Closes the deploy-integrity gap flagged in 13b: the hlo/loi/org/psv/res (and res/com/loi/spu/…) taxonomy nodes lived only in `old_data_enrichment_20260512/01_enrich_imported_old_data.sql` (NOT in the manifest), so a fresh DB could not reproduce the taxonomy hierarchy — caught by the fresh-apply gate 2026-07-01 (`test_reference_catalog.sql`: *taxonomy_res parent_code never resolved*). Generated by a read-only export of live (`.tmp_pgapply/gen_taxonomy_seed.cjs`). **Idempotent + ORDER-INDEPENDENT**: Phase 1 upserts every node (`ON CONFLICT (domain,code) DO UPDATE`, no parent_id); Phase 2 resolves `parent_id` by `(domain, parent_code)` via a `VALUES` join, so it never depends on insert order or per-DB uuids. Runs **LAST** in the fresh manifest — after `seeds_data.sql` + every taxonomy migration (8x/8y/13b/13d) and **before** the MV refresh — so it converges each domain to the live state and the MVs include it. Validated against live in a rolled-back transaction (syntax + idempotence, 0 persisted; `run_sql_file.cjs --validate`). On an already-deployed DB this is a no-op (live IS the snapshot). Covered by `tests/test_reference_catalog.sql` (hierarchy assertions).
14a. `migration_media_visibility_gate.sql` — **§59 media.visibility gate** (closes the §51-deferred field gate, found again by the §05 Médias editor-review consumer sweep): (A) `read_media`'s published arm composes `(visibility IS NULL OR visibility = 'public')` (§49 doctrine — the flag composes, never substitutes; BOTH the object leg and the place leg) — pre-14a, anon direct PostgREST could read 'private'/'partners' media rows of PUBLISHED objects; extended arm unchanged (the §05 editor loads private/unpublished rows). (B) `update_object_cached_main_image()` + the `maintenance.sql` sweep add the same filter to the is_main pick — a private cover could otherwise become the PUBLIC card image (`object.cached_main_image_url` feeds the explorer cards/map); the migration recomputes any cache the new filter invalidates (0 live). **NULL ≈ public for media** (matches `get_object_resource`/`get_media_for_web` and the 4014 NULL live rows = the public galleries; deliberately unlike `object_description` where NULL is extended-only). Live magnitude at gate time: 0 private/partners rows ⇒ forward-looking (8v/8w precedent), closed BEFORE the §05 visibility select puts real private rows in the table. After step 4 (rls_policies) + step 6 (`current_user_extended_object_ids`). Folded into `rls_policies.sql` + `schema_unified.sql` + `maintenance.sql` ⇒ idempotent no-op on fresh. Live-applied as MCP migration `media_visibility_gate`. Covered by `tests/test_media_visibility_gate.sql`.

14b. `migration_seed_drift_fix_legaltype_weekday.sql` — **§68 ref-seeding audit live↔source seed-drift reconcile** (post-seed fixup; **no-op on a fresh DB**): (1) inserts `ref_legal_type('raison_sociale', …)` — canonical in `schema_unified.sql` (17 codes) but missing on live (16); idempotent `ON CONFLICT (code) DO NOTHING` (an `object_legal` row keyed to it would FK-fail on live). (2) backfills `ref_code.dow_number` (ISO Mon=1..Sun=7) for `domain='weekday'`, which was NULL on live **and** on fresh because the `schema_unified.sql` backfill UPDATE runs *before* `seeds_data.sql` inserts the weekday rows (mis-ordered) — `seeds_data.sql` is fixed to set `dow_number` at insert time, so this UPDATE only touches pre-existing rows. After step 11 (`seeds_data.sql`) + step 1 (`ref_legal_type`). Live-applied as MCP migration `seed_drift_fix_legaltype_weekday_dow` (verified: legal_type 16→17, weekday dow_number 7 NULL→0). Idempotent.

14c. `migration_room_type_bed.sql` — **§72 structured bed list (Phase 2 — room descriptive)** (folded into `schema_unified.sql` / `rls_policies.sql` / `seeds_data.sql` ⇒ **no-op on a fresh DB**): adds (1) the `bed_type` FK-target `ref_code` partition (`ref_code_bed_type` + `id`/`code` uniques + the house RLS pair — mirrors `ref_code_room_type`; on fresh the dynamic partition loop in `rls_policies.sql` also covers it), (2) ~10 seeded `bed_type` codes + en/es i18n (deduped by the code-unique), and (3) `object_room_type_bed` (room → bed type + `quantity`/`position`, composite PK — sibling of `object_room_type_amenity`) with the §38 split read gate + the per-command canonical write triple (outer columns table-qualified per §55; NEVER `FOR ALL`). The editor loads/saves it via direct PostgREST like the amenity/media room links. After step 1 (`schema_unified.sql`), step 6 (`rls_policies.sql` — `current_user_extended_object_ids` / `user_can_write_object_canonical`), step 11 (`seeds_data.sql`). Live-applied as MCP migration `room_type_bed_structured` (verified: 10 codes + i18n, 4 policies = 1 read + 3 per-command, 0 FOR ALL, RLS on; security advisor clean). Idempotent. Covered by `tests/test_room_type_bed.sql`.

14d. `migration_classification_labels_expansion.sql` — **§71 §08 catalogue expansion** (folded into `seeds_data.sql` ⇒ **no-op on a fresh DB**): seeds the classements/labels the OTI was missing in the §08 « Classifications & distinctions » editor — **5 official Atout France classements** (`residence_tourisme_stars`, `village_vacances_stars`, `auberge_collective_stars`, `prl_stars` @ 1–5★ ; `ot_category` @ cat I/II/III) + **7 quality labels** (`monument_historique` classé/inscrit, `musee_de_france`, `jardin_remarquable`, `maison_des_illustres`, `accueil_velo`, `tables_auberges` 7 cat., `logis` multiple cheminées/cocottes) + a `charme` value on the existing `qualite_tourisme_reunion` (« QTIR de Charme »). **National « Qualité Tourisme™ » is NOT re-seeded** — it already exists as `LBL_QUALITE_TOURISME` (and its successor `LBL_DESTINATION_EXCELLENCE`), made §08-editable by §71 E. All `is_distinction=TRUE`, `display_group ∈ {official_classification, quality_label}` ⇒ surfaced by the §08 catalog-driven picker automatically; §10 (accessibility) untouched, §11 sustainability *labels* now §08-editable per §71 E. After step 11 (`seeds_data.sql`). i18n FR-only (audit §68 deferral). Live-applied as MCP migration `classification_labels_expansion` (then §71 E removed the `qualite_tourisme` duplicate; live now: 10 classements + 13 labels qualité + 9 labels durabilité editable in §08). Idempotent. Covered by `tests/test_classification_labels_expansion.sql` (asserts 7 quality labels).

14f. `migration_amenity_popularity_order.sql` — **§73 amenity « industry popularity » order**: seeds `ref_amenity.position` (global rank by `COUNT(object_amenity)` desc) and `ref_code_amenity_family.position` (family rank by total usage) so the §06 room **equipment picker** can group amenities by family (collapsible) and order them most-common-first instead of alphabetically. Consumed only by the room picker (`buildRoomAmenityGroups` respects `position`); §10/§05 keep their alphabetical `buildAmenityGroups`, so they are visually unchanged. **Data fixup, idempotent** (recomputes the full ranking each run), **reversible** (`SET position = 0`). **USAGE-DERIVED ⇒ a fresh DB (no objects/usage) ranks deterministically by id, not by usage** — a cosmetic ordering divergence vs live, not a schema one (the fresh-apply gate does not assert position values). After step 11 (`seeds_data.sql`) + any amenity import. Live-applied as MCP migration `amenity_popularity_order` (verified: `parking` → position 1, family `outdoor` → 1). No PostgREST reload (data only).

14g. `migration_amenity_room_scope.sql` — **§75 room-relevant amenity scope**: sets `ref_amenity.scope = 'both'` for ~30 room-relevant amenity codes (Wi-Fi, TV, clim, kitchenette/fridge/microwave, douche, sèche-cheveux, linge, coffre-fort, balcon…) so the §06 room equipment picker — which filters `scope IN ('room','both')` — shows **only** room-relevant amenities and hides the establishment-level ones (Bar, Restaurant, Parking, the 43 accessibility items, sports…). `'both'` (not `'room'`) is deliberate so they stay in the §10/§05 OBJECT picker (`scope IN ('object','both')`) ⇒ **no existing `object_amenity` selection is orphaned** (towels/shower/kitchenette have 233/229/272 object-level uses). The other 106 amenities stay `'object'`. **Data fixup, idempotent, reversible** (`SET scope='object'`); **code-list-derived ⇒ deterministic, so fresh == live** (unlike the §73 usage order). After step 11 (`seeds_data.sql`). Live-applied as MCP migration `amenity_room_scope` (verified: room picker 136 → 30 amenities, 0 object-level leaks). No PostgREST reload (data only).

14e. `documents_bucket.sql` — **§71 C justificatif storage**: creates the public `documents` bucket (mirrors `media_bucket.sql`) for §08 classification/label justificatifs — PDF (`application/pdf`, stored as-is) + scanned images (`image/jpeg`, re-encoded + EXIF-stripped by the route's shared image pipeline), 10 MB cap. Write access restricted to `service_role` (RESTRICTIVE `documents_no_anon_write` ANDs with the media one — both buckets closed to anon/authenticated direct writes); public read. All writes go through `/api/document/upload`, which authorizes the caller (`user_can_write_object_canonical`) then creates the `ref_document` row (`object_classification.document_id`). Idempotent. Live-applied as MCP migration `documents_bucket`. Order-independent (a storage bucket has no schema deps).

A1. `avatars_bucket.sql` — **User profile pictures**: creates the public `avatars` bucket for the "Mon compte → Profil" avatar upload — images only (`image/jpeg`/`png`/`webp`, 5 MB cap; re-encoded ≤ 512 px + EXIF/GPS-stripped by the shared `processImage` pipeline). Write access restricted to `service_role` (RESTRICTIVE `avatars_no_anon_write` ANDs with the media/documents ones — anon/authenticated writes require `bucket_id NOT IN ('media','avatars')`); public read. All writes go through `/api/avatar/upload`, which authorizes **as the caller** (self only — path `{user_id}/avatar.jpg` derived server-side from the JWT `user.id`, never the request body) then persists `app_user_profile.avatar_url` (self-update RLS `id = auth.uid()`; cache-busted via a `?v=` query param). Same single-writer + metadata-strip invariant as media (CLAUDE.md §59) — a user's selfie can carry GPS EXIF. Idempotent. Live-applied as MCP migration `avatars_bucket`. Order-independent (a storage bucket has no schema deps).

A2. `branding_assets_bucket.sql` — **White-label brand logo**: creates the public `branding-assets` bucket for the Settings → branding logo upload — images only (`image/jpeg`/`png`/`webp`, 5 MB cap; re-encoded ≤ 1024 px + EXIF/GPS-stripped by the shared `processImage` pipeline with `outputFormat: 'preserve'` so PNG/WebP **transparency survives** — a logo must not be flattened onto an opaque JPEG). Write access restricted to `service_role` (RESTRICTIVE `branding_assets_no_anon_write` ANDs with the media/avatars/documents ones — anon/authenticated writes require `bucket_id NOT IN ('media','avatars','documents','branding-assets')`); public read (the login page loads the logo pre-auth via `api.get_public_branding` → `logo_public_url`). All writes go through `/api/branding/logo/upload`, which authorizes **as the caller** via `api.is_platform_admin()` — the SAME predicate that gates `api.upsert_app_branding` — then uploads service-role and returns `{logoStoragePath, logoPublicUrl, logoMimeType}` for `saveBrandingSettings` to pass to `upsert_app_branding` (the route does NOT write the branding row). This replaces the OLD off-pattern direct browser-client upload in `branding.ts` (which failed "Bucket not found", and would have failed RLS even with the bucket created — the browser client has no permissive write policy on any bucket). Same single-writer invariant as media/avatars (CLAUDE.md §59). Idempotent. Live-applied 2026-07-03 (bucket + 2 policies). Order-independent (a storage bucket has no schema deps; the admin gate lives in the route, not the bucket policy). **NB** the `ui_whitelabel_branding.sql` section 8 commented-out bucket block is superseded by this file (which adds the RESTRICTIVE deny + service-role write the inline block lacked).

14h. `migration_tags_create_and_order.sql` — **§09 « Tags & étiquettes » editor redesign — backend** (folded into `api_views_functions.sql` ⇒ **no-op on a fresh DB**): adds (1) `api.create_tag(p_anchor_object_id, p_name, p_color)` — dedup-guarded GLOBAL tag creation (`SECURITY DEFINER`, gated per-object by `internal.workspace_assert_can_write_object` = `user_can_write_object_canonical`; `ref_tag` is admin-write RLS so editor-facing creation MUST be a DEFINER fn). Dedup on `ref_tag.name_normalized` (STORED generated col, NOT `ON CONFLICT(slug)` — slug UNIQUE is case-sensitive + `idx_ref_tag_slug_ci` is non-unique), insert-or-return-existing; slug derived inline (no `api.slugify`); `gen_random_uuid()`; `created_by` set. (2) `api.set_tag_color(p_anchor_object_id, p_tag_id, p_color)` — sets a tag's GLOBAL color, same gate. **Color is a HEX `#rrggbb`** (100% of live `ref_tag.color` data is hex — the named-variant set was frontend-fallback fiction; verified 16/16 hex); both RPCs validate `^#[0-9a-f]{6}$`, create defaults to `#64748b`. (3) `api.get_object_tags_compact` re-ordered by **`tag_link.position`** (per-object §09 priority) instead of global `ref_tag.position`. ⚠ **THREE-SITE order change (deploy-integrity):** the tag aggregate is inlined as a `tags` CTE in three live sites — this fn, the `get_object_cards_batch` baseline (`api_views_functions.sql`), and its **SECURITY DEFINER override** at manifest **8j** (`migration_cards_batch_authorize_definer.sql`, the LIVE body serving the Explorer grid). 8j's file was **edited in place** (same `tl.position` change) and **re-applied** to live alongside this migration. A 4th site, `api.get_object_resource`, was deployed separately: its structured-beds change (commit ecf6ed8) went live in the §72 pass, and its tag-order line was deployed **2026-06-15** (MCP migration `get_object_resource_tag_order` — guarded surgical replace; verified `tl.position` present + beds intact). All 4 sites now order tags by `tag_link.position`. The `0029_authenticated_security_definer_function_executable` advisor on the two RPCs is expected (§36 class). After step 1 (`schema_unified.sql`), step 4 (`migration_permission_write_paths.sql` — `workspace_assert_can_write_object`/`user_can_write_object_canonical`), and the api functions file. Live-applied as MCP migrations `tags_create_and_order` + `tags_color_hex` (RPC hex fix) + `cards_batch_tag_order` (8j override re-apply). Idempotent (`CREATE OR REPLACE`). Covered by `tests/test_create_tag.sql`, `tests/test_set_tag_color.sql`, `tests/test_object_tags_compact.sql`. Decision log §76.

14i. `migration_opening_period_type.sql` — **§81 explicit, admin-extensible opening-period type** (the §14 editor «périodes d'ouverture» now requires picking a type instead of inferring it from the label): adds (1) the `opening_period_type` FK-target `ref_code` partition (`ref_code_opening_period_type` + `id`/`code` uniques + the house RLS pair — mirrors `ref_code_opening_schedule_type`), (2) **4 seeded types** with a ribbon colour + `metadata.all_year` flag in metadata (`high_season` teal, `mid_season` amber, `off_season` slate, `year_round` green `{all_year:true}` — admin can add more), (3) `opening_period.period_type_id` **nullable** FK (the 191 legacy periods stay valid → the UI falls back to label-inference until they are edited, which then requires a type), and `CREATE OR REPLACE` of (4) `api.save_object_openings` (resolves `period_type_code → id`, mirrors the schedule-type lookup) + (5) `api.build_opening_period_json` (emits `period_type_code` + `all_years` so the editor round-trips). The frontend reads the catalog via a direct `ref_code` select (domain filter, like §41 zones). The selected **type drives the date UI**: an `all_year` type means "no dates", others are dated. After step 1 (`schema_unified.sql` — `ref_code` parent + `opening_period`), the api functions file (`api_views_functions.sql`) + `object_workspace_safe_write_rpcs.sql` (the `CREATE OR REPLACE` overrides their bodies), and step 11 (`seeds_data.sql`). Validated transactionally (apply → `RAISE` → rollback) then live-applied as MCP migration `opening_period_type` (verified: 4 seeds + colours, FK column + enforcement, `build_opening_period_json` emits `period_type_code`, anon read / write-deny; security advisor clean). Idempotent. Covered by `tests/test_opening_period_type.sql`. Decision log §81.

14j. `migration_gdpr_erasure.sql` — **Art. 17 effacement / anonymisation** (plan `docs/conformite-rgpd/PLAN_effacement_art17.md`, décisions D1–D4). Ajoute (1) `gdpr_erasure_log` (registre de preuve ; RLS lecture superuser, écriture DEFINER seulement), (2) `audit.redact_subject(table,key,val,cols[])` — retire les clés PII de `audit.audit_log.before_data/after_data` en matchant `row_pk` OU le contenu `before_data` (capture les lignes DELETE dont la PK ne porte pas la FK), (3) `api.rpc_gdpr_erase_subject(kind,id,mode,reason)` `SECURITY DEFINER`, garde `api.is_platform_superuser()` avec contournement « connexion privilégiée sans JWT » (migration/test/psql). Proportionné (agit PAR SUJET, ne balaie jamais le référentiel public) : kinds actor (Tier A : identité+canaux+consentements+CRM) / incident / review / object_legal / contact_channel / user. Anonymisation par défaut (tombstone, FK préservées) ; `delete` = cascade dure. Retourne les URLs Storage à supprimer côté serveur (média = string sans FK). After step 1 (infra audit + tables PII) + step 4 (`api.is_platform_superuser`). **Live-applied 2026-06-16 as MCP migration `gdpr_erasure`** (vérifié au préalable en `BEGIN…(DDL+test)…ROLLBACK` = `VERIFICATION PASSED` ; post-apply : 3 objets présents, RLS on `gdpr_erasure_log`, advisor sécurité = uniquement le `0029_*_security_definer_function_executable` attendu sur le RPC — classe §36). **Folded into `schema_unified.sql`** (table + `ENABLE RLS` + grants + the 2 functions, placed AFTER the file's `SELECT audit.attach_missing_triggers()` call ON PURPOSE so `gdpr_erasure_log` is NOT auto-audited = matches live) + **`rls_policies.sql`** (the `gdpr_erasure_log_admin_read` SELECT policy — needs `api.is_platform_superuser`, defined in that file) ⇒ **fresh==live**, idempotent no-op on a fresh build. The Storage strip + `auth.users` delete live in `src/app/api/rgpd/erase/route.ts`. Idempotent (`CREATE TABLE IF NOT EXISTS` + `CREATE OR REPLACE`). Couvert par `tests/test_gdpr_erasure.sql`. Fold verified (transient parse-check on live: `FOLD OK`, policy resolves).

14j. `migration_pricing_vocabulary.sql` — **§83 §13 « Tarifs & extras » two-axis vocabulary** (the §13 editor now distinguishes the PUBLIC from the TYPE of tariff): adds (1) the `price_type` `ref_code` partition (`ref_code_price_type` + `id`/`code` uniques + the house RLS pair — mirrors every other ref_code partition; the parent's `(domain,code)`/`(domain,parent_id)` indexes propagate automatically), (2) **8 seeded types** (`principal`/`option`/`menu`/`pack`/`abonnement`/`taxe`/`caution`/`devis`) stored in the already-wired but unused **`object_price.indication_code` text column — NO new column, NO `save_object_commercial` change** (the saver already persists `indication_code`); a `COMMENT ON COLUMN` documents the repurpose, (3) **price_unit +10** (par chambre / logement entier / personne·jour / véhicule / entrée·billet / séance / couvert / an / unité / trajet → 20 total) and **price_kind +5** audiences (bébé / jeune·junior / scolaire / abonné / demandeur d'emploi → 15 total), (4) **`position` set on all three domains** (previously all NULL → unordered dropdowns). The editor reads the three catalogs via direct `ref_code` selects (domain filter, like §41 zones / §81 opening types); the frontend §13 became modal-driven (PricingLineEditModal / DiscountEditModal + compact lists) and dropped the write-trap «Politique & règles» block. **0 `object_price` rows live ⇒ greenfield, no data migration.** After step 1 (`schema_unified.sql` — `ref_code` parent + the partition/index fold) and step 11 (`seeds_data.sql` — the seed rows + positions). Live-applied as MCP migration `pricing_vocabulary_price_type` (verified: 3 domains 8/20/15 rows, all positioned + ordered, anon read of `price_type` OK). Idempotent (`CREATE TABLE/INDEX IF NOT EXISTS`, `WHERE NOT EXISTS` seeds, `IS DISTINCT FROM` position update). Frontend covered by `pricing-row.test.ts`, `widgets/PricingLineEditModal.test.tsx`, `widgets/DiscountEditModal.test.tsx`, `SectionPricing.test.tsx`. Decision log §84.

14k. `migration_object_stay_policy.sql` — **§85 accommodation stay policy (heure d'arrivée / départ)**: adds `object_stay_policy` (per-object: `check_in_from`/`check_in_until`/`check_out_until` time + `conditions` text), a direct sibling of `object_pet_policy`/`object_group_policy`. For lodging (HEB) a whole-rental has a check-in window + check-out deadline, not weekly desk hours — so this is surfaced in **§06** next to the group/pet policies (the §64 HEB home); §14's weekly opening grid is KEPT for everyone (some places still refuse arrivals on certain days). Mirrors `object_pet_policy` exactly: §38 split read gate + per-command `canonical_ins/upd/del` write family (NEVER FOR ALL), `update_..._updated_at` + `trg_audit_...` triggers, house DML grants (RLS-gated). Read + write go through **direct PostgREST** (the §40/§41 child-table precedent: loader direct-selects `object_stay_policy`, saver direct upsert/delete gated by the canonical RLS) ⇒ **NO change to `save_object_commercial` or `get_object_resource`**. After step 1 (`schema_unified.sql` — `object`) + step 6 (`rls_policies.sql` — `current_user_extended_object_ids`/`user_can_write_object_canonical`) + the audit infra (`audit.log_row_changes`). Validated transactionally (apply → `RAISE` → rollback: 4 policies, 2 triggers, FK enforced, anon reads only the published parent / write denied) then live-applied as MCP migration `object_stay_policy`. Idempotent (`CREATE TABLE/POLICY/TRIGGER IF NOT EXISTS`/DROP-then-CREATE). Covered by `tests/test_object_stay_policy.sql`. Frontend: `stayPolicy` on the capacity-policies module + the §06 « Arrivée & départ » `StayPolicyButton`. Decision log §85.

14l. `migration_actor_address_kind.sql` — **§19 prestataire postal addresses** (folded into `seeds_data.sql` ⇒ **no-op on a fresh DB**): seeds an `'address'` `contact_kind` so a prestataire (actor) can hold one or more POSTAL ADDRESSES as `actor_channel` rows, authored from the §19 "Suivi prestataire" fiche (`CrmActorEditModal` "Adresses" section) through the existing `api.save_actor_channel` / `api.delete_actor_channel` RPCs — **no new table**, addresses reuse the channel CRUD + RLS. `ref_code_contact_kind` is a PARTITION OF `ref_code` FOR VALUES IN (`'contact_kind'`), so the INSERT routes to the partition and satisfies `actor_channel.kind_id`'s FK. After step 11 (`seeds_data.sql`). Live-applied as MCP migration `actor_address_contact_kind` (verified: row present in `ref_code_contact_kind`). EN/ES i18n deferred (FR-only; joins the open i18n backlog). Idempotent (`ON CONFLICT DO NOTHING`). Decision log §89.

14m. `migration_object_web_channel.sql` — **§90 object-scoped online presence (réseaux sociaux + distribution OTA)**: adds `object_web_channel` (object → a `social_network` OR `distribution_channel` `ref_code` kind + URL `value` + `is_public` field-level flag + `position`). **Single table for BOTH ref_code domains** because a single FK cannot target two partitions and `ref_code_distribution_channel` doesn't exist (those rows live in the `ref_code_other` DEFAULT partition): the table carries `(kind_id, kind_domain)` with a **COMPOSITE FK `(kind_id, kind_domain) → ref_code(id, domain)`** (the partitioned PARENT's PK; PG12+) + a `CHECK kind_domain IN ('social_network','distribution_channel')` — no partition surgery. RLS mirrors `contact_channel`: §49 split read gate (the `is_public` flag COMPOSES inside the published arm, never substitutes; outer columns table-qualified per the silent-rebinding gotcha) + per-command canonical write triple (`canonical_ins/upd/del_object_web_channel`, predicate `api.user_can_write_object_canonical`; **NEVER FOR ALL**), `update_..._updated_at` + `trg_audit_...` triggers, house DML grants (RLS-gated). Read goes through **`api.get_object_resource`** (new `web_channels` key, same read gate — folded into `api_views_functions.sql`, deployed live by surgical `pg_get_functiondef` replace) so the PUBLIC drawer renders it; the editor §03 load/save use **direct PostgREST** (the §40/§41 child-table precedent). **Retires the §20 "Distribution & réseaux sociaux" editor section** — it projected the OPERATOR actor's `actor_channel` (which can hold neither domain ⇒ structurally always empty). After step 1 (`schema_unified.sql` — `object` + `ref_code`), step 6 (`rls_policies.sql` — `current_user_extended_object_ids`/`user_can_write_object_canonical`), and the api functions file (`api_views_functions.sql`). Validated transactionally (apply → assertions → ROLLBACK = `VERIFICATION PASSED`) then live-applied as MCP migrations `object_web_channel` + `get_object_resource_web_channels` (verified: 4 policies = 1 read + 3 per-command, 0 FOR ALL, 2 triggers, composite FK accepts both domains + rejects mismatch, read path emits `web_channels`; security advisor clean). Idempotent. Covered by `tests/test_object_web_channel_read_gate.sql`. Decision log §90. **Fold into `schema_unified.sql`/`rls_policies.sql`/`migration_write_policy_percommand.sql` deferred** (incremental layer, like 14k `object_stay_policy`).

14o. `migration_opening_period_recurrence.sql` — **§14 opening periods: explicit recurrence + priority cascade** (14n is the §17 membership migration — §91). Replaces the editor's broken date model (a "Haute saison" set to dated `all_years=FALSE` never recurred; the `allYears` TS flag was conflated with "no dates"; the "Période (cycle)" bucket dropdown was a mislabeled cosmetic). Adds (1) `opening_period.is_closure BOOLEAN NOT NULL DEFAULT FALSE` (+ partial index `idx_opening_period_is_closure`) — the **closure layer**; (2) `api.opening_period_rank(is_closure,all_years,date_start,date_end)` → 4 closure / 3 fixed / 2 cyclic / 1 base, and `api.opening_period_width(...)` (narrower window wins at equal rank); (3) `api._covered_days` + `api.periods_partial_overlap` + `api.assert_no_period_overlap(jsonb)` — the **anti-overlap guard** (same-layer partial cross rejected `ERRCODE 23514`, **nesting tolerated**, closures excluded; SQL mirror of the pure TS `periodsPartialOverlap`); `CREATE OR REPLACE` of (4) `api.refresh_open_status` — was an OR of all active periods, now picks the **most-specific active period per object** (DISTINCT ON rank DESC, width ASC) and an active closure **forces closed** (stays set-based; §37 timezone perf preserved); (5) `api.save_object_openings` — `PERFORM api.assert_no_period_overlap` + persists `is_closure`; (6) `api.build_opening_period_json` — emits `is_closure`. Recurrence is DERIVED from the existing triple: base=`all_years=TRUE`+no dates, **cyclic**=`all_years=TRUE`+dates in **sentinel year 2000** (wrap déc→fév → end in 2001, satisfies the existing `CHECK date_end>=date_start`), fixed=`all_years=FALSE`+dates. (7) **Retires the `year_round` opening_period_type** (redundant with the "Toute l'année" recurrence mode; `DELETE … WHERE NOT EXISTS(period_type_id ref)` — 0 typed live ⇒ safe). The 187 live periods are all base ⇒ **0 backfill, behaviour preserved**. After step 1 (`schema_unified.sql` — `opening_period`, `refresh_open_status`, `is_opening_period_active_on_date`), the api functions file (`api_views_functions.sql` — `build_opening_period_json`), `object_workspace_safe_write_rpcs.sql` (`save_object_openings`), and **after 14i** (`migration_opening_period_type.sql`, which this `CREATE OR REPLACE`-overrides for the two RPCs). Live-applied 2026-06-17 as MCP migration `opening_period_recurrence` (+ a follow-up `execute_sql` for `save_object_openings`); verified transactionally on live (pure-fn assertions + engine base-open/closure-forces-closed + build_json `is_closure`, all rolled back; `refresh_open_status` re-run = 45/362 open, 187 periods intact, 3 period types). Idempotent (`ADD COLUMN/CREATE INDEX IF NOT EXISTS`, `CREATE OR REPLACE`, guarded `DELETE`). Covered by `tests/test_opening_recurrence.sql`. Spec `docs/specs/2026-06-17-opening-periods-recurrence-design.md`, plan `docs/plans/2026-06-17-opening-periods-recurrence-plan.md`. Decision log §92. **Fold into `schema_unified.sql`/`api_views_functions.sql`/`object_workspace_safe_write_rpcs.sql` deferred** (incremental layer, like 14k/14m).
14p. `migration_opening_open_without_hours.sql` — **§14 « ouvrir un jour sans horaires » (par jour, persisté — §93)** : `CREATE OR REPLACE api.get_opening_slots_by_day` n'émet plus QUE les jours OUVERTS (un weekday rattaché à un `opening_time_period` non fermé) ; un jour ouvert SANS `opening_time_frame` ressort `[]` = **ouvert sans horaire précis** (hôtel/location : arrivée/départ libres) ; les jours fermés/absents sont omis. **Byte-équivalent** sur les données existantes (les 254 time_periods live ont tous des frames ; 0 ouvert-sans-frame, 0 `closed=TRUE` ; vérifié 0 écart `old∖[] == new` sur 187 périodes) ⇒ le nouveau cas n'apparaît qu'avec le front. **AUCUN changement `save_object_openings`** (il insère déjà la ligne `opening_time_period`+`opening_time_period_weekday` même avec `time_frames` vide). Front : `buildOpeningsPayload` ne sérialise QUE les jours ouverts (jour fermé = omis ; jour ouvert sans heures = `closed:false` + `time_frames:[]`), `buildWorkspaceOpeningWeekdaysFromCanonical` conserve les `[]` (1 slot vide au lieu de jeter le jour), `periodDayHours`/§14 affichent « Ouvert · sans horaire » au lieu de « Fermé ». **DÉPLOYER CE SQL AVANT le front** (ancien front + ce SQL = no-op sur l'existant ; nouveau front + ANCIEN SQL = tous les jours sans frame vus comme ouverts). After the api functions file (`api_views_functions.sql`). Live-applied 2026-06-17 (MCP migration `opening_slots_open_without_hours`) ; vérifié transactionnellement (jour ouvert-sans-frame ⇒ `[]`, jour fermé omis, jour à heures conservé ; ROLLBACK). Idempotent (`CREATE OR REPLACE`). Couvert par `tests/test_open_without_hours.sql`. Decision log §93. **Folded into `api_views_functions.sql`.**

14q. `migration_object_external_id_writes.sql` — **§22 « Identifiants externes » CTA fonctionnel (tranche A — folded into `api_views_functions.sql` ⇒ no-op on a fresh DB)**: adds the two admin-gated write RPCs the table never had (`object_external_id` shipped `admin_*` per-command RLS + 0 write RPC). (1) `api.rpc_upsert_object_external_id(p_object_id, p_source_system, p_external_id, p_last_synced_at)` — `SECURITY DEFINER` (bypasses the table's `admin_*` RLS ⇒ the in-function gate IS the boundary), gate = `api.is_platform_superuser() OR api.current_user_admin_role_code() IS NOT NULL` else `RAISE FORBIDDEN`; `organization_object_id := api.current_user_org_id()` (the client NEVER chooses the org); rejects canonical sources (`upper(source_system) IN ('OTI','SU')` OR `lower(source_system) LIKE '%canonical%'` ⇒ `CANONICAL_SOURCE`); `INSERT … ON CONFLICT (object_id, organization_object_id, source_system) DO UPDATE` (respects `uq_object_external_id_object_org_source`); `gen_random_uuid()`; `SET search_path = public, api, internal`. (2) `api.rpc_delete_object_external_id(p_id)` — same admin gate; the row must belong to `current_user_org_id()` (platform superuser may delete any non-canonical row) and must not be canonical. (3) `api.current_user_is_org_admin()` — tiny boolean helper mirroring the gate, consumed by the front to drive `permissions.syncIdentifiers.canDirectWrite`. All three `REVOKE … FROM PUBLIC, anon` + `GRANT … TO authenticated, service_role`. The `0028/0029_*_security_definer_function_executable` advisor on the RPCs is **expected** (§36 class — public-executable DEFINER that self-authorizes). After step 1 (`schema_unified.sql` — `object_external_id` + `uq_object_external_id_object_org_source`) and step 6 (`rls_policies.sql` — `api.is_platform_superuser` / `api.current_user_admin_role_code` / `api.current_user_org_id`). Idempotent (`CREATE OR REPLACE`). Covered by `tests/test_object_external_id_writes.sql`. Decision log §A1.

14r. `migration_object_version_read_restore.sql` — **§3.C éditeur « Versions / historique » (tranche C — folded into `api_views_functions.sql` ⇒ no-op on a fresh DB)** : 3 RPC `SECURITY DEFINER` sur la table `object_version` existante (snapshot JSONB append-only capté par `save_object_version`). (1) `api.get_object_versions(p_object_id, p_limit, p_offset)` — authorize-once (§36 : `p_object_id IN api.current_user_readable_object_ids()` sinon `RAISE 42501`) ; auteur résolu via `app_user_profile.display_name` ; `changed_fields` = clés du `data` qui diffèrent de la version précédente (`LAG(data) OVER version_number`) MOINS la liste cache/`updated_at`/`current_version`/`updated_by`/identité/colonnes générées (la même que `save_object_version` ignore, étendue aux clés identité/audit/`name_normalized`/`name_search_vector` — **byte-identique à `DIFF_IGNORE_KEYS` dans `bertel-tourism-ui/src/services/object-versions.ts`, à garder en lockstep**). (2) `api.get_object_version_snapshot(p_object_id, p_version_number)` — même authorize-once ; renvoie le `data` jsonb. (3) `api.rpc_restore_object_version(p_object_id, p_version_number)` — gate `api.user_can_write_object_canonical` ; `UPDATE object` sur les **colonnes canoniques inscriptibles uniquement** (liste explicite : object_type/name/business_timezone/commercial_visibility/region_code/updated_at_source/secondary_types/extra/name_i18n) ; EXCLUT `id`, `current_version`, `created_at/by`, `updated_at`, `is_editing`, tous les `cached_*`, les colonnes générées (`name_normalized`/`name_search_vector`), et **`status`** (le changement de statut passe par `rpc_set_object_status`/`rpc_publish_object` ; `trg_guard_object_status_change` est `BEFORE UPDATE OF status` ⇒ ne pas lister `status` l'évite). Le trigger `save_object_version` capte une **nouvelle** version pour cet UPDATE (append-only ; restaurer = nouvelle version, pas de réécriture du passé). Grants : `REVOKE … FROM PUBLIC, anon` puis `GRANT … TO authenticated, service_role` ; l'advisor `0028/0029_*_security_definer_function_executable` sur les 3 RPC est **attendu** (§36). After step 1 (`schema_unified.sql` — `object`/`object_version`/`save_object_version`/`app_user_profile`), `migration_cards_batch_authorize_definer.sql` (`api.current_user_readable_object_ids`), `migration_permission_write_paths.sql` (`api.user_can_write_object_canonical`), et la api functions file (`api_views_functions.sql`). Idempotent (`CREATE OR REPLACE` + grants gardés). Couvert par `tests/test_object_version_read_restore.sql`. **Folded into `api_views_functions.sql`.** Decision log §C.

14s. `migration_metric_snapshot.sql` — **Brique 2 registre temporel (table)** : `public.metric_snapshot`
(long-format `(snapshot_date,scope,scope_key,metric_key,value,denominator)`, unique de désambiguïsation,
RLS ON + read authenticated, écritures DEFINER/service_role). Foldé dans `schema_unified.sql`. After step 1.
Idempotent. Couvert par `tests/test_metric_snapshot.sql`. Spec
`docs/superpowers/specs/2026-06-18-dashboard-timeseries-observatory-design.md`.

14t. `migration_object_cuisine_type.sql` — **§06 Restaurant P1 : cuisine NIVEAU-OBJET** (folded into `schema_unified.sql` (table+index) + `rls_policies.sql` (RLS+grants) ⇒ **no-op on a fresh DB**). Nouvelle table descriptor-link `object_cuisine_type(object_id TEXT, cuisine_type_id UUID, position INT, PK(object_id,cuisine_type_id))` — « cuisines proposées » devient un attribut du restaurant (mirror `object_amenity`), découplé de `object_menu_item` : un restaurant déclare sa/ses cuisine(s) **sans menu** ; `position 1 = principale`. Supprime le write-trap (`menus.items[0].items[0]`, 0 menu live ⇒ sélection silencieusement jetée pour 100 % des restaurants). RLS = §38 split read gate (`published OR current_user_extended_object_ids`) + famille per-command `canonical_ins/upd/del_object_cuisine_type` (`api.user_can_write_object_canonical`, colonne externe qualifiée ; NO FOR ALL) ; `GRANT SELECT … anon` (gotcha P0.3 : anon a déjà EXECUTE sur le prédicat). `api.get_object_resource.cuisine_types` (bloc RES + FMA `associated_restaurants_cuisine_types`) et `api.search_restaurants_by_cuisine`/`search_events_by_restaurant_cuisine` repointés sur `object_cuisine_type` (folded into `api_views_functions.sql`). Catalogue `ref_code` `cuisine_type` : `metropolitan` renommé « Française » + 14 nouveaux types (folded into `seeds_data.sql`). 0 donnée à migrer. Live-applied (MCP `object_cuisine_type_p1`, vérifié : RLS on, 4 policies per-command, read gate §38, round-trip insert/delete OK). Couvert par `tests/test_object_cuisine_type.sql`. After step 1 (`schema_unified.sql`), `migration_permission_write_paths.sql` (`user_can_write_object_canonical`), `migration_cards_batch_authorize_definer.sql` (`current_user_extended_object_ids`), et `api_views_functions.sql`. Decision log §P1 (§06).

14u. `migration_object_document.sql` — **§06 Restaurant P3 : CARTE PDF** (folded into `schema_unified.sql` (table+index) + `rls_policies.sql` (RLS+grants) + `seeds_data.sql` (`document_type`) ⇒ **no-op on a fresh DB**). Lien générique `object_document(object_id TEXT, document_id UUID→ref_document, role_id UUID→ref_code_document_type, title TEXT, valid_from/valid_to DATE, position INT, PK(object_id,document_id))` — attache une/des carte(s) PDF **au restaurant** (distinct des menus structurés et de la cuisine globale). **Titre + validité portés par le LIEN** (object_document = canonical-write ; `ref_document` est admin-write ⇒ l'éditeur ne peut y écrire) ; url depuis `ref_document` (lecture publique `USING(true)`). Seed `ref_code` `document_type` : `carte`/`brochure`/`certificat` (partition était vide). RLS = §38 split read gate + per-command `canonical_ins/upd/del_object_document` (`user_can_write_object_canonical`, colonne externe qualifiée ; NO FOR ALL) ; `GRANT SELECT … anon`. `api.get_object_resource` (RES) gagne la clé **`menu_documents`** (`object_document`→`ref_document` filtré role `carte` ; comptée dans `resource_block_pricing` + `resource_block_misc` = invariant compose §101) ⇒ `get_object_with_deep_data` hérite. Upload via `POST /api/document/upload` (service-role, autorise par objet) qui crée `ref_document` puis le client insère le lien. Live-applied (MCP `object_document_p3`, vérifié : table+4 policies, round-trip `menu_documents` url/titre/validité OK). After step 1 (`schema_unified.sql` — `object`/`ref_document`/`ref_code_document_type`), `migration_permission_write_paths.sql`, `migration_cards_batch_authorize_definer.sql`, `api_views_functions.sql`, et `seeds_data.sql`. Decision log §104 (§06 P3).

14v. `migration_menu_item_section.sql` — **§06 Restaurant P2b : carte structurée à 3 niveaux** (folded into `schema_unified.sql` (colonne+index) ⇒ **no-op on a fresh DB**). `ALTER TABLE object_menu_item ADD COLUMN section_id UUID REFERENCES ref_code_menu_category(id)` (+index) ⇒ la **SECTION** (Entrée/Plat/Dessert/Boissons…) passe au niveau du **plat** : `object_menu` devient le **menu** (titre = `name` ; `category_id` = conteneur optionnel, déjà nullable), une « section » UI = les `section_id` distincts d'un menu avec leurs plats. `api.get_object_resource` (menus) émet `section` sur chaque item (foldé `api_views_functions.sql`). 0 menu/0 item live ⇒ remodelage libre. Live-applied (MCP `menu_item_section_p2b`). After step 1 (`schema_unified.sql` — `object_menu_item`/`ref_code_menu_category`) + `api_views_functions.sql`. Decision log §104 (§06 P2b).

14w. `migration_markdown_strip_descriptions.sql` — **Descriptions Markdown servies en clair + structuré (Delivery 1, famille `object_description`)** (la fonction `api.strip_markdown` est **foldée dans `api_views_functions.sql`** juste après `api.i18n_pick_strict`, AVANT ses consommateurs ⇒ ordre fresh-apply portant ; **PAS dans `schema_unified.sql`** — les helpers `api.*` n'y vivent pas, et un corps `LANGUAGE sql` référençant une fonction absente échoue au `CREATE` en 42883). `api.strip_markdown(text)` (`IMMUTABLE STRICT`, schéma `api` pour parité `i18n_pick` ; protège `\*`→`chr(1)` avant l'emphase ; règles bloc ancrées `^` + flags `gn` ; `GRANT … anon/authenticated/service_role`) dérive le texte propre des colonnes `object_description.{description,description_chapo,description_mobile,description_edition,description_adapted}` devenues Markdown-canoniques. Lecteurs **plats** wrappés `strip_markdown` (+ collapse espaces) : `get_object_card`, `get_object_cards_batch` (+ **`migration_cards_batch_authorize_definer.sql`** = forme DEFINER live, à garder en lockstep), `get_object_map_item` (→ `list_objects_map_view`), `export_publication_indesign` (`print_text`, `custom_print_text` gardé tel quel). Voie **riche** : `get_object_resource` (single + `descriptions[]`) et `get_object_resource_adapted` émettent la clé historique = `strip_markdown(i18n_pick(...))` (plat) **+** une clé sœur `*_md` = valeur résolue brute (Markdown) pour les 5 champs ; `get_object_with_deep_data` hérite (embed verbatim) ; les `*_md` transitent par `resource_block_misc` comme leurs frères plats (pas de changement des allow-lists `resource_block_*`). **Aucune colonne ajoutée, pas de migration de données** (gate diff vérifié : 180 lignes flat changent bénignement — pseudo-listes/gras aplatis, notation `3 étoiles ***` préservée ; décision PO : règles ancrées + diff, **pas** d'échappement). 0 changement de signature ⇒ `CREATE OR REPLACE` (le gros `get_object_resource` re-appliqué à live via `.tmp_pgapply/apply_range.cjs`). `NOTIFY pgrst` après application. After step 1 (`schema_unified.sql` — `object_description`/`api.i18n_pick`), `migration_permission_write_paths.sql` (`api.user_can_write_object_canonical`), `migration_cards_batch_authorize_definer.sql` (`api.current_user_extended_object_ids`), et `api_views_functions.sql`. Couvert par `tests/test_strip_markdown.sql` + `tests/test_markdown_descriptions_api.sql`. Frontend (MarkdownEditor variant `inline`, swap §04 Descriptif/Accroche, rendu tiroir `*_md`) hors manifest. Decision log §106 ; spec `docs/superpowers/specs/2026-06-21-markdown-all-description-fields-design.md`. (Type-spécifique rooms/menus/places/iti + GPX/KML + `get_object_room_types` = Delivery 2, plan séparé.)

14x. `migration_object_hard_delete.sql` — **§108 Suppression définitive d'une fiche (admin-only, irréversible)** (**NON foldé dans `schema_unified.sql`** — la policy RLS référence `api.is_platform_superuser`, définie dans `rls_policies.sql` ; folder ici casserait la passe fresh-apply en 42883). (1) `public.object_deletion_log` — journal immuable (`object_id`/`object_name`/`object_type`/`status_at_deletion`/`media_deleted_count`/`document_deleted_count`/`performed_by`/`performed_at`/`report jsonb`, PK `gen_random_uuid()`, **pas de FK vers object** — la ligne survit à la suppression ; RLS ON + read superuser-only `(SELECT api.is_platform_superuser())` ; `REVOKE … anon`, `GRANT SELECT authenticated` / `ALL service_role` ; écrite uniquement par le RPC). (2) `api.rpc_delete_object(p_object_id text, p_confirm_name text) RETURNS jsonb` (`SECURITY DEFINER`, `SET search_path = public, api, auth`) : gardes en cascade `NO_AUTH_CONTEXT` → `FORBIDDEN` (non `api.is_platform_superuser`) → `NOT_FOUND` → `FORBIDDEN_ORG` (`object_type='ORG'` rejeté — établissements only) → `MUST_ARCHIVE_FIRST` (`status<>'archived'`) → `NAME_MISMATCH` (confirmation par nom exact) ; collecte les `media.url` (object-keyed **ET** place-keyed des sous-lieux — `media` est XOR object/place, les deux partent par CASCADE) + les `ref_document.url` **orphelinés** (`ref_document` = catalogue partagé référencé par 6 chemins : lien `object_document` + 4 colonnes object-keyed SET NULL `object_classification`/`object_legal`/`object_sustainability_action`/`object_iti.status_document_id` ; on collecte tout doc référencé par l'objet puis on ne supprime ligne+fichier que si AUCUN autre objet ni `actor_consent` ne le référence — sinon SET-NULL d'un pointeur vivant + effacement d'un fichier en usage) ; journalise ; `DELETE FROM ref_document` (orphelins) ; `DELETE FROM object` (CASCADE enfants) ; retourne `{media_to_delete[], documents_to_delete[], deleted:true}` pour le balayage Storage best-effort côté route (autorisation = garde superuser du RPC, PAS la service key ; cf. route). `gen_random_uuid()` (search_path restreint, jamais `uuid_generate_v4()`). `REVOKE … FROM PUBLIC, anon` + `GRANT EXECUTE … TO authenticated, service_role` ; l'advisor `authenticated_security_definer_function_executable` (WARN) sur le RPC est **attendu** (§36 class — DEFINER auto-autorisant). After step 6 (`rls_policies.sql` — `api.is_platform_superuser`) et step 1 (`schema_unified.sql` — `object`/`media`/`object_document`/`ref_document`). Live-applied 2026-06-22 (MCP `object_hard_delete_108` ; vérifié transactionnellement : 6 gardes + happy path = objet+enfants supprimés, `ref_document` orphelin supprimé, 1 ligne de journal, arrays Storage renvoyés ; ROLLBACK, 0 fuite). Idempotent (`CREATE TABLE IF NOT EXISTS`/`DROP POLICY IF EXISTS`/`CREATE OR REPLACE`). Couvert par `tests/test_object_hard_delete.sql`. Route `/api/objects/delete` (sweep buckets `media` + `documents` en service-role) hors manifest. Decision log §108 ; spec `docs/superpowers/specs/2026-06-22-object-hard-delete-design.md`.

14y. `migration_global_search_document.sql` — **§109 Recherche Explorer globale** (colonne/index/fonction de recalcul/triggers/MV foldés dans `schema_unified.sql` ; les deux fonctions requête foldées dans `api_views_functions.sql` ⇒ **no-op on a fresh DB**). Ajoute `object.search_document tsvector` (+ index GIN, + colonne/index dans `internal.mv_filtered_objects`) : document full-text pondéré **agrégé des tables enfants** (A=nom/ville composés à la requête ; B=taxonomie+classements/labels ; C=équipements+tags+environnement+cuisines+menus+plats+régimes+allergènes ; D=prose description canonique markdown-strippée + descriptions de plats). Maintenu par `api.refresh_object_filter_caches` **étendue** + nouveaux triggers : réutilise les 4 existants (amenity/environment/classification/taxonomy) ; ajoute object_id-direct (object_description, object_menu, object_cuisine_type) + résolus via parent (`trg_refresh_caches_from_object_menu_item` pour object_menu_item ; `trg_refresh_caches_from_menu_item_link` pour object_menu_item_dietary_tag/_allergen ; `trg_refresh_caches_from_tag_link` pour tag_link target_pk). Périmètre **FR canonique** ; menus indexés seulement `is_active` + visibilité public/NULL ; descriptions = ligne canonique (`org_object_id IS NULL`) public/NULL (`description_edition` exclu). `api.get_filtered_object_ids` gagne une colonne de sortie `relevance REAL` (`ts_rank` pondéré ; 0 sans recherche ⇒ ordre legacy préservé) + un bras de filtre `search_document` actif quand `p_filters->>'search_mode' = 'global'` (signature in inchangée ⇒ DROP+CREATE pour la RETURNS, grants ré-appliqués) ; `api.list_object_resources_filtered_page` propage `relevance` et trie `ORDER BY relevance DESC, label_rank, name_normalized, id`. Le mode global est **opt-in** côté front (l'Explorer pose `search_mode='global'` ; les pickers éditeur restent nom-seul ⇒ inchangés). Backfill unique des 843 fiches via `SELECT api.refresh_object_filter_caches(o.id)` puis reconstruction MV. Live-applied 2026-06-22 (MCP `search_document_column_109`/`refresh_caches_search_document_109`/`search_document_triggers_109`/`mv_filtered_objects_search_document_109`/`get_filtered_object_ids_global_search_109`/`list_filtered_page_relevance_order_109` ; vérifié transactionnellement : jacuzzi/plat « Salade de palmiste »/végan/description remontent en global, mode nom NON élargi, menu privé NON remonté, ranking nom>équipement, ROLLBACK). `NOTIFY pgrst` après application. After step 1 (`schema_unified.sql` — object/object_menu/object_cuisine_type/object_description/tag_link/ref_*/mv_filtered_objects), `api.strip_markdown` (folded api_views_functions.sql §106), puis `api_views_functions.sql` (les deux fonctions requête) et `rls_policies.sql`. Couvert par `tests/test_global_search.sql`. Frontend (`searchScope`/`search_mode`, placeholder) hors manifest. Decision log §109 ; spec `docs/superpowers/specs/2026-06-22-global-explorer-search-design.md` ; plan `docs/superpowers/plans/2026-06-22-global-explorer-search.md`.

14z–15d. `migration_markdown_d2_*.sql` — **§112 Markdown « toutes descriptions » Delivery 2 (prose type-spécifique)** (édits **body-only** `CREATE OR REPLACE`, foldés dans `api_views_functions.sql` ⇒ **no-op on a fresh DB** ; ces fichiers `migration_markdown_d2_*.sql` sont des **descriptions de changement**, pas du DDL autonome — le DDL réel vit dans la source canonique). Étend le contrat §106 (strip le plat + émet `*_md`) aux 5 champs prose publics restants : **14z `_direction`** (`object_location.direction`, plain text, 2 sites de `get_object_resource` : adresse principale + sous-lieu) ; **15a `_room`** (`object_room_type.description`+`_i18n`, bloc `room_types` de `get_object_resource` + getter autonome `api.get_object_room_types`) ; **15b `_dish`** (`object_menu_item.description`, bloc `menus.items` + ligne `render.menu_item_lines` + tsvector recherche `doc_d` — **2 copies** : `schema_unified.sql` `api.refresh_object_filter_caches` ET `migration_global_search_document.sql` — + backfill `SELECT api.refresh_object_filter_caches(o.id) FROM object o`) ; **15c `_place`** (`object_place_description` famille i18n description/chapo/mobile/edition/adapted, bloc `places.descriptions` ; émet aussi `*_raw` = base scalaire brute pour la leg éditeur `parseDescriptionScope`) ; **15d `_iti`** (`object_iti_stage.description`, bloc `itinerary_details.stages` + 3 exports plats `build_iti_track` KML/GPX, `export_itinerary_gpx`, `get_itinerary_track_geojson`). Les blocs `to_jsonb(row)` soustraient la clé plate brute (+ la colonne `*_i18n` brute) et la surchargent par `api.strip_markdown(...)` + un frère `*_md`. **Legs éditeur jamais strippées** : room/menu chargent par select direct (pas de raw-leg) ; direction/place exposent une leg brute (`direction_md`/`*_raw`) ; iti a un override select direct. **Aucune colonne ajoutée** (sauf le tsvector recherche, déjà foldé `schema_unified.sql`), pas de migration de données (strip à la volée). 0 changement de signature ⇒ `get_object_resource` ré-appliqué à live via `.tmp_pgapply/apply_range.cjs`, petites fonctions via MCP `execute_sql`, `NOTIFY pgrst` après. Applied incrémentalement 2026-06-22 (par tâche ; vérif transactionnelle par `tests/test_{direction,room_description,dish_description,place_description,iti_stage}_markdown.sql`). After `api.strip_markdown` (folded §106) + step 1 (`schema_unified.sql`) + `api_views_functions.sql`. Frontend (`MarkdownEditorLazy` block/inline swaps, `MarkdownCellField` pour les lignes de répéteur places/iti) hors manifest. **Pas de rendu tiroir public dans cette livraison** (`*_md` émis = data-ready). Decision log §112 ; spec `docs/superpowers/specs/2026-06-22-markdown-description-fields-delivery2.md` ; plan `docs/superpowers/plans/2026-06-22-markdown-description-fields-delivery2.md`.

16a. `migration_ai_provider_config.sql` — **Extraction IA de carte §06 : configuration fournisseur (Phase 1)** (**NON foldé dans `schema_unified.sql`** — les RPCs référencent `api.is_platform_superuser`, définie dans `rls_policies.sql` ; folder ici casserait la passe fresh-apply en 42883. `CREATE TABLE IF NOT EXISTS` ⇒ idempotent/re-jouable). Table `public.app_ai_provider_config(id, label, api_kind CHECK('openai_compatible'|'anthropic'), base_url, model, key_secret_id uuid, max_output_tokens, is_active, extra jsonb, created/updated_at)` — **plusieurs profils fournisseur, UN seul actif** (index unique partiel `uq_ai_provider_active WHERE is_active`). **La clé API vit dans Supabase Vault** (`key_secret_id → vault.secrets`), JAMAIS dans la table, JAMAIS renvoyée au client. RLS ON + **aucune policy + aucun GRANT anon/authenticated** ⇒ deny-all direct PostgREST ; tout passe par les RPCs DEFINER. RPCs **super-admin gated** (`api.is_platform_superuser`, `RAISE FORBIDDEN`) browser-callable : `api.upsert_ai_provider(...)` (crée/rote le secret Vault via `vault.create_secret`/`update_secret` ; **désactive-les-autres-puis-active** pour respecter l'index partiel non-déférable — un `SET is_active=(id=…)` global laisse 2 TRUE transitoires ⇒ 23505), `api.list_ai_providers()` (renvoie `has_key` bool, **jamais la clé**), `api.set_active_ai_provider(id)`, `api.delete_ai_provider(id)` (supprime aussi le secret Vault) ; `REVOKE … PUBLIC,anon` + `GRANT … authenticated,service_role` — l'advisor `authenticated_security_definer_function_executable` (WARN) sur ces 4 RPCs est **attendu** (§36 class, self-gated). **Lecteur d'exécution serveur** `api.get_active_ai_provider_secret()` renvoie la config **+ clé déchiffrée** (`vault.decrypted_secrets`) : `REVOKE … FROM PUBLIC, anon, authenticated` + `GRANT … TO service_role` SEUL — le grant EST la frontière de confiance (inexécutable par un utilisateur connecté ; seules les routes serveur extraction/test-connexion, porteuses de la service-role key, l'appellent). `gen_random_uuid()` (search_path restreint). After step 6 (`rls_policies.sql` — `api.is_platform_superuser`) + extension `supabase_vault`. Live-applied 2026-06-22 (MCP `ai_provider_config_16a` + `ai_provider_config_16a_fix_single_active` ; vérifié transactionnellement : key round-trip Vault, list n'expose pas la clé, get_active service_role-only, un seul actif, ROLLBACK). Couvert par `tests/test_ai_provider_config.sql`. Page réglages super-admin + routes `/api/menu/extract` et `/api/admin/ai-config/test` hors manifest. Decision log §114 ; spec `docs/superpowers/specs/2026-06-22-ai-menu-extraction-design.md`.

16b. `migration_ref_code_admin_rpcs.sql` — **Phase 7.5 éditeur de référentiels `ref_code`** (**NON foldé dans `schema_unified.sql`** — les RPCs référencent `api.is_platform_superuser` de `rls_policies.sql` ; folder ici casserait la passe fresh-apply en 42883. `CREATE OR REPLACE` ⇒ idempotent). `ref_*` est admin-write RLS ⇒ l'écriture éditeur passe par 4 fonctions `SECURITY DEFINER` gated `api.is_platform_superuser()` (précédent `api.create_tag`, 14h) : `api.ref_code_domain_is_editable(domain)` (éditable = **NON structurel** : `NOT EXISTS` d'une entrée `ref_code_domain_registry` marquée `is_taxonomy`/`is_hierarchical`/`object_type` — les domaines plats n'ont souvent **pas** d'entrée registre ⇒ éditables ; les `taxonomy_*` sont structurels ⇒ lecture seule), `api.rpc_upsert_ref_code` (crée : `code` normalisé `immutable_unaccent(lower())` + **VERROUILLÉ**, `is_active=true`, `position=MAX+1`, `gen_random_uuid()` ; édite : `name`/`name_i18n`/`position`, code+domaine verrouillés), `api.rpc_set_ref_code_active`, `api.rpc_reorder_ref_code` (position = rang dans le tableau d'ids), + lecture `api.list_ref_code_domains()` (domaines éditables + compteurs `n_values`/`n_active`, `GRANT … anon`). **Gate FAIL-CLOSED** : `IF api.is_platform_superuser() IS NOT TRUE` (PAS `IF NOT …` — `is_platform_superuser()` renvoie **NULL** hors contexte auth ⇒ `NOT NULL = NULL` ne lèverait pas = fail-open ; bug attrapé + corrigé). v1 = **désactiver-pas-supprimer** (suppression à 0 référence déférée v2). L'advisor `authenticated_security_definer_function_executable` (WARN) sur les 3 RPCs d'écriture est **attendu** (§36 class). After step 6 (`rls_policies.sql` — `api.is_platform_superuser`) + step 1 (`schema_unified.sql` — `ref_code`/`ref_code_domain_registry`/`public.immutable_unaccent`). Live-applied 2026-06-23 (MCP `ref_code_admin_rpcs` + `ref_code_admin_rpcs_failclosed_gate` + `list_ref_code_domains` ; vérifié transactionnellement super-admin local + ROLLBACK : create/edit-code-verrouillé/deactivate/refus-domaine-structurel/fail-closed-sans-superadmin, 0 ligne de test persistée). Couvert par `tests/test_ref_code_admin_rpcs.sql`. Front (`services/ref-codes.ts`, `RefCodeEditor`, section « Listes & référentiels » du rail 7.1) hors manifest. Decision log §115 ; phase `docs/design/ui-ux-overhaul/phase-7-parametres.md` §7.5.

16c. `migration_moderation_rpcs.sql` — **P2.1 §120 Module Modération (`pending_change`)** (**NON foldé dans `schema_unified.sql`** — les RPCs référencent `api.is_platform_superuser`/`user_has_permission`/`current_user_crm_object_ids`/`can_read_object` de `rls_policies.sql`+migrations, et ré-invoquent les writers de `object_workspace_*` ; folder ici casserait la passe fresh-apply en 42883/42883. `CREATE OR REPLACE` ⇒ idempotent). La table `pending_change` + ses triggers `is_editing` (after insert/update/delete) **existent déjà** dans `schema_unified.sql` — **aucune DDL de table ici**. 5 fonctions `SECURITY DEFINER` schéma `api`, search_path restreint (`public, api, auth`), `REVOKE … FROM PUBLIC, anon` + `GRANT … TO authenticated, service_role` : (1) `api.user_can_moderate_object(text)` = `is_platform_superuser() OR (user_has_permission('validate_changes') AND object_id IN current_user_crm_object_ids())` (« publisher-ORG = ORG du user ») ; (2) `api.submit_pending_change(p_object_id, p_target_table, p_target_pk, p_action, p_payload jsonb, p_metadata jsonb DEFAULT NULL) → uuid` — **large** : authentifié + objet lisible (`can_read_object`), `submitted_by = auth.uid()`, statut `pending` (le trigger bascule `is_editing`) ; (3) `api.list_pending_changes(p_status DEFAULT 'pending', p_object_id DEFAULT NULL, p_limit, p_offset) RETURNS TABLE(...)` — **auto-autorisée (§36)** : périmètre publisher calculé une fois (`current_user_crm_object_ids`), ne renvoie que les lignes modérables (superuser = tout) ; libellés `object_name`/`submitter_label`/`reviewer_label` + dérivés `field_label`/`before_value`/`after_value` (depuis `metadata`) ; (4) `api.approve_pending_change(p_id, p_review_note DEFAULT NULL) → jsonb` — garde `user_can_moderate_object`, refuse si déjà résolu ; **DÉCISION CLÉ — Option A** : ré-invoque le writer structuré nommé par `metadata->>'rpc'`, **validé contre une WHITELIST** des 7 save-RPCs de section `(p_object_id, p_payload)` (`save_object_commercial`/`save_object_workspace_sustainability`/`save_object_workspace_tags`/`save_object_itinerary_nested`/`save_object_openings`/`save_object_places`/`save_object_relations`) via `EXECUTE format('SELECT api.%I($1,$2)', v_rpc)` — **jamais d'EXECUTE arbitraire** ; puis `status='applied'` + `applied_at`/`reviewed_by`/`reviewed_at`. Le writer tourne **en tant que l'appelant** (auth.uid() inchangé sous DEFINER) ⇒ re-vérifie l'écriture canonique = défense en profondeur (le modérateur doit aussi satisfaire la garde du writer : superuser aujourd'hui, sinon `edit_canonical_when_publisher`). (5) `api.reject_pending_change(p_id, p_review_note) → jsonb` — garde `user_can_moderate_object`, **note obligatoire** non vide, refuse si déjà résolu, aucun re-dispatch. L'advisor `authenticated_security_definer_function_executable` (WARN) sur les 5 fonctions est **attendu** (§36 class). After step 1 (`schema_unified.sql` — `pending_change` + triggers), step 6 (`rls_policies.sql` — helpers), steps 7/8 (`object_workspace_safe_write_rpcs.sql` + `object_workspace_gap_rpcs.sql` — les 7 writers), et `migration_crm_module.sql` (`current_user_crm_object_ids`). Live-applied 2026-06-24 (MCP `moderation_rpcs_p21` ; vérifié transactionnellement par persona viewer/contributor/editor/superuser : helper, submit large + garde de lisibilité, list self-scopée, approve gated + re-dispatch effectif (le writer supprime réellement un object_payment_method) + rpc-hors-whitelist refusé + double-résolution refusée, reject note-obligatoire + is_editing↓FALSE à 0 pending, ROLLBACK, 0 fuite ; advisors : uniquement les WARN §36 attendus). Couvert par `tests/test_moderation_rpcs.sql`. Frontend (`services/moderation.ts` RPC-only re-exporté par `services/rpc.ts`, `views/ModerationPage.tsx` câblée, `object-workspace.ts` panneau §21 repointé sur l'RPC) hors manifest. Decision log §120.

I1. `migration_reference_catalog_rpc.sql` — **Audit API Phase 1 — endpoint catalogue de référentiels** (self-contained ; appliqué en CI après `migration_moderation_rpcs.sql`). 3 fonctions `api` **SECURITY INVOKER** (les `ref_*` sont déjà lisibles anon `USING(true)` ⇒ pas de DEFINER), `STABLE`, `search_path = api, public, extensions`, `GRANT … TO anon, authenticated, service_role` : `api.public_catalog_domains() → text[]` (**whitelist default-deny** des 65 domaines publics — 59 `ref_code` [40 vocabulaires plats + 19 taxonomies] + 6 tables séparées `amenity`/`classification_scheme`/`language`/`commune`/`sustainability_action`/`sustainability_category` ; **exclut** crm_sentiment/demand_topic/mood/feedback_type/booking_status/membership_*/distribution_channel/client_type) ; `api.list_catalog(p_domain, p_lang='fr') → jsonb` (un référentiel, forme uniforme `{code,name,icon_url,parent_code,domain}`, `name = COALESCE(api.i18n_pick(name_i18n,lang,'fr'), name)` [pour sustainability_action : colonne `label`/`label_i18n`], `parent_code` résolu par self-join [taxonomies] ou jointure famille/catégorie [amenity→ref_code_amenity_family, sustainability_action→ref_sustainability_action_category], filtres `is_active`+`valid_from/to`, tri `position NULLS LAST, code` ; **lève `22023` sur domaine non-public**) ; `api.list_reference_bundle(p_domains text[]=NULL, p_lang='fr') → jsonb` (objet `{domain:[...]}` ; NULL = tous les domaines publics ; les domaines non-whitelistés sont silencieusement filtrés). Différé I1b (ajout trivial) : classification_value, capacity_metric, tag, legal_type. Needs `api.i18n_pick` (`api_views_functions.sql`) + tables `ref_*` (schema) + seeds (données). Live-applied 2026-07-01 (MCP `reference_catalog_rpc_i1` ; vérifié : 65 domaines, payment_method 15/0-null, taxonomy_res 25 parents, amenity 161/161 familles, commune 24, labels 36, sustainability_action 239/0-null, bundle filtre crm_sentiment, **appel réel en rôle anon OK**, rejet domaine non-public OK). Idempotent (`CREATE OR REPLACE` + grants). Couvert par `tests/test_reference_catalog.sql`. Plan `docs/api-audit/2026-06-30-api-fix-plan.md` (I1).

R1a. `migration_partner_api_keys.sql` — **Audit API Phase 1 — fondation DB du modèle partenaire** (self-contained ; appliqué en CI après `migration_reference_catalog_rpc.sql`). Donne à l'API une AUTHENTIFICATION PARTENAIRE dédiée (clé API par prestataire externe, traçable/révocable/scopée) en vue de la passerelle `/api/public/*` (R1b). 2 tables schéma **`internal`** (non exposé PostgREST) RLS deny-all : `partner_api_key(id, label, key_hash UNIQUE [SHA-256 hex, jamais la clé brute], key_prefix, scopes[], is_active, expires_at, revoked_at, last_used_at, created_by, …)` + journal append-only `partner_api_call(id, key_id→SET NULL, path, status, occurred_at)`. 5 RPC `api` SECURITY DEFINER, `search_path = api, public, internal, extensions` : gestion **superuser-only** (garde **fail-closed** `is_platform_superuser() IS NOT TRUE` — `IF NOT fn()` serait fail-OPEN sur le NULL hors-auth) `rpc_issue_partner_key(label, scopes, expires_at)→jsonb` (génère `bk_live_`+24o hex, renvoie la clé brute UNE fois, stocke le SHA-256), `rpc_revoke_partner_key(id)`, `list_partner_keys()` (jamais le hash) — `REVOKE PUBLIC,anon` + `GRANT authenticated,service_role` (advisor DEFINER WARN attendu §36) ; auth/log **service_role-only** (`REVOKE PUBLIC,anon,authenticated` + `GRANT service_role` — le grant EST la frontière, cf. get_active_ai_provider_secret) `partner_authenticate(p_key_hash text)→jsonb {ok,id,label,scopes}` (le route Next hashe la clé en SHA-256 Node ⇒ la clé brute ne touche jamais la DB ; refuse inactif/expiré/révoqué ; stampe last_used_at) + `partner_log_call(key_id, path, status)`. Le rate-limit/quota = R2 (pas ici). After step 6 (`rls_policies.sql` — `api.is_platform_superuser`) + extension pgcrypto. Live-applied 2026-07-01 (MCP `partner_api_keys_r1a` + fail-closed fix ; vérifié transactionnellement : gate refuse sans superuser, auth-by-hash + scopes + révocation + log, service-role-only, 0 ligne laissée). Couvert par `tests/test_partner_api_keys.sql`. Route `/api/public/*` (R1b) hors manifest, à venir. Plan `docs/api-audit/2026-06-30-api-fix-plan.md` (R1).

R2. `migration_partner_rate_limit.sql` — **Audit API R2 — rate-limit de la passerelle partenaire** (self-contained ; après `migration_partner_api_keys.sql`). Limiteur pg-backed **fenêtre fixe** (pas de Redis dans le stack) pour `/api/public/*` : table `internal.partner_rate_bucket(key_id→partner_api_key ON DELETE CASCADE, window_start, count, PK(key_id,window_start))` + index `window_start` + RLS deny-all ; `api.partner_rate_check(p_key_id, p_limit=120, p_window_seconds=60) → jsonb {allowed, retry_after|remaining}` `SECURITY DEFINER` service_role-only (`REVOKE PUBLIC,anon,authenticated` + `GRANT service_role`) — upsert du compteur de la fenêtre courante (`clock_timestamp`), refuse au-delà de la limite. Le route Next appelle ce check après l'auth ⇒ 429 + `Retry-After` (fail-**open** côté route si la DB tombe : un limiteur d'abus ne bloque pas le trafic légitime ; l'auth reste fail-closed). ponytail : fenêtre fixe (burst ~2× à la frontière) ; balayage des vieilles fenêtres = maintenance (index posé), pas par appel. Le front interne n'appelle PAS ce limiteur. After R1a (partner_api_key). Live-applied 2026-07-01 (MCP `partner_rate_limit_r2` ; vérifié : limite=2 → 2 OK + 3e refusé + retry_after). Couvert par `tests/test_partner_rate_limit.sql`. Plan `docs/api-audit/2026-06-30-api-fix-plan.md` (R2).

C-4. `migration_partner_tombstone_feed.sql` — **Audit API Phase 1 — flux tombstone partenaire** (self-contained ; appliqué en CI après `migration_partner_rate_limit.sql`). Comble le trou de synchro delta : un partenaire voit les upserts (liste publiée `GET /api/public/objects`) mais **jamais les suppressions définitives** (§108 hard delete ⇒ la fiche disparaît sans signal). 1 fonction `api.list_deleted_objects_since(p_since timestamptz=NULL, p_limit int=500) → jsonb {tombstones:[{object_id,type,deleted_at}], cursor, count}` **SECURITY INVOKER** (moindre privilège, `STABLE`, `search_path = api, public, extensions`) lisant le journal **immuable** `object_deletion_log` (§108, 14x) WHERE `performed_at > p_since`, ordre `performed_at` ASC, `LIMIT` clampé 1..1000, `cursor = MAX(performed_at)` de la page (echo `p_since` si page vide ⇒ le partenaire garde son curseur). **RGPD** : projette UNIQUEMENT `{object_id, object_type→type, performed_at→deleted_at}` — **jamais** `report`/`performed_by`/`object_name`/`status_at_deletion`. **service_role-only** : `REVOKE … FROM PUBLIC, anon, authenticated` + `GRANT … TO service_role` (la passerelle appelle en service-role, qui bypasse la RLS superuser-only du journal ; si le grant s'élargissait, la RLS `object_deletion_log_admin_read` fail-close quand même). ponytail : plafond « égalité `performed_at` à la frontière de page » (négligeable — hard-deletes rarissimes : superuser-only, archivage requis, confirmation par nom ; upgrade = keyset `(performed_at,id)`). **PÉRIMÈTRE (arbitrage)** : couvre les suppressions DÉFINITIVES ; upserts/état courant = re-sync via `GET /api/public/objects` ; l'unpublish = tombstone LOGIQUE réconcilié par le partenaire (un vrai delta d'upserts exigerait un suivi de changements couvrant l'objet ET ses enfants — `object.updated_at` ne bouge pas sur l'enrichissement enfant — différé). After 14x (`migration_object_hard_delete.sql` — `object_deletion_log`). Live-applied 2026-07-01 (MCP `partner_tombstone_feed_c4` ; vérifié : grants anon/authenticated=false + service_role=true, forme vide `{count:0,cursor:null|echo}`, ET le **test transactionnel complet** [2 tombstones seedés → projection, anti-fuite RGPD des colonnes sensibles, filtre `since`, curseur=max, page vide=echo, `limit`] passe **en live self-cleaning** via `ROLLBACK_PROBE` ⇒ **0 ligne** laissée dans le journal immuable ; advisors sécu inchangés). Idempotent (`CREATE OR REPLACE` + grants). Couvert par `tests/test_partner_tombstone_feed.sql`. Route `GET /api/public/objects/deletions` (+ allowlist `list_deleted_objects_since`) hors manifest. Plan `docs/api-audit/2026-06-30-api-fix-plan.md` (C-4).

C-5. `migration_partner_i18n_all.sql` — **Audit API Phase 1 — i18n multi-langue « all »** (**foldé dans `api_views_functions.sql`** ⇒ **no-op sur une base fraîche** ; cette migration sert les bases live existantes ; appliquée en CI après `api_views_functions.sql`). Donne à un partenaire toutes les traductions d'une fiche publiée en UN appel (`?lang=all`) au lieu de rappeler la RPC par langue. **Approche A — `get_object_resource` N'EST PAS TOUCHÉE** : 2 fonctions additives, le défaut single-langue reste inchangé **byte-à-byte** (garde §103). (1) `api.strip_markdown_i18n(jsonb) → jsonb` **IMMUTABLE**, `search_path = pg_catalog`, `{lang: markdown} → {lang: texte propre}` (réutilise `api.strip_markdown` par langue ; clés minusculées comme `i18n_pick`, valeurs vides éliminées, `NULL`/`{}`/tout-vide → `NULL` ⇒ `jsonb_strip_nulls`-friendly) — `REVOKE PUBLIC` + `GRANT anon,authenticated,service_role` (primitive pure, aucun accès données, alignée sur `strip_markdown`). (2) `api.get_object_i18n_all(text) → jsonb` **SECURITY INVOKER** (`STABLE`, `search_path = api, public, extensions`) renvoie la **famille de prose publique `object_description`** (7 champs : description/`_chapo`/`_mobile`/`_edition`/`_adapted`/`_offre_hors_zone`/`sanitary_measures`) sous forme `{field:{lang:texte propre}}`, depuis **la MÊME ligne que `get_object_resource`** (overlay ORG publisher primaire → canonique en fallback), **visibilité `public` uniquement** (pas de contexte utilisateur sur la voie partenaire ⇒ jamais d'overlay privé/NULL). **CONTRAT MARKDOWN §106/§112** : texte plat par langue, **jamais de `*_i18n` brut** sur la voie tierce. **Projette UNIQUEMENT les maps `*_i18n`** : un champ FR-only sans map est absent (son FR reste servi par la clé `description` de base). Objet publié inconnu/non-publié → `NULL` ; bloc vide → `{}`. **AUTORISATION** : `REVOKE … FROM PUBLIC, anon, authenticated` + `GRANT … TO service_role` (mirror C-4 — la passerelle appelle service-role, qui bypasse la RLS ; **self-gate `status='published'`** = défense en profondeur, un mis-call ne peut exposer un brouillon). Ne dépend que de tables cœur (`object`/`object_description`/`object_org_link`) + `api.strip_markdown` ⇒ **couvert par le gate fresh-apply** (via `api_views_functions.sql`). Live-applied 2026-07-01 (MCP `partner_i18n_all_c5` ; vérifié : grants (service_role=true, anon/authenticated=false), ET le **test transactionnel complet** [unit strip par langue/minuscule/vides/NULL + comportemental overlay→canonique, visibilité NULL incluse, chapo, gate publié→NULL, public-only→`{}`, map-only→`{}`, id inconnu→NULL] passe **en live self-cleaning** via `ROLLBACK_PROBE` ⇒ **0 ligne** fixture laissée). Idempotent (`CREATE OR REPLACE` + grants). Couvert par `tests/test_partner_i18n_all.sql`. Route `GET /api/public/objects/{id}?lang=all` (+ allowlist `get_object_i18n_all`, + trim des legs éditeur `canonical_description`/`org_description`) hors manifest. Plan `docs/api-audit/2026-06-30-api-fix-plan.md` (C-5).

16d. `list_object_markers_rpc` — **§125 Explorer map markers (lightweight, one-call)** (folded into `api_views_functions.sql` ⇒ **no-op on a fresh DB**). New `api.list_object_markers(p_types object_type[], p_status object_status[], p_filters jsonb, p_search text) RETURNS json` — the MAP's data source, replacing the eager all-pages card fetch that fed the map. Returns ONLY the cheap direct columns a pin + hover needs (`{id,type,name,image,open_now,location{lat,lon,city}}`) for ALL matching geolocated objects in ONE call — **no per-row taxonomy/tag/badge enrichment** (that per-row work is what makes `api.list_objects_map_view` ~240 ms/item / unusable). **§36 authorize-once `SECURITY DEFINER`**: filtered id set ∩ `api.current_user_readable_object_ids()` (published ∪ extended = the SAME visibility the Explorer list enforces via the `object` SELECT RLS gate, §35/§38) evaluated ONCE ⇒ the `object_location` read runs RLS-free and never pays the per-row `api.can_read_object` scalar — the §35 anti-pattern that made a naïve markers JOIN take ~6.7 s on the editor (draft) path. Measured **113 ms** for the full corpus (840 markers) as the authenticated editor; set-equivalent to the list's visible-and-geolocated set (840 = 840, 0 discrepancy live-verified per persona; anon = published-only 361). `REVOKE … FROM PUBLIC` + `GRANT … TO anon, authenticated, service_role`; the `authenticated_security_definer_function_executable` (WARN) advisor is **expected** (§36 class — self-authorizing DEFINER). After step 1 (`schema_unified.sql` — `object`/`object_location`), `migration_cards_batch_authorize_definer.sql` (`api.current_user_readable_object_ids`), and `api_views_functions.sql` (`api.get_filtered_object_ids`). Live-applied 2026-06-26 (MCP migration `list_object_markers_rpc`). Idempotent (`CREATE OR REPLACE` + guarded grants). Covered by `tests/test_object_markers.sql`. Frontend (`MapPanel` reads markers not cards; lazy list; subtypes→`p_types`) hors manifest. Decision log §125.

15e. `migration_iti_section06_vocab.sql` — **§111 Section 06 ITI editor — vocabulaires manquants** (Phase A des fondations backend de la refonte §06 ITI). Seede **`ref_iti_assoc_role`** (8 rôles : `sur_le_parcours`/`a_proximite`/`point_de_depart`/`hebergement_etape`/`restauration`/`parking`/`point_interet`/`prestataire`) — la table était **0 ligne live** ⇒ tout `object_iti_associated_object` (« objets liés ») échouait en **FK 23503** ; `ON CONFLICT (code) DO NOTHING`. Crée **3 nouvelles partitions `ref_code`** (`iti_difficulty` 1-5, `iti_open_status` aligné sur le CHECK `object_iti.open_status` = open/partially_closed/warning/closed, `iti_stage_kind` depart/etape/point_interet/point_eau/panorama/parking/ravitaillement/arrivee) selon la recette §13 : `CREATE TABLE … PARTITION OF ref_code FOR VALUES IN (…)` + uniques par-partition `(id)`/`(code)` + paire RLS maison (`pub_ref_code_read` SELECT USING(true) / `admin_ref_code_write` FOR ALL), seeds via `INSERT … SELECT … WHERE NOT EXISTS` (ref_code PK = `(id,domain)` avec id `uuid_generate_v4()` ⇒ pas de cible `ON CONFLICT` re-jouable). Remplace les write-traps de l'éditeur §06 : Difficulté (input texte → INTEGER 1-5) et Statut d'ouverture (input texte → enum) deviennent des selects pilotés DB ; `iti_stage_kind` type explicite des étapes (stocké `object_iti_stage.extra->>'kind'`, remplace le D/A déduit de la position). After step 1 (`schema_unified.sql` — `ref_code` parent + `ref_iti_assoc_role`) et step 11 (`seeds_data.sql`). Live-applied 2026-06-22 (MCP `iti_section06_vocab` ; vérifié : 8 rôles + 5/4/8 codes, partitions dédiées + RLS). Idempotent (`CREATE TABLE/INDEX IF NOT EXISTS`, `DROP/CREATE POLICY`, `ON CONFLICT`/`NOT EXISTS` seeds). Couvert par `tests/test_iti_section06_vocab.sql`. Spec `docs/superpowers/specs/2026-06-22-section06-iti-editor-redesign-design.md`, plan `docs/superpowers/plans/2026-06-22-section06-iti-phase-a-backend.md`. Decision log §111.

14n. `membership_vocab_seed_and_create_rpcs` — **§17 « Rattachements organisationnels » adhésions OTI** (folded into `seeds_data.sql` + `api_views_functions.sql` ⇒ **no-op on a fresh DB**): (1) seeds the **membership vocabulary socle** — `membership_campaign` ×3 (`adhesion_2025`/`adhesion_2026`/`charte` "Charte d'engagement") + `membership_tier` ×4 (`membre`/`membre_premium`/`partenaire`/`charte_gratuit` "Charte (gratuit)") into the existing `ref_code_membership_campaign` / `ref_code_membership_tier` partitions (were 0/0 ⇒ the §17 adhésion UI was a dead no-op). A **free charte = campaign `charte` + tier `charte_gratuit`** (both `object_membership.campaign_id`/`tier_id` are NOT NULL; "gratuit" is label-borne, no price column). (2) Adds **create-on-the-go RPCs** `api.create_membership_campaign(p_anchor_object_id, p_name)` + `api.create_membership_tier(...)` — exact mirror of `api.create_tag` (`SECURITY DEFINER`, gated per-object by `internal.workspace_assert_can_write_object`, dedup on `ref_code.name_normalized` STORED generated col WITHIN the domain, code derived inline + anti-collision suffix, `gen_random_uuid()`; `REVOKE … FROM anon` + `GRANT … TO authenticated, service_role` — least-privilege like `create_tag`, the per-object gate denies anon anyway). The `0028/0029_*_security_definer_function_executable` advisor on the two RPCs is **expected** (§36 class; no new ERROR-level advisor). Frontend §17 became modal-driven: actor block REMOVED (authoring lives solely in §19 `ProviderCards`), `OrgPicker` modal for org attachment, `MembershipEditModal` with creatable campaign/tier comboboxes + an explicit "Aucune adhésion" empty state. After step 1 (`schema_unified.sql` — `ref_code` parent + the membership partitions), step 4 (`migration_permission_write_paths.sql` — `workspace_assert_can_write_object`), the api functions file (`api_views_functions.sql`), and step 11 (`seeds_data.sql`). Live-applied as MCP migration `membership_vocab_seed_and_create_rpcs` (+ a grant-tighten REVOKE anon follow-up; verified: 3+4 seeds positioned, RPC create→dedup `created:false` same `ref_id`, gate enforced, security advisor = only the expected DEFINER WARN). Idempotent (`ON CONFLICT DO NOTHING` seeds, `CREATE OR REPLACE` fns). Covered by `tests/test_membership_vocab.sql` + `tests/test_membership_create_rpcs.sql`. Frontend covered by `org-links.test.ts`, `membership-edit.test.ts`, `OrgPicker.test.tsx`, `MembershipEditModal.test.tsx`, `SectionAttachments.test.tsx`. Decision log §91.

L1. `migration_object_list.sql` — **Module « Listes & templates d'envoi »** (self-contained ; appliqué en CI après `migration_partner_rate_limit.sql`, avant le refresh MV). Permet à un conseiller OTI de transformer une **sélection** (liste STATIQUE) ou un **jeu de filtres Explorer** (liste DYNAMIQUE, ré-évaluée à chaque accès) en liste curatée imprimable / envoyable / partageable par lien public brandé OTI. **2 tables** `public.object_list` (métadonnées + `kind static|dynamic` + `filters jsonb` [payload RPC-ready `{buckets:[{types,filters,search}]}`] + `filters_url` + partage `share_token/share_enabled/share_expires_at` + statut) et `object_list_item` (membres statiques : `object_id text`→`object(id)`, `position`, `note_fr/en`) — **`object.id` est TEXT**. **Verrouillées** : RLS ON + `REVOKE ALL … FROM anon, authenticated` (aucun PostgREST direct) + policies SELECT `read_object_list[_item]` (defense-in-depth) ; helpers `api.user_can_read_list/user_can_write_list` (superuser OU membre ORG ; write = créateur OU admin ORG). **RPCs `SECURITY DEFINER` authorize-once (§36/§61)**, `search_path` restreint, `REVOKE PUBLIC,anon` + `GRANT authenticated,service_role` : `create_list`/`get_list`/`update_list`/`set_list_items` (reconcile non-destructif §40)/`delete_list`/`share_list` (token = 2× `gen_random_uuid()` hex ⇒ ~244 bits, **sans dépendance `extensions`** §29)/`list_my_lists`/`mark_list_sent` (route email : `last_sent_at` + statut `sent`, write-gated). **Résolution dynamique = wrapper du leaf existant** `api.resolve_list_object_ids(p_buckets jsonb, p_published_only, p_limit)` sur `api.get_filtered_object_ids` (published-only quand demandé, borné 200 — `ponytail` plafond, upgrade = pagination) ⇒ **zéro duplication** du prédicat de filtre ; membres effectifs via helper interne `api.list_effective_object_ids` (statique = `object_list_item`, dynamique = résolveur ; **service_role-only**, jamais anon/authenticated). **RPC PUBLIQUE anon** `api.get_public_list_by_token(p_token)` (`REVOKE PUBLIC` + `GRANT anon,authenticated,service_role`) : objets **PUBLIÉS uniquement** (item draft exclu), **AUCUNE PII destinataire** (`recipient_label` jamais émis), token invalide/désactivé/expiré → `NULL` indifférencié. Trigger `updated_at` (`api.tg_object_list_touch`, `search_path=pg_catalog`). Les advisors `anon_/authenticated_security_definer_function_executable` (WARN) sur les RPC sont **attendus** (§36 class). After step 1 (`schema_unified.sql` — `object`), step 6 (`rls_policies.sql` — `is_platform_superuser`/`current_user_org_id`/`current_user_admin_rank`) et `api_views_functions.sql` (`api.get_filtered_object_ids`/`get_object_cards_batch`). Live-applied 2026-07-01 (MCP `object_list_module` + fix search_path trigger ; vérifié transactionnellement en personas : statique 3 items / reconcile / update / share / **lecture publique 2 items published-only sans PII (draft exclu)** / dynamique `resolved_from=filters` borné / **isolation cross-org** / lock grants — 0 ligne persistée ; advisors = uniquement les WARN §36 attendus). Idempotent (`CREATE TABLE/INDEX IF NOT EXISTS`, `CREATE OR REPLACE`, `DROP/CREATE POLICY`). Couvert par `tests/test_object_list.sql`. Frontend (module `/listes`, page publique `/l/[token]`, accroches Explorer, canaux) hors manifest — à venir. **Incrément 2026-07-02 (MCP `object_list_item_contacts_accent`, source mise à jour EN PLACE)** : helper interne `api.list_item_contacts(text[])` (**service_role-only** ; contacts PUBLICS par item — `phone` avec repli `mobile` + `web` = website, `is_public` uniquement — le flag champ §49 compose) joint dans `get_list` ET `get_public_list_by_token` (clé `contacts` par item, `'{}'` si aucun canal) + `accent` émis par `list_my_lists` (liseré des cartes de la grille) ; vérifié en live (test transactionnel personas ROLLBACK — incl. sonde de fuite canal privé sur les payloads owner ET public — + smoke sur canaux réels). Spec `docs/superpowers/specs/2026-07-01-listes-templates-envoi-design.md`, plan `docs/superpowers/plans/2026-07-01-listes-templates-envoi.md`.

I4. `migration_object_jsonld_schemaorg.sql` — **Audit API Phase 2 — sortie multi-standards, profil schema.org (JSON-LD)** (self-contained ; appliqué en CI après `migration_object_list.sql`, avant le refresh MV). Expose chaque fiche publiée dans un format PIVOT sectoriel — le PO a acté « les 4 » (schema.org/DATAtourisme/APIDAE/Tourinsoft) ; cette passe livre **schema.org** bout-en-bout. **Approche A — `get_object_resource` N'EST PAS TOUCHÉE** (garde §103/§130) : la sortie JSON-LD vit dans une clé SÉPARÉE, fusionnée par la passerelle sous `data.jsonld` sur `?format=jsonld` (défaut inchangé byte-à-byte, comme `?lang=all`/C-5). **(1) Table `public.ref_interop_crosswalk(profile, object_type, target_class, context_url, is_active, PK(profile,object_type))`** : le mapping `object_type → classe cible` est **piloté par TABLE, jamais hardcodé en UI/RPC** (invariant I4). Clé (profile, object_type) ⇒ « un 2e profil = un seed de plus » (aucune DDL) ; `context_url` dénormalisé par profil ⇒ le RPC ne porte AUCUNE connaissance de profil en dur. Seed profil `'jsonld'` = schema.org, les **19** `object_type` (HOT→Hotel, HLO/RVA→LodgingBusiness, HPA/CAMP→Campground, RES→Restaurant, ASC/ACT/LOI/PCU/PNA→TouristAttraction, ITI→TouristTrip, FMA→Event, PRD/PSV→LocalBusiness, VIL→TouristDestination, COM→Store, SPU→CivicStructure, ORG→Organization — tous des types schema.org valides, **valeurs PO-ajustables** car en table). RLS = paire maison `ref_*` (pub-read `USING(true)` court-circuitant l'arme admin `FOR ALL` ⇒ pas de grant P0.3 ; `auth.role()` wrappé §39). **(2) `api.get_object_jsonld(p_object_id text, p_profile text='jsonld') → jsonb`** **SECURITY INVOKER** (`STABLE`, `search_path = api, public, extensions`), **service_role-only** (`REVOKE … FROM PUBLIC, anon, authenticated` + `GRANT … TO service_role` — la passerelle appelle service-role, qui bypasse la RLS ; **self-gate `status='published'`** = défense en profondeur, un mis-call n'expose jamais un brouillon). Émet un document schema.org : `@context`/`@type` (depuis le crosswalk — unmapped (profile,type) ⇒ `NULL`, jamais de fallback hardcodé), `@id` = `urn:bertel:object:<id>` (URN stable, zéro dépendance URL front), `name`, `description` en **texte propre** (`api.strip_markdown` §106), `image` (cover `cached_main_image_url`), contacts **public-only** (`telephone` phone/mobile, `email`, `url` = website http/https — allowlist SEC-7), `address` (PostalAddress ; `addressRegion`/`addressCountry` = périmètre plateforme La Réunion/FR, fait métier documenté `ponytail` — upgrade multi-région = dériver de `code_insee`), `geo` (GeoCoordinates), `sameAs` (web_channels **public-only** http/https). `jsonb_strip_nulls` ⇒ clés absentes omises. After step 1 (`schema_unified.sql` — `object`/`object_location`/`contact_channel`/`media`/`object_web_channel`/`object_description`), `api_views_functions.sql` (`api.strip_markdown`/`i18n_pick`) et step 6 (`rls_policies.sql` — `api.is_platform_superuser`). Live-applied 2026-07-01 (MCP `object_jsonld_schemaorg_i4` ; vérifié : 19 lignes crosswalk, grants (service_role=true, anon=false), advisors sécu inchangés, sortie schema.org valide sur fiches RES réelles, ET le **test transactionnel complet** [crosswalk @type, urn @id, description strippée, PostalAddress/geo, contacts/website/sameAs public-only avec exclusion des privés, gate publié→NULL, id inconnu→NULL, profil non-mappé→NULL] passe **en live self-cleaning** via `ROLLBACK_PROBE` ⇒ **0 ligne** fixture laissée). Idempotent (`CREATE TABLE IF NOT EXISTS`, seed `ON CONFLICT DO UPDATE`, `DROP/CREATE POLICY`, `CREATE OR REPLACE`). Couvert par `tests/test_object_jsonld_schemaorg.sql`. Route `GET /api/public/objects/{id}?format=jsonld` (+ allowlist `get_object_jsonld`, merge `data.jsonld`) hors manifest. Les 3 autres profils (datatourisme/apidae/tourinsoft) = passes suivantes (seed du profil + branche de sérialisation). Plan `docs/api-audit/2026-06-30-api-fix-plan.md` (I4) ; decision log §136.

I4b. `migration_interop_profiles.sql` — **Audit API Phase 2 — les 3 profils d'interop restants (datatourisme / apidae / tourinsoft)** (self-contained ; appliqué en CI après `migration_object_jsonld_schemaorg.sql` (I4), avant le refresh MV). Applique l'invariant §136 « un nouveau profil = un seed + une branche de sérialisation ». **(1) Seeds `ref_interop_crosswalk`** pour 3 profils × 19 `object_type` (crosswalk total 19→76) : classes DATAtourisme (ontologie `PointOfInterest` : Accommodation/FoodEstablishment/SportsAndLeisurePlace/CulturalSite/NaturalHeritage/Tour/EntertainmentAndEvent/Store/PlaceOfInterest ; `context_url` = `@vocab https://www.datatourisme.fr/ontology/core#`), types APIDAE (HOTELLERIE/HEBERGEMENT_LOCATIF/HOTELLERIE_PLEIN_AIR/RESTAURATION/ACTIVITE/EQUIPEMENT/PATRIMOINE_CULTUREL/PATRIMOINE_NATUREL/TERRITOIRE/FETE_ET_MANIFESTATION/DEGUSTATION/COMMERCE_ET_SERVICE/STRUCTURE ; `context_url` NULL), bordereaux Tourinsoft (**quasi-identité — les codes Bertel SONT de lignée Tourinsoft** : HOT/HLO/HPA/RES/PCU/PNA/FMA/ITI/ASC/DEG/COM/VIL/ORG ; `context_url` NULL). Valeurs **PO-ajustables** (données en table). **(2) `api.interop_object_core(text) → jsonb`** **SECURITY INVOKER** service_role-only : lecteur cœur PARTAGÉ (DRY) d'une fiche PUBLIÉE (published + `is_public`/visibilité public-only, `strip_markdown` §106) → `{type,name,description,image,street,postcode,city,lat,lng,phone,email,url,sameas}`. **(3) `api.get_object_interop(p_object_id text, p_profile text) → jsonb`** **SECURITY INVOKER** service_role-only : published-gate + lookup crosswalk (unmapped ⇒ NULL, jamais de fallback) + core + `CASE p_profile` construisant le document au format cible — **datatourisme** = JSON-LD (`@context` @vocab+schema/rdfs/dc/foaf, `@type ["PointOfInterest", <classe>]`, `rdfs:label`, `hasDescription`, `isLocatedAt`→`schema:PostalAddress`+`schema:GeoCoordinates`, `hasContact`, `hasMainRepresentation`) ; **apidae** = JSON (`nom.libelleFr`, `type`, `presentation.descriptifCourt`, `localisation.adresse`+`geolocalisation.geoJson [lng,lat]`, `informations.moyensCommunication`, `illustrations`) ; **tourinsoft** = enregistrement fielded (`SyndObjectID`=object.id, `type`=bordereau, `NomOffre`/`Descriptif`/`Adresse1`/`CodePostal`/`Commune`/`Latitude`/`Longitude`/`Telephone`/`Mel`/`SiteWeb`/`Photo`). **PÉRIMÈTRE HONNÊTE** : socle interopérable — structure de tête + vocabulaire de type corrects, champs CŒUR ; la conformité field-à-field à l'importeur cible (chaque plateforme a des centaines de champs + conventions régionales, Tourinsoft surtout) doit être validée contre cet importeur avant synchro de production. `get_object_jsonld` (§136) intacte. After I4 (`migration_object_jsonld_schemaorg.sql` — table `ref_interop_crosswalk`) + `api_views_functions.sql` (`strip_markdown`/`i18n_pick`). Live-applied 2026-07-01 (MCP `interop_profiles_i4b` ; vérifié : crosswalk 76 lignes, grants (service_role=true, anon=false sur les 2 fonctions), advisors sécu inchangés, sortie valide des 3 formats sur fiche RES réelle, ET le **test transactionnel complet** [3 formats : classe/type par crosswalk, adresse/géo, contacts public-only, gate publié→NULL, id inconnu→NULL, profil inconnu→NULL] passe **en live self-cleaning** via `ROLLBACK_PROBE` ⇒ **0 ligne** fixture). Idempotent (seed `ON CONFLICT DO UPDATE`, `CREATE OR REPLACE`). Couvert par `tests/test_interop_profiles.sql`. Route `GET /api/public/objects/{id}?format=datatourisme|apidae|tourinsoft` (+ allowlist `get_object_interop`, merge `data.<format>`) hors manifest. Plan `docs/api-audit/2026-06-30-api-fix-plan.md` (I4) ; decision log §137. **⇒ I4 « les 4 » COMPLET.**

I4c. `migration_interop_batch.sql` — **Audit API Phase 2 — sortie pivot PAR LOTS pour la liste partenaire (§153)** (self-contained ; appliqué en CI après `migration_interop_profiles.sql` (I4b) — **`LANGUAGE sql` ⇒ corps validé à la CREATE (§135b), DOIT venir après I4/I4b** dont il référence les fonctions). Les 4 profils n'étaient servis que par le détail `{id}?format=…` ⇒ synchroniser le corpus = N appels détail par page. **`api.get_objects_interop_batch(p_object_ids text[], p_profile text) → jsonb`** = UN RPC batch mince `{"<id>": <document>}` qui **wrappe les sérialiseurs existants** (DRY, aucune duplication de forme) : clamp **200 ids** (= plafond `page_size` de la liste), dedup, filtre `published` UNE fois (authorize-once §36 ; chaque sérialiseur re-gate par objet = défense en profondeur), dispatch `jsonld`→`get_object_jsonld` / autres→`get_object_interop`, `WHERE doc IS NOT NULL` ⇒ un id non-publié/inconnu/non-mappé est **ABSENT** de la map (jamais d'erreur ni de fallback), NULL/vide/profil inconnu → `{}`. **PERF mesurée live (garde-fou §125)** : 200 fiches × `get_object_interop('datatourisme')` = **88 ms** (~0,44 ms/fiche — index lookups simples, per-row borné par le clamp ; rien à voir avec la classe ~240 ms/item de §125). **SECURITY INVOKER**, `STABLE`, `search_path = api, public, extensions`, **service_role-only** (`REVOKE … FROM PUBLIC, anon, authenticated` + `GRANT … TO service_role`). After I4 + I4b. Live-applied 2026-07-01 (MCP `interop_batch_i4c` ; vérifié : grants, ET le **test transactionnel complet** [published-only + draft/inconnu absents + dedup, dispatch apidae/jsonld/tourinsoft, profil non-mappé→`{}`, NULL/vide→`{}`, clamp id#201 ignoré] passe **en live self-cleaning** via `ROLLBACK_PROBE` ⇒ 0 fixture). Idempotent (`CREATE OR REPLACE` + grants). Couvert par `tests/test_interop_batch.sql`. Route `GET /api/public/objects?format=<profil>` (extraction des ids de la page → 1 appel batch → merge `item.<profil>` par fiche, curseur/page_size inchangés ; + allowlist `get_objects_interop_batch`) hors manifest. Plan `docs/api-audit/2026-06-30-api-fix-plan.md` (I4) ; decision log §153.

16e. `migration_partition_maintenance_hardening.sql` — **Partitions born-gated + maintenance object_version (audit structure DB 2026-07-02, P1a)** (self-contained ; appliqué en CI après `migration_interop_profiles.sql` (I4b), avant le refresh MV). Deux causes racines fermées : **(1)** `public.create_object_version_monthly_partition()` existait mais n'était branché sur AUCUN cron ⇒ les partitions mensuelles s'arrêtaient à 2026_05 et toutes les lignes depuis le 01/06 tombaient dans `object_version_default` (951 lignes re-homées par ce fichier : DETACH default → création des mois présents → INSERT (le trigger `trg_audit_object_version` est AFTER UPDATE/DELETE ⇒ ne tire pas) → TRUNCATE → re-ATTACH). **(2)** Les DEUX créateurs de partitions (object_version + audit.audit_log) créaient des partitions **nues** — une partition n'hérite NI du RLS NI des policies du parent ; preuve live : `audit_log_2026_08/_09` (créées d'avance par le cron) étaient `relrowsecurity=false` + 0 policy. Côté audit c'est défense-en-profondeur (schéma non exposé PostgREST, 0 grant anon/authenticated) ; côté `public.object_version` une future partition nue aurait été **lisible anon en PostgREST direct** (snapshots complets de brouillons). Les créateurs deviennent **born-gated + auto-réparants** (create-if-missing puis TOUJOURS re-assert `ENABLE RLS` + la policy du parent, forme wrappée §39 ; arm `api.is_platform_superuser()` de la policy de lecture audit conditionnel `to_regproc` — ordre fresh : `schema_unified.sql` s'applique avant `rls_policies.sql` qui définit la fonction). Nouveau `public.ensure_object_version_partitions(months_ahead)` (miroir d'`audit.ensure_future_partitions`, service_role-only) appelé par `audit.maintain_partitions()` étendu (cron quotidien `maintain-partitions` 02:00 — ANALYZE des deux parents ; rétention `drop_old_partitions(12)` **audit uniquement** : les versions d'objet sont des données produit §98/14r, jamais purgées ici). Passe de réparation idempotente sur toutes les partitions existantes des deux parents. Folded into `schema_unified.sql` ⇒ no-op convergent sur fresh. Live-applied 2026-07-02 (MCP `partition_maintenance_hardening_16e` ; vérifié : 2026_06=951 lignes, default=0, 07/08/09 créées, 16/16 partitions RLS+policy, smoke `audit.maintain_partitions()` couvre les deux parents). Couvert par `tests/test_partition_maintenance_hardening.sql`. Decision log §146.

16f. `migration_ref_code_dup_policy_cleanup.sql` — **Drop des 6 paires de policies legacy dupliquées sur partitions ref_code (audit 2026-07-02, P1b ; advisor `multiple_permissive_policies` 24 combos/table)** (self-contained ; après 16e). Six partitions portaient À LA FOIS la paire maison (`pub_ref_code_read`/`admin_ref_code_write`, posée sur CHAQUE partition par la boucle de `rls_policies.sql`) ET une paire dédiée legacy : `ref_code_payment_method`/`ref_code_environment_tag`/`ref_code_view_type` (paires françaises « Lecture publique … »/« Écriture admin … ») + `ref_code_membership_tier`/`_campaign`/`ref_code_incident_category` (paires CRM `pub_*_read`/`admin_*_write`). Les 12 policies legacy sont DROPpées (assert paire maison présente avant COMMIT) et leurs CREATE **retirés de `rls_policies.sql`** (les DROP IF EXISTS y restent en ceinture). **Nuance sémantique documentée** : les 3 arms d'écriture français portaient en plus `api.is_platform_superuser()` — leur suppression retire l'écriture superuser en PostgREST direct sur ces 3 partitions, les alignant sur les ~60 autres (voie sanctionnée = RPCs DEFINER §119 `migration_ref_code_admin_rpcs.sql`). Live-applied 2026-07-02 (MCP `ref_code_dup_policy_cleanup_16f` ; vérifié : 2 policies exactement sur chacune des 6 ; smoke anon lit les catalogues). Couvert par `tests/test_ref_code_dup_policy_cleanup.sql`. Decision log §146.

16g. `migration_fk_covering_indexes.sql` — **Index FK couvrants + drop des doublons staging (audit 2026-07-02, P2a ; advisor `unindexed_foreign_keys` 310 findings/61 tables)** (self-contained ; après 16f). Le gros du lint est de l'amplification : un FK vers le parent partitionné `ref_code` est cloné dans `pg_constraint` une fois par partition référencée (`object_taxonomy` 53 clones / UN set (ref_code_id,domain) ; `object_web_channel` 53 / (kind_id,kind_domain)) ⇒ un index composite par set réel efface le paquet. Crée ~21 index sur les tables « worthwhile » : les 2 composites + `object_relation(target_object_id)` (le probe cascade delete-objet longtemps différé) + `object_relation(relation_type_id)` + `crm_interaction` ×8 (actor_id, topics, handled_by, owner, parent — le partiel §61 `idx_crm_interaction_parent WHERE NOT NULL` est convergé en index PLEIN, même nom, sinon l'advisor/le check couvrant l'ignorent — sentiments ×2) + `object_membership` ×3 + `object_menu_item` ×3 + `object_private_description` ×3. `ref_code_taxonomy_closure` volontairement PAS indexé (l'advisor le flague encore mais ses 3 sets FK SONT couverts live — pkey + idx ancestor/descendant ; bruit d'advisor périmé, vérifié 2026-07-02). Drop des 3 jumeaux exacts `staging.idx_old_data_*` (advisor `duplicate_index` ; DO gardé — staging absent d'un fresh). Les petites tables restantes du lint sont laissées SANS index (coût d'écriture vs bénéfice nul à cette échelle — décision d'audit, pas un oubli). Live-applied 2026-07-02 (MCP `fk_covering_indexes_16g` + `fk_covering_indexes_16g_fixup_parent` ; vérifié : 0 set FK non couvert sur les 7 tables). Couvert par `tests/test_fk_covering_indexes.sql`. Decision log §146.

16h. `migration_rls_initplan_broad_sweep.sql` — **Sweep large `auth_rls_initplan` catalog-driven (audit 2026-07-02, P2b ; 129 findings advisor)** (self-contained ; après 16g, **dernier des quatre** — catch-all). Le sweep §39 (8k) avait traité les 18 policies de la famille object et différé le reste (~120 ref_*/ref_code_*/user_org_*/permissions/app_user_profile/object_version+partitions audit/promotion/publication/i18n/org_config) ; depuis, des modules NEUFS ont régressé sur l'invariant §39 (« new policies MUST use `(select auth.x())` ») : `actor`/`actor_channel`/`actor_consent` (CRM §61/§63), `pending_change` (modération §120), `incident_report`, `app_user_profile`. Fix **catalog-driven, pas une liste manuelle** : DO scannant `pg_policies` (schemas public+audit) pour tout USING/WITH CHECK portant un `auth.uid()/auth.role()/auth.jwt()/auth.email()` NON wrappé (sentinelle `( SELECT auth.` protège le déjà-wrappé) et réécrivant via `ALTER POLICY` — remède Supabase documenté, sémantique IDENTIQUE (hoist en un InitPlan par requête au lieu d'un appel par ligne scannée). 0 policy live n'utilise `current_setting()` brut (vérifié) ⇒ auth.* est toute la classe. Auto-assert : lève si un unwrapped survit. Re-run = converge tout ce qu'une migration ultérieure aurait re-créé brut — **la garde permanente est `tests/test_rls_initplan_broad_sweep.sql`** (fin de manifest : ZÉRO policy non wrappée en public/audit, sinon gate rouge — c'est le garde-fou CI que l'audit recommandait). Les templates des boucles par-partition de `rls_policies.sql` (ref_code / object_version / audit_log) + `admin_pending_change`/`admin_object_version` parent + la paire `ref_code`/`ref_code_other` sont AUSSI wrappés à la source (les futures partitions naissent wrappées via 16e). Live-applied 2026-07-02 (MCP `rls_initplan_broad_sweep_16h` ; vérifié : 129 réécrites, 0 restantes public/audit ET 0 autres schémas ; smoke anon catalogues/drafts/versions inchangé). Decision log §146.

16i. `migration_filters_open_at_event_dates.sql` — **Filtre « Ouvert à … » paramétré + filtre Dates Événements (audit filtres 2026-07-02, P1-c/P2 ; decision log §157)** (self-contained ; après 16h). Quatre CREATE OR REPLACE : `api.get_local_time_for_timezone(tz, p_at)` (résolveur d'heure locale à instant ARBITRAIRE — même validation try/catch `AT TIME ZONE` que §37, jamais de scan `pg_timezone_names`) ; `api.get_local_now_for_timezone` devient son délégué à `CURRENT_TIMESTAMP` (une seule implémentation) ; `internal.compute_open_status(p_at)` = **LE moteur d'ouverture** (2 bras EXISTS + tri-état §133 + « ouvert sans horaire » §93 + récurrence §92, publiés uniquement) ; `api.refresh_open_status()` devient son write-back à `now()` (byte-équivalent — la pastille et le filtre lisent le MÊME calcul, plus de dérive possible) ; `api.get_filtered_object_ids` gagne les clés jsonb `open_at` (ISO timestamptz ; moteur évalué UNE fois dans un CTE — jamais en LATERAL par ligne, leçon §37 ; ne matche que `is_open = TRUE`, le NULL tri-état n'est jamais matché, invariant §133) et `event:{from,to}` (recouvrement `[event_start_date, COALESCE(end,start)]` sur `object_fma` ; `recurrence_pattern` texte-libre NON évalué — limite documentée). Signatures inchangées ⇒ pas de NOTIFY pgrst. Post-apply : `SELECT api.refresh_open_status();`. Foldé : `schema_unified.sql` (3 fns) + `api_views_functions.sql` (le RPC). Couvert par `tests/test_filters_open_at_event.sql` (parité moteur/cache, parité open_at(now)==open_now, NULL jamais matché, fixture FMA rollback).

16j. `migration_filters_accessibility_label.sql` — **Filtre PMR : le label Tourisme & Handicap suffit (directive PO 2026-07-03 ; decision log §162)** (self-contained ; après 16i). Un seul CREATE OR REPLACE : `api.get_filtered_object_ids` gagne la clé jsonb `accessibility_any` (boolean — équipement famille `accessibility` OU `LBL_TOURISME_HANDICAP` `granted` ; clé DÉDIÉE : `amenity_families_any` reste équipement-pur pour le filtre transverse §159, et le payload PMR du front ne peut plus être clobbé par lui) ; `disability_types_any` gagne un bras label (subvalue_ids couvrant un type demandé, OU subvalue_ids vides = couverture inconnue ⇒ le label seul matche — état de l'import, à affiner via la saisie éditeur §10) ; `accessibility_any` ajouté à l'exclusion `use_mv` (labels non cachés — le test le prouve : fixtures en transaction invisibles du MV). Contexte : 0 objet ne porte d'équipement accessibility (catalogue 43 `acc_*` inutilisé) ⇒ le toggle PMR renvoyait TOUJOURS 0 malgré les labels T&H (Dimitile Hôtel, La Table du Dimitile). Signature inchangée ⇒ pas de NOTIFY pgrst. Foldé : `api_views_functions.sql`. Live-applied 2026-07-03 (apply_range 1050..1742 d'`api_views_functions.sql` ; vérifié : toggle PMR = les 2 objets T&H publiés, chip motor idem, corpus sans filtre inchangé). Couvert par `tests/test_accessibility_label_filter.sql` (label-only / label-typé / équipement-seul / aucun ; exclusion sur type non couvert ; pureté équipement d'amenity_families_any).

ORG1. `migration_org_onboarding.sql` — **Création d'organisation par un superadmin plateforme (chantier 2026-07-03)** (self-contained ; après `rls_policies.sql` pour `api.is_platform_superuser` et `schema_unified.sql` pour `org_config`/les triggers `object` ; non foldé — référence `api.is_platform_superuser`). Deux CREATE OR REPLACE : `api.rpc_create_org(p_name, p_region_code DEFAULT 'RUN', p_access_scope DEFAULT 'own_objects_only')` (SECURITY DEFINER, superuser-only) — **voie UNIQUE de création d'ORG** : crée l'objet ORG directement `published` (l'INSERT ne déclenche pas `trg_guard_object_status_change` = BEFORE UPDATE OF status, et un draft ORG serait impubliable faute d'ORG publisher ; `published_at` posé explicitement ; `trg_auto_attach_object_to_creator_org` no-op sur ORG) + `org_config` en une transaction ; gardes `NO_AUTH_CONTEXT`/`FORBIDDEN`/`MISSING_REQUIRED_FIELD`/`INVALID_ACCESS_SCOPE`/`DUPLICATE_ORG` (casse-insensible même région). `api.rpc_list_orgs()` (superuser-only) alimente le module « Organisations » de la console admin + le sélecteur d'ORG `/team`. Signatures nouvelles ⇒ `NOTIFY pgrst, 'reload schema';` (inclus). Advisors attendus : `authenticated_security_definer_function_executable` (WARN) sur les 2 RPC — classe §36 (auto-autorisation via `is_platform_superuser`). Live-applied 2026-07-03 (migration `org_onboarding_rpcs`, test transactionnel vert). Couvert par `tests/test_org_onboarding.sql` (création published + org_config + id ORGRUN ; 4 gardes ; list superuser-only).

ORG2. `migration_org_branding.sql` — **Branding par ORG (chantier 2026-07-03)** (self-contained ; APRÈS `ui_whitelabel_branding.sql` = `app_branding_settings` + `get_app_branding` de base, ET `migration_org_onboarding.sql` = les ORG existent ; non foldé — `user_can_manage_org_branding` référence `api.is_platform_superuser`). Table `org_branding_settings` (PK `org_object_id` FK `object` CASCADE, tous les champs identité NULLABLE = héritent, CHECK `#RRGGBB`/MIME quand non NULL, trigger type-ORG + `updated_at`) ; RLS lecture `TO authenticated USING(true)` (forme initplan-safe §39/§146), **aucune policy d'écriture directe** ; `api.user_can_manage_org_branding(org)` = superuser OU admin actif rang ≥ 30 de CETTE ORG ; `api.get_org_branding(org)` (gated) = `{raw, resolved}` pour l'éditeur ; `api.upsert_org_branding(org, …9 champs…, p_reset)` (gated, **full-state PUT**, `p_reset`=suppression) ; **`api.get_app_branding()` réécrite** — résout la surcharge ORG via `api.current_user_org_id()` (membership actif unique ⇒ non ambigu), COALESCE champ par champ avec le singleton, trio logo EN BLOC, `markerStyles`/`extra` restent plateforme, **signature/forme inchangées ⇒ zéro changement front** + clé `orgObjectId` ajoutée. `get_public_branding()` INCHANGÉE (login = plateforme). Advisors attendus : `authenticated_security_definer_function_executable` (WARN) sur les nouvelles DEFINER. Live-applied 2026-07-03 (migration `org_branding_per_org` ; vérifié : `get_app_branding` byte-équivalent plateforme table vide, `orgObjectId` présent, `get_public_branding` inchangée). Couvert par `tests/test_org_branding.sql` (résolution par champ, gates croisés org A/B, reset, public inchangée, anon 0 ligne).

16k. `migration_label_filter_sections.sql` — **Résultats sectionnés du filtre Label (decision log §173)** (self-contained ; après 16j — **le plus récent des CREATE OR REPLACE d'`api.get_filtered_object_ids`**, il DOIT porter le corps complet §157+§162+§173). Deux CREATE OR REPLACE : `api.get_filtered_object_ids` gagne la clé jsonb `label_scheme_ranked_exact_only` (restreint aux labellisés rank-0 ; démarches équivalentes exclues) ; `api.list_object_resources_filtered_page` trie `label_rank` en premier quand le filtre label est actif (même sous recherche) + renvoie `meta.label_rank_counts` = `{labelled, equivalent}` (comptes corpus, `null` sans filtre). `since_fast` intact (keyset (ts,id)). Signatures inchangées ⇒ pas de NOTIFY pgrst. Foldé : `api_views_functions.sql`. Live-applied 2026-07-06 (validate transient BEGIN/ROLLBACK vert + apply). Couvert par `tests/test_label_filter_sections.sql` (exact_only exclut les équivalents / défaut renvoie les deux / tri labellisé en premier sous recherche / meta.label_rank_counts présent).

16l. `migration_classification_regroup_network_labels.sql` — **Reclassement §175 : Gîtes de France + Clévacances = labels qualité, pas classements officiels** (self-contained ; après le seed `seeds_data.sql` — indépendant de la chaîne `get_filtered_object_ids`). Un seul `UPDATE` idempotent : `gites_epics` + `clevacances_keys` passent de `display_group='official_classification'` à `'quality_label'`. Motif : ce sont des **labels de réseau privés** (grille en épis / en clés), pas le classement officiel de l'État (étoiles Atout France) — retour métier OTI 2026-07-06. **DONNÉE de référence seule, aucun DDL.** **⚠️ SUPERSÉDÉ par 16m** (§176 les place, avec Logis, dans le groupe dédié `graded_label`) — 16l reste dans le manifest pour l'historique ; sur une base fraîche il est transitoirement réverté puis reconvergé par 16m (idempotent). Couvert par `tests/test_classification_regroup_network_labels.sql` (mis à jour pour l'état final graded_label).

16m. `migration_classification_graded_label_group.sql` — **Groupe dédié « Labels notés » §176 : Gîtes de France + Clévacances + Logis** (self-contained ; après 16l). Un seul `UPDATE` idempotent : `gites_epics` + `clevacances_keys` + `logis` passent en `display_group='graded_label'`. Motif : ce sont des distinctions **NOTÉES** par un réseau privé (épis / clés 1-5, cheminées/cocottes 1-3) — des objets classés, mais pas par l'État. Ils méritent un groupe distinct **des classements officiels** (`official_classification`, Atout France) **ET des labels binaires** (`quality_label`, obtenu/pas obtenu). Décision OTI 2026-07-06 (séparer les notés des labels binaires ; Logis inclus car même nature). **DONNÉE de référence seule, aucun DDL.** `display_group` ne pilote que les EN-TÊTES de regroupement (filtre Explorer « Distinctions », Dashboard `DistinctionOverview`, sélecteur éditeur §08) ; **aucun RPC ne branche sur `graded_label`/`official_classification`/`quality_label`** (seuls `sustainability_labels`/`accessibility_labels` sont testés en dur, les autres partagent le bras « tout le reste ») ⇒ inerte pour le filtrage (`classifications_any` matche sur les codes `scheme:value`), le badge carte (code/label inchangés), la cocarde §133 et la barre de niveaux §174. **Front : nouveau groupe câblé dans `RANKED_LABEL_FAMILIES` (Explorer, order 2), `DistinctionOverview` (Dashboard) + `DistinctionDisplayGroup`, et `DISPLAY_GROUP_LABELS` (éditeur §08).** Foldé : `seeds_data.sql` (⇒ no-op sur une base fraîche). Live-applied 2026-07-06 (UPDATE + vérif : les 3 en `graded_label`, `official_classification` = 8 classements officiels). Couvert par `tests/test_classification_regroup_network_labels.sql`.

14. `REFRESH MATERIALIZED VIEW CONCURRENTLY internal.mv_ref_data_json;` then `REFRESH MATERIALIZED VIEW CONCURRENTLY internal.mv_filtered_objects;`
15. Smoke tests (see Verification below).

**Upgrade-only — NOT part of a fresh install:** `branding_admin_profile_role_patch.sql` (only for databases that ran an older `ui_whitelabel_branding.sql` before its admin guard checked `app_user_profile.role`).

Fresh-install migrations and post-seed fixups use idempotent DDL/data patterns and are transaction-wrapped where the files contain `BEGIN`/`COMMIT`. RPC scripts are idempotent through `CREATE OR REPLACE` plus grants/revokes; review local/pilot-only scripts before reapplying.

### CI enforcement (deploy integrity)
A GitHub Actions gate, `.github/workflows/sql-fresh-apply.yml`, executes this manifest against a fresh Supabase local database on every change to `Base de donnée DLL et API/*.sql`, via the executable driver `Base de donnée DLL et API/ci_fresh_apply.sql` (which mirrors the manifest exactly, with `ON_ERROR_STOP`). If a migration is ever applied only to live PROD and never folded into the manifest/files, a fresh apply diverges and the gate goes red. Run it on demand from the Actions tab (**Run workflow** / `workflow_dispatch`). The driver is also the recommended way to bootstrap a local dev DB: `psql "$LOCAL_DB_URL" -v ON_ERROR_STOP=1 -f "Base de donnée DLL et API/ci_fresh_apply.sql"`.

## Incremental Update Order

> For routine SQL **updates** to an already-deployed database. A **fresh** install must follow the complete manifest above. For incremental updates, apply every changed DDL/RPC/storage file in the same dependency order as the fresh manifest before verification.

1. Apply changed schema/DDL/storage files in manifest order: `schema_unified.sql`, changed `migration_*.sql`, and `media_bucket.sql` if bucket or storage RLS changed.
2. Apply changed API, RLS, and RPC files in dependency order: `api_views_functions.sql`, `rls_policies.sql`, `object_workspace_safe_write_rpcs.sql`, `object_workspace_gap_rpcs.sql`, `ui_whitelabel_branding.sql`.
   > **Q1a caveat (audit 2026-06-30):** after `api_views_functions.sql`, run `migration_revoke_ops_grants_anon.sql` — it removes `anon` (via `PUBLIC`) from 7 ops/maintenance functions (cache refresh/disable/enable, taxonomy-closure refresh, open-status refresh, monthly-partition create) and re-grants `authenticated, service_role`. On a **fresh** DB those functions are born with `PUBLIC EXECUTE`, so this must run **after** their CREATE. `CREATE OR REPLACE FUNCTION` preserves grants, so re-applying `api_views_functions.sql` on an existing DB does NOT undo it. The fresh-apply gate does NOT catch a missing REVOKE (grant hygiene, not correctness).
   > **Q1b caveat (audit 2026-06-30, denylist — applied 2026-07-01):** run `migration_revoke_anon_q1b_denylist.sql` **last** (after ALL files that create/replace `api.*`, incl. the `16b`/`15e`/dashboard migrations) — it removes `anon` (via `PUBLIC`) from **57** functions proven not anon-needed: **31 trigger functions** (`RETURNS trigger`; EXECUTE not checked when a trigger fires ⇒ `REVOKE` only, no re-grant) + **26 write/admin/dashboard** functions (legal writes, `rpc_*_ref_code` admin, `get_dashboard_*`, `upsert_app_branding`, `set_itinerary_track`, exports… ⇒ `REVOKE PUBLIC,anon` + re-`GRANT authenticated, service_role`). Same fresh-DB rationale as Q1a (functions born `PUBLIC EXECUTE`). **KEEPS anon** on the 13 SELECT-applicable RLS-policy helpers + the public reader RPCs + i18n helpers (`user_can_write_canonical`/`user_can_create_object` are policy-referenced only in WRITE arms ⇒ already NOT anon-exec, untouched). Outside the fresh-apply gate (grant hygiene, like Q1a); verified LIVE by `tests/test_revoke_anon_q1b.sql` (anon-exec `api` count 180 → 123). Idempotent.
   > ⚠ **§47/§48 caveat (manifest 8o + 8r):** After re-applying `rls_policies.sql` or `object_workspace_safe_write_rpcs.sql` to a deployed DB, ALWAYS re-run `migration_write_policy_percommand.sql` (8o) **and `migration_actor_links_editor.sql` (8r)** — those source files still create the retired `FOR ALL` write families (`rls_policies.sql` also recreates the legacy `admin_actor_object_role_write` FOR ALL + the per-row read policy on `actor_object_role`, and `object_workspace_safe_write_rpcs.sql` still ships the actors-skip `save_object_relations` body), and this incremental order runs `migration_*` (step 1) before them, so skipping this silently **resurrects ~90 FOR ALL policies** on live (the P0.3 write-predicate-pollutes-read gotcha returns; `test_write_policy_percommand.sql` flags it as live-vs-fresh drift) and reverts the §48 actors write path. A **fresh** apply is safe without this — all FOR ALL creators sit at steps 6/7/8b/8c/8g, before 8o/8r.
   > ⚠ **§146 caveat (manifest 16h) :** après re-application de `rls_policies.sql` (ou de tout fichier créant des policies avec `auth.*()` brut) sur une base déployée, re-run **`migration_rls_initplan_broad_sweep.sql` (16h)** — `rls_policies.sql` recrée encore des centaines de policies en forme brute (seules les boucles par-partition, `admin_pending_change`/`admin_object_version` et la paire `ref_code`/`ref_code_other` sont wrappées à la source) ; 16h est catalog-driven donc reconverge tout en un run. Le test `test_rls_initplan_broad_sweep.sql` (gate CI, fin de manifest) matérialise l'oubli en rouge.
3. Apply changed post-seed/post-import fixups only when their preconditions match the target database.
4. Reload the PostgREST schema cache after function, grant, or exposed-schema changes: `NOTIFY pgrst, 'reload schema';`
5. Refresh materialized views introduced or affected by the update:
   - `REFRESH MATERIALIZED VIEW CONCURRENTLY internal.mv_ref_data_json;`
   - `REFRESH MATERIALIZED VIEW CONCURRENTLY internal.mv_filtered_objects;`
6. Smoke tests on key endpoints.

## Verification (Before/After)

Run on staging and compare before/after metrics:

```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT api.list_object_resources_since_fast(
  p_since := NOW() - INTERVAL '7 days',
  p_limit := 100,
  p_status := ARRAY['published']::object_status[]
);
```

```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT api.list_object_resources_filtered_since_fast(
  p_since := NOW() - INTERVAL '7 days',
  p_limit := 100,
  p_filters := '{"bbox":[2.0,48.5,3.0,49.0]}'::jsonb
);
```

```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT api.list_objects_with_validated_changes_since(
  NOW() - INTERVAL '30 days'
);
```

Also run the curated benchmark script:

```sql
\i 'Base de donnée DLL et API/test_performance.sql'
```

When using the new filtered MV path, run one direct plan check:

```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT object_id
FROM api.get_filtered_object_ids(
  p_filters := '{"within_radius":{"lat":48.8566,"lon":2.3522,"radius_m":5000},"amenities_any":["wifi"]}'::jsonb,
  p_types := ARRAY['RES','HOT']::object_type[],
  p_status := ARRAY['published']::object_status[],
  p_search := 'restaurant'
)
LIMIT 100;
```

## Success Criteria

- API responses remain contract-compatible for existing clients.
- Key endpoints show stable or improved execution time.
- No permission regressions on protected data paths.
- No migration rerun failures for policy creation.

## Rollback Plan

1. Re-deploy previous SQL revisions for modified files.
2. If index operations were introduced and need removal, drop newly added indexes:
   - `idx_pending_change_validated_effective_ts`
   - `idx_object_updated_at_id`
   - `idx_object_updated_at_source_id`
   - `idx_object_published_updated_at_id`
   - `idx_object_published_updated_at_source_id`
3. Re-run smoke tests and confirm endpoint behavior matches pre-deploy baseline.

## Post-Deployment Monitoring (24h)

- Track P95/P99 latency of list/filter endpoints.
- Track database CPU and buffer/cache pressure during peak periods.
- Check for permission-denied errors on RPC calls.
- Confirm no unexpected policy drift after reruns.
- Confirm `mv_filtered_objects` freshness remains within target SLA (5-15 minutes).
- Alert if refresh fails repeatedly or row-count drifts unexpectedly from published main-location baseline.

## MV Refresh Scheduling (Supabase pg_cron)

Use a cadence aligned with your accepted staleness budget:

```sql
SELECT cron.schedule(
  'refresh-mv-filtered-objects',
  '*/10 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY internal.mv_filtered_objects;$$
);
```

Health checks:

```sql
SELECT COUNT(*) AS mv_rows FROM internal.mv_filtered_objects;
SELECT COUNT(*) AS baseline_rows
FROM object o
JOIN object_location ol ON ol.object_id = o.id AND ol.is_main_location IS TRUE
WHERE o.status = 'published';
```

## Other Scheduled Jobs (pg_cron)

`api.refresh_open_status()` recomputes `object.cached_is_open_now` for published objects. It is **heavy** (~18–22s/run on ~373 objects: correlated EXISTS over the `opening_*` chain + per-object timezone LATERAL). To avoid periodic instance saturation that amplified the Explorer timeout, it runs **every 15 min, staggered** off the `*/5` MV refresh:

```sql
SELECT cron.alter_job(job_id := <id>, schedule := '3,18,33,48 * * * *');  -- refresh-open-status
```

"Open now" staleness budget: ≤15 min. A set-based rewrite (sub-second) is a tracked follow-up (`lot1_mapping_decisions.md` §35).