MARS Entity Minting Doctrine

Version: 1.0 Date: 2026-07-09 Owner: kee (mars) Audience: every vertical that writes to the MARS canonical layer — reit, credit, insurance, signals, bios, intl, lp, or any future spin-off.

Why this document exists

Every vertical inevitably needs to mint entities into MARS's canonical layer — companies, persons, investors, advisors, institutions. Get the match-vs-mint decision right and integration is free. Get it wrong and you contribute to the same class of pain we've absorbed repeatedly on the mars side:

• 2026-06-27 self-merger cleanup: 1,850 rows where Grok said target.root_domain == acquirer.root_domain and we minted separate canonicals for what was ONE company
• 2026-07-04 merge_entities pass: 891 canonical merges + 5,544 bridge repoints on the companies side, cleaning up phantom-mint accumulation
• NVIDIA dup: canonical "NVIDIA Corporation" + orphan "Nvidia" for months — extract_funding.py wasn't checking name_variants
• G3 spider misattribution: 798 companies_v2 rows poisoned with hosting-provider brand names (Cloudflare / Squarespace / Vercel) because extract_main.py wasn't sentinel-filtering
• 4× Jim Baird: the bios pilot found four separate persons_v2 rows for the same real person before we ran multi-signal Qwen verify

Every one of those cost real cleanup effort. This doctrine exists so new verticals never contribute to that list.

The one-page summary

If you remember nothing else, remember these 5:

1. Match first with STRONG IDs where present. CIK / CRD / LinkedIn URL / ticker — a hit on a partial-UNIQUE-indexed strong ID is a definitive match. Skip Qwen entirely. If your source carries a strong ID, populate it at mint time.
2. Multi-signal candidate search + Qwen verify — NEVER name alone. Name alone is how the 4× Jim Baird problem happens. Always pass Qwen employer / expertise / date / education / geography signals along with the name.
3. Sentinel filter at write time. Reject placeholder names ("Team Member", "TBD", "Unknown Target", "Confidential Parent") BEFORE any DB query. Every canonical has an is_sentinel_*() filter — use it.
4. Log every match/mint decision to identity_decisions_v2. With methodology_version + one-sentence reason. Reversibility is a hard requirement, not a nice-to-have.
5. Run a weekly Qwen-verified dedup sweep on rows minted under YOUR methodology tags. Even with rules 1-4 tight, some dupes slip through. merge_entities.py supports all 5 canonical kinds — use it.

The universal 5 rules

Rule U1 — Match first, mint only on no match

Before every INSERT into a canonical table, run the resolution chain:

1. Strong ID lookup (partial UNIQUE indexed column — see per-entity table below). Single hit → done. Two hits → substrate violation, file bug.
2. Name-based candidate search returning multiple candidates ranked by activity (row count, membership size, recency).
3. Qwen multi-signal verify on the candidate set. Confidence bands: high → attach; medium → attach + flag entity_resolution_pending=TRUE; low or no match → mint new.

You NEVER go straight to INSERT. If your code does INSERT INTO companies_v2 (...) VALUES (...) without first checking, you're the problem.

Rule U2 — Multi-signal Qwen verify — NEVER name alone

Name matches are necessary but not sufficient. For every candidate ranking > 0, pass Qwen a rich verification prompt:

• The extracted attributes (name + primary identifier + at least 2 secondary signals)
• Each candidate row with its existing state
• Prompt: "which candidate is the SAME real entity?" with confidence + reasoning required

Qwen must cite which signals matched. If reasoning is only "same name" → treat as no-match.

Reference prompts by entity kind: - Companies: mars_feed/resolve_pending_companies_qwen.py - Persons: mars_feed/adv_bio_pilot.py - Investors: mars_feed/qwen_investor_families.py

Rule U3 — Sentinel filter at write time

Every canonical entity kind has its own sentinel filter. Reject at the extractor OR at the ETL layer, but always BEFORE the resolution chain runs.

Sentinel rows are hard NULL / skip, not "mint with warn tier". They pollute downstream aggregates.

Rule U4 — Log every decision to identity_decisions_v2

Schema (already exists):

mars.identity_decisions_v2 (
  entity_kind         text,      -- 'company' | 'person' | 'investor' | 'advisor' | 'institution' | 'funding_deal' | 'merger_deal'
  entity_id           uuid,      -- primary
  related_entity_id   uuid,      -- for merge or link decisions
  decision            text,      -- e.g., 'match', 'mint', 'merge_candidate', 'known_distinct'
  reason              text,      -- one-sentence justification
  methodology_version text,      -- e.g., 'signals-lawsuit-v1', 'reit-mint-v1'
  created_at          timestamptz
)

