sql-postgres-ambiguous-column-rollup

Answer

Bug A — rollup_domains_daily SQLSTATE 42702 (ambiguous publisher_domain)

001_initial.up.sql:400-420 selects/group by publisher_domain unqualified while the join exposes it from both shadow_attribution sa and tool_events te. Fix: qualify every column reference (te. for event-side attributes, sa. for attribution-side, explicit in ON). Corrected fragment:

-- 001_initial.up.sql:400-420  (fixed)
CREATE OR REPLACE VIEW rollup_domains_daily AS
SELECT
    DATE_TRUNC('day', te.recorded_at)    AS day,
    te.publisher_domain,                 -- qualified: tool event's domain
    COUNT(DISTINCT te.event_id)          AS events,
    COUNT(DISTINCT sa.attribution_id)    AS attributions,
    COALESCE(SUM(sa.revenue_usd), 0)     AS revenue_usd
FROM tool_events te
LEFT JOIN shadow_attribution sa ON sa.event_id = te.event_id
GROUP BY DATE_TRUNC('day', te.recorded_at), te.publisher_domain;

te.publisher_domain is the correct grouping key — the rollup counts tool events per domain; the orphan sa row (attribution whose event is absent) must not mint a new group. If product intent is instead "attribution domain", use sa.publisher_domain in both places — the invariant is: whatever you qualify in SELECT, qualify identically in GROUP BY, and no unqualified columns remain.

Bug B — 56 legacy publishers locked out after U01-002

U01-002 switched auth to users/api_keys but never backfilled existing publishers.api_key rows, so auth's JOIN api_keys … WHERE key = $1 returns 0 rows. Two-part fix:

1. Backfill migration (idempotent; reuses publisher UUID as user id for stable 1:1, copies existing keys so clients keep working):

-- 003_legacy_publisher_backfill.up.sql
BEGIN;

-- (a) one user per legacy publisher that lacks one
INSERT INTO users (id, email, role)
SELECT p.id, p.name || '@publisher.local', 'publisher'
FROM publishers p
WHERE p.is_active
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = p.id)
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.email = p.name || '@publisher.local')
ON CONFLICT DO NOTHING;

-- (b) carry publishers.api_key into api_keys (no key rotation => no client breakage)
INSERT INTO api_keys (user_id, key)
SELECT p.id, p.api_key
FROM publishers p
WHERE p.is_active
  AND NOT EXISTS (SELECT 1 FROM api_keys ak WHERE ak.key = p.api_key)
ON CONFLICT DO NOTHING;

COMMIT;

1. Defense-in-depth in the auth query — primary api_keys path, legacy fallback so any still-unbackfilled publisher can log in:

SELECT user_id, key AS api_key
FROM api_keys WHERE key = $1
UNION ALL
SELECT p.id, p.api_key
FROM publishers p
WHERE p.api_key = $1 AND p.is_active
  AND NOT EXISTS (SELECT 1 FROM api_keys ak WHERE ak.key = p.api_key)
LIMIT 1;   -- 0 rows only for genuinely unknown/inactive keys, never for stranded ones

Inactive publishers (is_active = FALSE) are deliberately excluded from both backfill and fallback — lockout for deactivated accounts is intended.