AAO: `discovered_properties.source` column to enable property-removal reconciliation in hosted sync

[bokelley]

Context

PR #4106 added hosted-property → federated-index sync. Authorizations are fully reconciled (rows we own with source='aao_hosted' are deleted when the manifest drops them). Properties cannot be — the discovered_properties table has no source column, so we can't safely distinguish hosted-written rows from crawler-written rows.

What's needed

Add a source column to discovered_properties:

ALTER TABLE discovered_properties
  ADD COLUMN source TEXT NOT NULL DEFAULT 'crawler'
  CHECK (source IN ('crawler', 'aao_hosted'));

Backfill: existing rows get 'crawler' (the default). Update the hosted-property sync to write 'aao_hosted' and reconcile on re-sync (delete rows for (publisher_domain, source='aao_hosted') not in the new manifest).

Why it matters

Today, a publisher who removes a property from their AAO-hosted manifest sees the property persist on the publisher page until manually cleared. Small but visible inconsistency. Tracked as a known limitation in the merged changeset; this issue is the cleanup.

Related

• PR feat(aao): publisher self-service — /publisher/:domain page, API rollup, hosted adagents.json #4106 (merged) — services/hosted-property-sync.ts
• See file-level comment in that service: "additive only — the table has no source column"

[bokelley]

Triage

Classification: Feature request (cleanup)
Bucket: admin/ops tools
Status: drafting-pr

What the experts said:

• internal-tools-strategist: Migration 202 already added source_type TEXT DEFAULT 'adagents_json' to discovered_properties — extending that column avoids a second overlapping origin-tracker; UNNEST composite exclusion keyed on (name, property_type) is the correct reconciliation key (not property_id, which is nullable).
• code-reviewer: Approved — ON CONFLICT correctly excludes source_type from the update set so crawler-written rows keep 'adagents_json'; empty-manifest branch required (present); push-before-try ordering is the safe-side choice (documented in code).

My take: Both experts agreed the existing source_type column is the right extension point rather than a new source column as described in the issue body. PR #4121 implements full property reconciliation: writes source_type='aao_hosted' on hosted-sync upserts and deletes stale aao_hosted rows on re-sync; crawler rows are never touched.

Drafting PR: #4121 — fix(registry): reconcile aao_hosted properties on re-sync via source_type

---

Triaged by Claude Code. Session: https://claude.ai/code/session_01BrwStUhPtFoxWMRkWMvytQ

---

Generated by Claude Code

[bokelley]

Triage

Classification: Feature request (server-side registry cleanup)
Bucket(s): registry / discovery
Status: drafting-pr

What the experts said:

• adtech-product-expert: right approach — mirrors the existing authorization-layer discriminator pattern; owned omission and source CASE logic are correct; behavioral commitment (hosted-manifest authoritative) documented.
• internal-tools-strategist: migration safety is fine (Postgres 11+ metadata-only ADD COLUMN); one blocker — concurrency race in the reconcile loop, fixed via advisory-lock transaction; CHECK constraint is nit-level.
• code-reviewer (two passes): stale module comment updated; statement_timeout added; identifiers/tags preserved on crawler-row conflicts; DELETE rewritten to key on (name, property_type) tuples; changeset corrected.

My take: Drafting PR #4125 with full reconcile semantics: the hosted manifest is authoritative for the publisher's property list, the DELETE is keyed on (name, property_type), and the whole property section runs inside a domain-scoped advisory-lock transaction.

---

Triaged by Claude Code. Session: https://claude.ai/code/session_01P6jrAAgREAnLNQodRxEyfQ

---

Generated by Claude Code