Data model for provider attribution (source + "Provided By") #38

Description

The ProvidedBy component shipped in phlask-map (#692), and Billy raised in a recent Data Circle meeting that many scraped resources lack provider attribution or a logo. Kevin proposed (#phlask_dev) assigning providers automatically at scrape time to cut down manual work.

The display layer exists. The data model is what's missing. This issue scopes the schema so attribution can be assigned, reviewed, and audited.

Current state (measured against production)

source.type          MIGRATION   441 (46%)   ← not in DataSourceType!
                     MANUAL      313 (33%)
                     WEB_SCRAPE  198 (21%)

attribution          WEB_SCRAPE  163/198 attributed (82%)
                     MANUAL        1/313 attributed  (0%)
                     MIGRATION     0/441 attributed  (0%)

providers on file    2  (Share Food Program; "Test Provider" w/ no logo)
source.url domains   sharefoodprogram.org (167), sharingexcess.com (31), "sdfsdfsdf" (1)

Important nuance: the headline "788 resources lack attribution" overstates the problem. Broken out by type, the unattributed are FORAGE 310, WATER 253, BATHROOM 171, FOOD 54 — and most foraging spots (wild trees) and public fountains legitimately have no provider. The scrapers are already doing well (82%). So the real problems are narrower:

1. We can't tell "has no provider" apart from "provider unknown / needs research" — absence of a row means both. Without that distinction there's no way to build a "needs attribution" work queue or know when we're done.
2. Attribution can't flow through the review pipeline — resource_providers is a join table, and resource_edits (added in Add resource_edits review pipeline: holding table, queues, approve/reject/rollback #24) only mirrors the resources columns. A crowdsourced edit cannot add or correct a provider.
3. Scrapers have no stable key to resolve an org by; matching on name is fragile.
4. DataSourceType is "MANUAL" | "WEB_SCRAPE", but 46% of live rows are MIGRATION. The type is wrong, and source.url is unvalidated (see the sdfsdfsdf row).

Proposed data model

1. providers — add a stable machine key

alter table providers
  add column slug          text unique not null,  -- 'share-food-program'
  add column source_domain text;                  -- 'sharefoodprogram.org'

slug gives scrapers a stable identifier instead of name matching. source_domain does double duty: scrapers resolve their own provider, and it enables backfill by matching existing source.url domains (which would immediately attribute the remaining ~35 scraped resources).

2. resource_providers — add integrity + provenance

alter table resource_providers
  add column created_at     timestamptz not null default now(),
  add column attributed_via text,      -- SCRAPER | ADMIN | CROWDSOURCE
  add column is_primary     boolean not null default false,
  add constraint resource_providers_uniq unique (resource_id, provider_id);

attributed_via answers "where did this attribution come from" — the same provenance question source answers for the resource itself. is_primary decides which logo renders first (the ProvidedBy component already supports multiple with "+X more", and one resource currently has 2 providers). The unique constraint prevents duplicate links.

3. Distinguish "no provider" from "unknown" ⭐ the main decision

alter table resources
  add column attribution_status text not null default 'UNKNOWN'
    check (attribution_status in ('ATTRIBUTED', 'NONE', 'UNKNOWN'));

• ATTRIBUTED — has provider link(s)
• NONE — reviewed, genuinely has no provider (public fountain, wild fruit tree)
• UNKNOWN — not yet reviewed → this is the admin work queue

Without this, "no rows in resource_providers" is ambiguous and the backlog is unmeasurable. This is the one real judgment call in the proposal — flagging it for team sign-off, since an alternative is a simpler attribution_reviewed_at timestamptz (null = needs review).

4. Let edits carry attribution (closes the review-flow gap)

alter table resource_edits
  add column suggested_provider_ids bigint[];  -- null = no change proposed

Semantics matter here:

• NULL → the edit proposes no attribution change (leave resource_providers alone)
• non-null → replace the resource's provider set with exactly this list
• {} → explicitly remove all attribution

approve_edit() syncs resource_providers when the column is non-null. An array (rather than a parallel resource_edit_providers join table) keeps the current-vs-suggested diff trivial to compute and render in the compare view.

5. Fix the source type

• Add MIGRATION to DataSourceType in app/types/ResourceEntry.ts — it describes 46% of live rows
• Validate source.url on write

Scope

• Migration for 1–4 above
• Update approve_edit() to sync resource_providers from suggested_provider_ids
• DataSourceType fix + source.url validation
• Backfill: map source.url domain → providers.source_domain for the ~35 unattributed scraped resources
• Seed real providers (Share Food Program, Sharing Excess); remove "Test Provider"
• Set attribution_status = 'NONE' in bulk where a provider is not applicable (likely most FORAGE)

Out of scope / related

• Assigning providers inside the scrapers belongs with phlask-map #727 / #697 — this issue just gives those scrapers the slug/source_domain to resolve against
• Display is already done (phlask-map#692)

Reference

Builds on #24 (resource_edits review pipeline) and #3 (schemas & views).