Every match, mint, or merge decision by your ETL writes here. Consumers (health audits, cleanup sweeps, MCP tools) inspect this ledger. Six months from now when someone asks "why does this company have three canonical rows?" — the answer is one SQL query away.

Reversibility contract: any decision your vertical wrote can be reverted by filtering on your methodology_version tag and running the inverse. Never write in a way that isn't reversible.

Rule U5 — Weekly Qwen-verified dedup sweep

Even with rules U1-U4, some dupes slip through — name variants you didn't catch, edge cases, races. Every vertical runs a weekly dedup sweep on the rows minted under its methodology tags:

1. SQL candidate detector: find suspected dupes (same normalized name + overlapping strong-ID space, or nearby dates, or shared bridges)
2. Qwen classifier: SAME_ENTITY / DIFFERENT / UNCERTAIN with reasoning
3. Auto-apply SAMEs via merge_entities.py (registry supports all 5 canonicals — see [[ref-merge-entities-tool]])
4. Log every decision to identity_decisions_v2

Reference doctrine: [[ref-qwen-verified-dedup-pattern]] — proven 4× in one 14h session (07-07/08) on funding dupes + mega-round + investor aliases + phantom investors. $0 cost, 0 human review, 0 false positives.

Rule U6 — A name is written only when CORROBORATED (never on rendering alone)

A name may be written to primary_name or name_variants ONLY when something independent confirms it: it matches the existing canonical, or a strong ID (CIK / ticker / CRD / Atlas) backs it. An uncorroborated rendering is not written at all.

