Writable Standby PostgreSQL Failback Merges: Why ON CONFLICT (id) DO NOTHING Silently Drops Rows on Sequential IDs (And How to Fix It With UUIDs or Split Sequence Ranges)

-- 1. Audit primary key column types and defaults across all public tables
SELECT
  tc.table_name,
  kcu.column_name,
  c.data_type,
  c.column_default,
  c.is_identity
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
JOIN information_schema.columns c
  ON c.table_schema = tc.table_schema
  AND c.table_name = tc.table_name
  AND c.column_name = kcu.column_name
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_schema = 'public'
ORDER BY tc.table_name;

-- 2. Option A (Preferred for Lead/Form Tables): Core Built-In UUIDv4 Primary Key (No pgcrypto Needed in PG 13+)
CREATE TABLE IF NOT EXISTS public.form_submissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_domain TEXT NOT NULL,
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  origin_node TEXT NOT NULL DEFAULT 'primary',
  payload JSONB NOT NULL
);

-- 3. Option B (For Legacy Integer Tables): Dynamic Odd/Even Parity Above Current MAX(id)
-- Run on PRIMARY node (nextval() is guaranteed to be the first ODD integer above MAX(id), even if MAX(id) is even):
ALTER SEQUENCE public.leads_id_seq INCREMENT BY 2;
SELECT setval(
  'public.leads_id_seq',
  COALESCE((SELECT MAX(id) - (1 - (MAX(id) % 2)) FROM public.leads), 1),
  true
);

-- Run on STANDBY node (nextval() is guaranteed to be the first EVEN integer above MAX(id), even if MAX(id) is odd):
ALTER SEQUENCE public.leads_id_seq INCREMENT BY 2;
SELECT setval(
  'public.leads_id_seq',
  COALESCE((SELECT MAX(id) - (MAX(id) % 2) FROM public.leads), 2),
  true
);