Why this is a rule, not a preference: name_variants is not a search field — it is the first match tried in find_or_create_company ("any existing company that has already aliased this spelling wins immediately"). A wrong variant therefore causes a silent, immediate mis-resolution, and if a different real company shares that string the two fuse. That is the Eli Lilly variant-explosion class (#193).

The trap this replaces. It is tempting to gate on the rendering — "is it Latin script?", "does it look like a company name?". Rendering is a format check masquerading as a correctness check. Measured on bios's Phase-2b web crawl (2026-08-01) against ticker-bearing companies as ground truth:

A Latin-script test passes all four. The corroboration test passes only the first — and it generalises to every failure mode at once (romanization, resold domain, partner/acquirer confusion) instead of needing a new guard per class.

Practical shape: default any identity writer to fill-only — populate NULL/empty columns, never overwrite a value a consumer already sees. Overwrite requires per-case adjudication. On bios's first 2b run this mattered: of 7,612 candidates 0 names were written (all already had one), while 7,602 industries and 7,563 specialties were genuinely missing. The gap is almost always missing data, not wrong data.

Measure against ground truth, not against the thing you are replacing. bios initially reported "74% name agreement with the spider" as if it were accuracy; the spider was itself a Grok extractor running at a 6% fetch-success rate. Re-measured against ticker/Atlas, exact agreement was 14%. Agreement with an incumbent is not correctness.

Corollary — where alternate renderings DO belong. Native-script and romanized forms have real search value (someone searching 惠普森医药 should find China Pharma Holdings). That is a search-only concern and must use a field the resolver never reads. Do not solve it by loosening name_variants.

What the write gate covers — and deliberately does not

Decided 2026-08-07 (kee): the gate protects critical NODE tables only. That is the whole of what was decided. Everything below the table is mars's reasoning for why the line sits there, and is open to argument — a first version of this section attributed the reasoning to kee as well, which was an overreach and is corrected here.

Gated:

companies_v2 · persons_v2 · investors_v2 · institutions_v2
funding_deals_v2 · merger_deals_v2

The gate does not run in front of edge or attribute tables — person_bios_v2, person_work_history_v2, person_education_v2, person_relationships_v2, and their equivalents. Those are directly writable.

"Not gated" is a statement about the GATE, not about ownership. It means those tables do not require a submit_* call. It does not mean mars has no say in them, that no other process may write them, or that a vertical owns them exclusively. Ownership of any given edge table is a separate question, settled per table, and is not decided here.

Why the line is drawn there

The gate exists for ONE failure mode: identity damage is irreversible.

• A fused entity silently merges two real companies and then looks like a legitimate row with an unusual history. Unpicking it is a campaign: ~421 divisional entities re-minted, 466 article rows re-attributed, and that was one class in one vertical.
• A junk node is permanent and every downstream consumer pays for it.

An edge row has none of that shape. Wrong FK, delete the row, nothing else moved. The cost is a DELETE, not an archaeology project. Gating it would buy almost no safety and would put a function call in front of 5.4M rows/30 days — bios's crawler alone sustains ~10 writes/second.

So an edge submission kind is not a gap we owe anyone — the protection that matters is already structural (see below). Mint discipline is about WHO GETS AN IDENTITY; what is then recorded about that identity is governed by whatever rules that specific table carries, not by the gate.

What this means in practice

• Verticals write edges and attributes directly, at whatever throughput they need. No submission kind is coming for them, and none is needed.
• This does not make edge tables off-limits to mars. Where an edge row IS the evidence for a gated decision — e.g. person_work_history_v2 is what the person gate reads to tell two namesakes apart — mars writing it may be the correct design. That is a live question, not a settled one; see the person-gate employer task.
• The FK still points at a gated node, so an edge can only ever attach to an identity that passed the gate. That is where the protection actually lives — you cannot hang a bio off a company that was never allowed to exist.
• Adding a NEW canonical node table? It joins the gated set. Adding a new bridge or attribute table? It does not.

The test, when it is not obvious

Ask: if this row is wrong, what does fixing it cost?

a DELETE                        -> not gated (edge / attribute)
finding every row that inherited a wrong identity  -> gated (node)

Per-entity kind chapters

companies_v2

Canonical table: mars.companies_v2(company_id UUID PRIMARY KEY)

Strong identifiers (partial UNIQUE indexed):

Sentinel filter: mars_feed/sentinel_names.py — 40+ exact strings + 4 regex patterns. Rejects "Unknown Target", "TBD Health", "Confidential Parent", "Unnamed KOSDAQ-listed company", etc. Extend the EXACT_SENTINELS frozenset when new patterns surface.

Normalization: mars_feed/name_aliases.py:normalize_name() — STRICT suffix-only version safe for blind cross-row matching. normalize_name_aggressive() exists but false-positives on "Pinnacle Technology Solutions" / "Pinnacle Financial Corporation" — only use when you have CIK or domain confirmation.

Match strategy (Rule U1 for companies): 1. CIK — WHERE cik = %s → definitive 2. Ticker — WHERE ticker = %s → definitive 3. Primary domain — WHERE primary_domain = %s (may return multiple; disambiguate) 4. Name (strict) — WHERE LOWER(primary_name) = LOWER(normalize_name(%s)) 5. Name (variants) — WHERE %s = ANY(name_variants) — do this BEFORE trigram. It's deterministic, free, and catches the "Kroger" → "The Kroger Co." class that trigram won't reliably catch. Query pattern (per the GIN + LIMIT 1 planner trap in CLAUDE.md — bare @> ... LIMIT 1 on <200K rows picks Seq Scan): sql WITH found AS MATERIALIZED ( SELECT company_id FROM companies_v2 WHERE name_variants @> ARRAY[$1] ) SELECT * FROM found LIMIT 1 MATERIALIZED hides the LIMIT from the planner → Bitmap Index Scan on companies_v2_name_variants_gin, ~5ms cold. Reit surfaced this as a class 2026-07-13. 6. Trigram fuzzy — similarity(LOWER(primary_name), LOWER(%s)) >= 0.85 7. Qwen verify on the candidate set with rich signals — but see the fund/vehicle warning in Rule U2 below. "Is Blackstone Real Estate Partners the same entity as Blackstone Europe Real Estate?" is world knowledge, NOT context Qwen can read out of a headline. For the fund/vehicle/regional-arm class, layer a deterministic core-token guard on top of Qwen — auto-attach only when the two names share a distinctive core AND the pick is the only candidate with that core; otherwise log merge_review_needed (NOT merge_candidate — see the §identity_decisions_v2 gotcha).

Reference impl (v1 legacy path): mars_feed/etl_company_id.py:find_or_create_company(). New verticals should NOT call this directly — use the mint API (mars-feed/mint/) which handles the strong-ID ladder + external_ids match + near-collision + reversibility uniformly.

• Task #252 fix (2026-07-28): find_or_create_company now accepts CIK-only calls (no domain). Historically returned no_domain and blocked any SEC-filer path where the company's website wasn't known — insider's Form 4 mints and adrian's Form D mass-mint had to route around this. The v1 news-mint pipeline (only remaining caller) still passes a domain, so this is a widening, not a behavior change.
• All verticals: use the mint API. Sedar / sections / footnotes / bios / insider / credit / signals / prospects all already do — they never hit this function.

Post-mint dedup: mars_feed/cleanup_company_name_collisions_tier_a.py — same-brand merge candidates + CIK-conflict resolution. Run weekly on rows minted under your methodology.

Auto-populated: entity_kind (task #336, LIVE 2026-08-04) — you do NOT set this. A BEFORE INSERT/UPDATE trigger populates companies_v2.entity_kind from row-local signals via mars.classify_entity_kind(). Taxonomy: operating_company | operating_company_thin | op_co_no_domain | investment_vehicle | foundation_nonprofit | spac_shell | individual | regulatory_body | sec_shell_unknown | unknown. Downstream consumers (manifest, coverage MVs, MCP tools) filter/partition by this. If your mint delivers a signal that should change the classification (e.g. new firm_type value, new EIN), the trigger fires automatically on UPDATE. Async Qwen residual (task #339) drains the unknown tail. When adding a new signal source, edit sql/entity_kind_v1.sql + re-run the one-shot UPDATE.

persons_v2

Canonical table: mars.persons_v2(person_id UUID PRIMARY KEY)

Strong identifiers:

FULL doctrine: bios_pipeline/MINTING_PEOPLE.md (10 rules, drop-in code for sentinel filter, name normalization with 20-entry nickname map, multi-signal Qwen prompt).

Reference impl: - mars_feed/adv_bio_pilot.py — end-to-end (proven 2026-07-08) - mars_feed/canonical_resolvers.py:resolve_or_mint_officer_person — runtime resolver used by Phase G - mars_feed/etl_form_d_officers.py — bulk mint at 1.4M-row scale

When to link vs mint: same person on multiple firms is common. Employer overlap in prior_employers[] should trigger match, not separate mints. Rule 4 of MINTING_PEOPLE.md covers this.

investors_v2

Canonical table: mars.investors_v2(investor_id UUID PRIMARY KEY)

Strong identifiers:

Sentinel filter: use the person-sentinel variant + specific firm placeholders ("Family Office", "Undisclosed VC", "Series-Only", "Endowments and foundations" — this last one is a real ExtractExtracted-from-article-prose contamination we cleaned up 2026-07-07).

Normalization: mars_feed/canonical_resolvers.py:INV_SUFFIX_PATTERNS — strips LLC / Inc / LP / GP / GmbH / etc. Reuse; don't fork.

Match strategy: 1. CRD — definitive for RIA-registered 2. Name (strict) after suffix strip 3. Trigram fuzzy on LOWER(primary_name) with sim ≥ 0.85 4. Name variants array match 5. Family parent match (if candidate's parent_investor_id matches yours or vice versa, both are same-family variants) 6. Qwen verify with (name / RAUM / registration_type / family_canonical / recent-deal-count)

Family rollup pattern: sub-vehicles get family_canonical_name + parent_investor_id pointing to the family head. See the 2026-07-08 a16z + Andreessen Horowitz cleanup as the canonical example.

Reference impl: mars_feed/canonical_resolvers.py:resolve_or_mint_investor + mars_feed/enrich_investors_with_adv.py.

Post-mint dedup: mars_feed/cleanup_investor_dupes_tier_a.py — CRD-conflict + fund-series detection. Run weekly.

advisors_v2

Canonical table: mars.advisors_v2(advisor_id UUID PRIMARY KEY)

Strong identifiers: no external strong IDs (M&A advisor firms don't have a single canonical ID like CIK). Match relies on name + fuzzy + Qwen.

Sentinel filter: reuse investor variant.

Normalization: mars_feed/canonical_resolvers.py:ADV_SUFFIX_PATTERNS (extends investor with LLP / PLLC / P.C. suffix strip — common in law firms).

Match strategy: 1. Name (strict) after suffix strip 2. Trigram fuzzy with sim ≥ 0.85 3. Name variants match 4. Qwen verify with (name / practice area if known / deal frequency)

Reference impl: mars_feed/canonical_resolvers.py:resolve_or_mint_advisor.

institutions_v2

Canonical table: mars.institutions_v2(institution_id UUID PRIMARY KEY)

Universities, business schools, accelerators, professional bar associations. Used by person_education_v2 bridge and (in principle) any future "person attended X" or "person licensed by Y" pattern.

Strong identifiers: none universal. Some universities have a IPEDS ID or a DUNS but we don't ingest those.

Sentinel filter: small — "University", "The College", "Business School", "N/A" alone.

Normalization: strip "University of" prefix variance ("University of California, Berkeley" vs "UC Berkeley" vs "Berkeley"), handle "MIT" vs "Massachusetts Institute of Technology".

Match strategy: name variants + trigram fuzzy + Qwen verify with (name / geography / degree types).

Reference impl: mars_feed/canonical_resolvers.py:resolve_or_mint_institution + mars_feed/migrate_institutions_to_canonical.py.

Common operations

The identity_decisions_v2 ledger

Consumed by every cleanup sweep, health audit, and MCP-facing tool. Standard decisions your vertical writes:

Every write includes methodology_version (your <vertical>-<source>-v<n> prefix) + one-sentence reason field.

⚠️ Gotcha (reit surfaced 2026-07-13): merge_candidate is a load-bearing executable verb, not a note-to-self. A vertical that writes "candidate" in the everyday English sense — "this is a candidate to review later" — will get silent merges on the next merge_entities.py --entity <kind> --apply run. Reit nearly shipped this bug: their first cut logged fund-vs-vehicle ambiguities (Blackstone REP vs Blackstone Europe RE) as merge_candidate, which would have fused two distinct entities on the weekly sweep. Use merge_review_needed for anything pre-adjudication; promote to merge_candidate only when the merge decision is final.

Post-mint Qwen dedup sweep

Run weekly (cron or timer) against your minted rows. Reference [[ref-qwen-verified-dedup-pattern]]:

# Pseudocode — see mars_feed/cleanup_investor_dupes_tier_a.py for full template
candidates = sql_detect_suspected_dupes(entity_kind='company',
    methodology_version=YOUR_TAG)
verdicts = qwen_batch_classify(candidates)  # SAME_ENTITY | DIFFERENT | UNCERTAIN
for pair in verdicts:
    if pair.verdict == 'SAME_ENTITY':
        merge_via_merge_entities_py(pair.keep, pair.absorb)
        log_decision('merge', ..., methodology_version=YOUR_TAG)
    elif pair.verdict == 'DIFFERENT':
        log_decision('known_distinct', ..., methodology_version=YOUR_TAG)

Cost: ~$0 (Qwen on bench3). Wall clock: seconds to minutes depending on candidate count.

merge_entities.py executor

mars_feed/merge_entities.py — atomic merger tool. Consumes merge_candidate decisions from identity_decisions_v2 and executes:

• Dynamic FK discovery via pg_catalog.pg_constraint (auto-adapts to new bridge tables — reit's real_estate_deals_v2.company_id was picked up on day 1 with 0 code changes)
• Per-pair savepoint transaction
• Column coalescing (drop absorbed into keep for arrays + scalars + JSONB)
• Denorm recompute (funding_round_count, merger_count, n_bios)
• Optional Grok-web pre-merge veto via --verify

Supports all 5 canonical kinds out of the box. You do NOT write your own merge code. When your dedup sweep decides "SAME_ENTITY", it queues a merge_candidate decision and lets merge_entities.py execute.

See [[ref-merge-entities-tool]] for full doctrine.

Health check assertions

Add to mars_feed/health.py:

• Mint rate anomaly: alert if your vertical's mint-to-match ratio inverts (>80% mints suggests matching is broken)
• Sentinel leak: count rows minted under your methodology where primary_name matches a sentinel pattern (should be 0)
• Dupe rate: count of near-dupe pairs (same normalized name + overlapping bridges) under your methodology
• Post-mint pending backlog: count of entity_resolution_pending=TRUE rows > 7 days old under your methodology

Each assertion follows the check_<vertical>_<metric>() shape. Include in the daily 13:00 UTC email digest.

When to invent a new canonical entity kind

Rare. In practice, every vertical's entities fit one of the 5 existing kinds:

• Companies → companies_v2
• People → persons_v2
• Investors / funds / LPs → investors_v2
• M&A advisors / bankers / auditors → advisors_v2
• Universities / accelerators / bar assoc → institutions_v2

Consider a NEW canonical kind ONLY when: 1. Your entity type has significantly different attributes AND 2. No existing kind can be extended without polluting other verticals' queries AND 3. You'll have >10K rows AND 4. Cross-vertical consumers will want to reference this kind

If ANY of those fails, extend an existing kind. Adding external_ids JSONB attributes or adding sparse columns to an existing canonical is almost always the right move.

If ALL of those pass, team-mail mars slug with a design proposal BEFORE writing any code. mars owns the canonical layer.

Integration checklist for new verticals

When a new vertical spins up:

• [ ] Read this doc
• [ ] Identify which of the 5 canonical kinds your vertical writes to
• [ ] Decide your <vertical>-<source>-v<n> methodology tag scheme
• [ ] Add your prefix to mars_feed/health.py ACCEPTED_PREFIXES tuple
• [ ] For persons: read bios_pipeline/MINTING_PEOPLE.md too — 10 rules go deep
• [ ] Read the reference impl for your entity kind (per-entity chapter above)
• [ ] Write your extractor with sentinel filtering at the FIRST validation step
• [ ] Wire your resolution chain: strong ID → name-based candidates → Qwen verify → mint or attach
• [ ] Log every decision to identity_decisions_v2 with your methodology tag
• [ ] Add health check assertions (mint rate, sentinel leak, dupe rate)
• [ ] Schedule a weekly dedup sweep
• [ ] Team-mail mars slug on first-100-rows landing so we can spot-check

Appendix — psycopg2 traps every vertical will hit

Insider found both of these during the 179K Form-4 mint on 2026-07-12. Neither is a bug in psycopg2 per se; they're behavioral defaults that silently corrupt your writes or your logs if you don't know them.

Trap 1 — register_uuid() before iterating any uuid[] column

Without psycopg2.extras.register_uuid(), psycopg2 returns a uuid[] column as its string representation: '{a1b2c3-...,d4e5f6-...,...}'. Looks like a list. Isn't.

company_ids = row['company_ids']       # str, not list
set().update(company_ids)              # iterates CHARACTERS
# → {',', '-', '0', '1', ..., '{', '}'}   ← silent corruption

execute_values catches type mismatches at INSERT, but a lenient TEXT[] or JSONB column will swallow the garbage. Rule: register once at every connection open.

import psycopg2, psycopg2.extras
conn = psycopg2.connect(...)
psycopg2.extras.register_uuid()   # do this every time

Trap 2 — cur.rowcount after paged execute_values reports only the LAST page

psycopg2.extras.execute_values(cur, "INSERT ...", data, page_size=500)
print(f"inserted {cur.rowcount}")   # prints 500, even if data had 179,544 rows

execute_values batches into pages of page_size and issues one INSERT per page. cur.rowcount reflects the most recent statement only.

If the count is load-bearing (health check, status email, log line, decision-log summary), do NOT trust cur.rowcount. Two safe patterns:

# Option A: verify from the table
cur.execute("SELECT COUNT(*) FROM my_table WHERE methodology_version=%s", (TAG,))
n_actually_inserted = cur.fetchone()[0]

# Option B: use execute_batch instead — cur.rowcount is accurate but slower
psycopg2.extras.execute_batch(cur, "INSERT ...", data, page_size=500)

Insider hit this when their mint printed "MINTED 544" for what was actually a 179,544-row insert. Nothing broke — the numbers just looked wrong in the status output, which is arguably worse (you trust the wrong number and act on it).

Trap 3 — register_uuid() is also required to INSERT a scalar uuid.UUID into a uuid column

Trap 1 covers reading uuid arrays. Trap 3 is the write side of the same coin: passing a uuid.UUID object as a parameter to a uuid-typed column silently fails without registration.

cur.execute(
    "INSERT INTO mars.person_work_history_v2 (history_id, ...) VALUES (%s, ...)",
    (uuid.uuid4(), ...)
)
# → psycopg2.ProgrammingError: can't adapt type 'UUID'
# → cur.rowcount == 0

The error is caught silently if you're wrapping in a try/except-per-row (a common shape for defensive inserts) — the whole run reports 0 inserted with no exception surfaced. Fix, per Trap 1: psycopg2.extras.register_uuid(pg) right after psycopg2.connect(...). Do it at every connection open, not just when you know you're reading arrays.

Discovered 2026-07-20 during qwen_work_history_from_bios.py first-run — every INSERT of a fresh uuid.uuid4() history_id failed silently and the sweep applied 0 rows until the registration was added.

Trap 4 — ARRAY_AGG(uuid_col) returns a Python string of the postgres array literal, not a list

Same class as Trap 1 but worth calling out for the ORM-adjacent code that uses ARRAY_AGG for aggregation:

SELECT p.person_id, ARRAY_AGG(b.bio_id ORDER BY ...) AS bio_ids
FROM ...

person_id, bio_ids = cur.fetchone()
# bio_ids is:  '{6f5dfdfe-c84c-4490-8bb0-bbe9786630bb,244e51d2-1cdd-...,...}'
# NOT: [UUID('6f5dfdfe-...'), UUID('244e51d2-...'), ...]

primary = uuid.UUID(bio_ids[0])   # → ValueError: badly formed hexadecimal UUID string
                                   # bio_ids[0] is the single character '{'

Same root cause as Trap 1 (register_uuid missing) but doubly nasty because the failure mode is a slice-a-string error rather than an obvious type mismatch. Fix options in order of preference: 1. STRING_AGG(col::text, ',' ORDER BY ...) — return a text-comma-list, split(',') on it. Trivial, no type registration. 2. Skip the array entirely — look up the value at apply-time via a separate query keyed by the primary. This is what qwen_work_history_from_bios.py ended up doing (lookup_primary_bio_id(cur, person_id) per-row). 3. Register the array type: psycopg2.extras.register_uuid() — works but doesn't cover uuid[] specifically without extra ceremony.

Also discovered 2026-07-20 alongside Trap 3.

Trap 5 — execute_values + RETURNING + plain cur.fetchall() silently drops all-but-the-last page

The write-side twin of Trap 2. When execute_values(..., page_size=N) inserts more than page_size rows, it issues multiple separate INSERT statements under the hood — one per page. If your INSERT has a RETURNING clause and you cur.fetchall() after, you only see the RETURNING rows from the last page. Every earlier page's newly-inserted rows are silently lost from your Python-side result — the DB has them, but your code thinks they don't exist.

# BROKEN — silently drops all but the last page's RETURNING rows
psycopg2.extras.execute_values(cur, """
    INSERT INTO persons_v2 (person_id, primary_name, methodology_version)
    VALUES %s
    ON CONFLICT DO NOTHING
    RETURNING person_id, primary_name
""", data, page_size=500)
minted = cur.fetchall()   # only ~500 rows even if data had 179,544

Deeds hit this in their FEC ingest pilot: 1.95M rows, silently dropped 750,661 (38.5%) as skipped_no_donor before catching it. The tell was the skip rate decreasing over the run (68% → 39%) instead of being random — a bug, not bad source data.

Fix: pass fetch=True to execute_values. It makes execute_values itself accumulate RETURNING rows across all pages and return them as a list:

# CORRECT — fetch=True aggregates across pages
minted = psycopg2.extras.execute_values(cur, """
    INSERT INTO persons_v2 (person_id, primary_name, methodology_version)
    VALUES %s
    ON CONFLICT DO NOTHING
    RETURNING person_id, primary_name
""", data, page_size=500, fetch=True)
# minted has all rows across pages, not just the last

Reference impls of the correct pattern in mars_feed: mint_form4_active_issuers.py:170, sync_companies_from_atlas.py:289 (both with inline comments citing the bug).

Discovered by deeds 2026-08-04 during FEC Phase 1 pilot. mars_feed audit same day: all 11 files using execute_values are safe (either use fetch=True, sidestep with individual inserts, or don't use RETURNING).

Related mars memory / doctrine

• [[ref-canonical-entity-pattern]] — the general canonical template + 4-pass resolver
• [[ref-qwen-verified-dedup-pattern]] — 5-step reusable dedup doctrine
• [[ref-entity-quality-doctrine]] — 5-layer defense (extractor + ETL + resolver + sweep + dashboard)
• [[ref-merge-entities-tool]] — atomic merger executor
• [[ref-inferno-tier-system]] — Qwen (free) → Haiku ($0.003) → Grok-web ($0.08) tier chain
• bios_pipeline/MINTING_PEOPLE.md — persons kind, deep-dive with 10 rules + drop-in code
• MARS_PLATFORM_CONTRACTS.md — the 10 non-negotiable platform contracts every vertical honors

The one-line summary

Every entity → strong-ID match first; name-based candidates + Qwen verify next; sentinel-filter at write; log every decision; weekly dedup sweep.

Everything else is elaboration.