For: adrian
From: mars
Purpose: consolidate everything mars has shipped since your v2 gap doc, in one URL kee can forward. Your mars-ui/inbox/ has 8 unread notes from today; if your bus reader isn't set up yet this doc is the equivalent.
Headline correction to your progress report: items #1 signals_v2, #2 linkage denorm, and #3 scoring outputs on your "still open — five items" list are already shipped. Details + worked SQL below. Only #4 company_flags_v2 and #5 wealth + contact remain — both are kee-decision-gated (email sent to kee for those).
Following your process ask: every new table below comes with worked consumer SQL, not just column lists.
signals_v2 v0 — SHIPPED (task #285)mars.signals_v2. 389 detections across 3 generators. Nightly rebuild via refresh_signals.py, wired into mars-refresh-views.timer serving-tables chain.
CREATE TABLE mars.signals_v2 (
signal_id BIGSERIAL PRIMARY KEY,
kind TEXT NOT NULL, -- see enum below
company_id UUID, -- primary subject
person_id UUID, -- nullable, when person-anchored
verticals TEXT[] NOT NULL, -- ['form_144','form_d'] etc.
headline_facts JSONB NOT NULL, -- pre-rendered facts for UI
join_expr TEXT NOT NULL, -- reproducibility / debug
event_window_start DATE,
event_window_end DATE,
detected_at TIMESTAMPTZ NOT NULL DEFAULT now(),
integrity_tier TEXT NOT NULL, -- verified|vouched|warn
methodology_version TEXT NOT NULL
);
insider_sale_notice_after_form_d_close (84 rows) — Form 144 notice within 1-30d AFTER Form D on same company, market_value > $1M.
insider_open_market_sell_pre_merger (58 rows) — Form 4 open_market_sell aggregated per (target_company_id, person_id), 90d BEFORE definitive merger, SUM > $5M.
merger_target_had_recent_form_d (247 rows) — Form D within 365d BEFORE merger announcement on target.
-- Recent cross-vertical signals on watchlisted entities
SELECT s.kind,
c.primary_name,
s.headline_facts,
s.event_window_end AS anchor_date,
s.verticals
FROM mars.signals_v2 s
JOIN mars.companies_v2 c ON c.company_id = s.company_id
WHERE s.company_id = ANY(:watchlist_ids)
AND s.event_window_end > CURRENT_DATE - INTERVAL '90 days'
AND s.integrity_tier IN ('verified','vouched')
ORDER BY s.event_window_end DESC
LIMIT 25;
Adding a new detection kind = adding a SQL generator to refresh_signals.py. Nightly picks it up.
Your panel estimate for Home MARS signals should move 45% → ~80%.
Two materialized views, one row per entity, refresh nightly. LEFT JOIN target for the ◈ ×N pill.
mars.company_coverage_v2 (304,521 rows)company_id UUID PK
linkage_count INT signal-weighted total (excludes n_articles)
vertical_coverage TEXT[] chip array: {funding,ma,form_d,form_4,form_144,
13d,13g,people,articles,valuations,
liquidity,ipo,reit,credit,bankruptcy,
flare,sedar,bios}
identity_confidence NUMERIC(3,2) MAX over decisions; NULL = predates ledger
depth_tier TEXT 'none' | 'light' | 'medium' | 'deep'
-- Per-vertical counts
n_funding_deals, n_merger_deals_as_acquirer, n_merger_deals_as_target,
n_form_d_filings, n_form_4_transactions, n_form_4_filings,
n_form_144_filings, n_schedule_13d, n_schedule_13g,
n_people_ever_here, n_current_employees, n_articles,
n_valuation_marks, n_liquidity_events, n_ipo_offerings
-- Freshness anchors
last_funding_deal_date, last_merger_deal_date,
last_form_d_filing_date, last_form_4_filing_date, last_article_date
mars.person_coverage_v2 (1,513,762 rows)Same shape, person side:
person_id UUID PK
linkage_count INT
vertical_coverage TEXT[] {bio,work_history,education,form_4,
form_d_officer,deal_officer,
liquidity_events,comp,prospect_score}
identity_confidence NUMERIC(3,2)
depth_tier TEXT
n_bios, n_work_history_entries, n_current_roles, n_education_entries,
n_form_4_transactions, n_form_4_filings, -- <— this closes your ask about Form 4 counts per person
n_form_d_officer_roles, n_deal_officer_roles,
n_liquidity_events, n_comp_disclosures, n_company_role_rows
has_prospect_score BOOL
last_form_4_filing_date DATE
◈ ×N pill query-- for a rendered profile header
SELECT cv.linkage_count, cv.vertical_coverage, cv.depth_tier, cv.identity_confidence
FROM mars.company_coverage_v2 cv
WHERE cv.company_id = :focus;
Same on the person side. One row, one JOIN, no fan-out.
MAX over identity_decisions_v2 decisions per entity. NULL = predates ledger (render as "unknown", not "low").
| Signal | Score |
|---|---|
| cik/ticker/atlas exact match, bios+CRD | 1.00 |
| grok-confirmed merge, atlas Form 4 mint | 0.90 |
| ADV firm mint (has CRD), qwen non-dupe | 0.80-0.85 |
| gdelt / bdc / domain search mint | 0.65 |
| default mint / bios employer mint | 0.55-0.60 |
| pending mint | 0.50 |
| grok uncertain / merge_review_needed | 0.40 |
Only 22.5% of companies have identity_confidence populated today (older mints predate identity_decisions_v2). NULL should render as "unknown," NOT "low."
Three separate substrates, adrian composes final gauges.
mars.company_peers_v2 (task #279, 308,362 rows)Top-20 peers per funding-active company. Physical table (not MV — Postgres won't allow temp tables in MV definitions), same consumer interface. Nightly refresh 21s.
-- Companion query for the Peers panel
SELECT c2.primary_name, cp.similarity_score,
cp.industry_jaccard, cp.coinvestor_jaccard,
cp.n_shared_industries, cp.n_shared_investors
FROM mars.company_peers_v2 cp
JOIN mars.companies_v2 c2 ON c2.company_id = cp.peer_company_id
WHERE cp.company_id = :focus
ORDER BY cp.peer_rank
LIMIT 10;
Spot-check:
Score = 0.5 × industry_jaccard + 0.5 × coinvestor_jaccard, mega-industries filtered (>500 members) so pairs sharing only "Healthcare" or "Technology" don't produce noise.
Regarding the sections MCP tool for peer similarity: postgres substrate is now here for you to use. Both approaches work — pick per your latency budget (postgres = <15ms, MCP = network hop).
Your panel estimate for Company Peers should move 55% → ~90%.
mars.company_health_signals_v2 (task #276, 35,055 rows)Raw sub-signals; you compose the 0-1000 composite + Momentum/Money/Market/Management subgroups on your side.
-- Money (12mo trailing insider flow)
insider_buy_usd_12mo, insider_sell_usd_12mo, insider_net_buy_usd_12mo,
insider_buy_txn_count_12mo, insider_sell_txn_count_12mo
-- Momentum
last_funding_round_date, days_since_last_funding,
funding_amount_12mo_usd, funding_rounds_12mo
-- Market
article_volume_90d, article_volume_prior_90d
-- Management
personnel_hires_12mo, personnel_departures_12mo
-- Filings pulse
filings_12mo
Suggested starting composite formula (redesign at will):
money_score = tanh(insider_net_buy_usd_12mo / $100M) * 500 + 500 // 0-1000, midpoint 500
momentum_score = clamp((rounds_12mo / 3 + funding_amount_12mo_usd / $500M) * 100, 0, 1000)
market_score = clamp(log2((articles_90d + 1) / (articles_prior_90d + 1)) * 500 + 500, 0, 1000)
management_score = clamp((hires_12mo - departures_12mo) / max(n_current_employees, 10) * 500 + 500, 0, 1000)
overall_score = round(0.30 * money + 0.30 * momentum + 0.25 * market + 0.15 * management)
Caveats you'll want to code around:
funding_amount_12mo_usd for public companies includes Form D secondaries — NVIDIA/Apple show tens of billions "raised" that aren't primary. For public cos (ticker IS NOT NULL) render as "SEC financing activity" not "capital raised."personnel_hires_12mo / departures_12mo are thin — person_work_history_v2.started_at is NULL on many rows. Render "insufficient data" when both are 0 but n_current_employees > 0.article_volume_prior_90d = 0 with article_volume_90d > 0 = "just started getting coverage" — separate signal from a decline.Your panel estimate for Company Overview should move 75% → ~90%.
mars.person_edges_v2 (task #286, 5,511,963 edges) + tie_strength scalarTwo edge_kinds:
Mega-hubs skipped (>200 persons per company / institution) — the pair counts blow up and blanket-alumni edges aren't useful signal at scale. v1 would add restricted-cohort blocking for Apple/Google/Harvard-scale.
tie_strength column — pre-computed 0.10-1.00 scalar, indexed. Formula:
0.40 * shared_score + 0.30 * recency_score + 0.30 * duration_score
shared_score = min(1.0, n_shared_hubs / 5.0) -- 5+ shared hubs = max
recency_score = max(0.1, 1.0 - years_since_last_seen / 20.0)
duration_score = max(0.1, min(1.0, days_overlap / (5 * 365)))
Distribution: co_employment avg 0.411, max 1.000. Max = people who overlapped at 5+ companies across long careers (real "joined at the hip" tie).
-- top-20 ties for a person, ordered by strength then recency
SELECT
CASE WHEN e.person_a = :focus THEN e.person_b ELSE e.person_a END AS other_person_id,
e.edge_kind,
e.tie_strength,
e.last_seen,
jsonb_array_length(e.evidence) AS n_shared_hubs,
e.evidence
FROM mars.person_edges_v2 e
WHERE :focus IN (e.person_a, e.person_b)
ORDER BY e.tie_strength DESC, e.last_seen DESC NULLS LAST
LIMIT 20;
Indexes (person_a, tie_strength DESC) and (person_b, tie_strength DESC) make this <15ms even on the max-489-edges person.
Path score is client-side BFS on this substrate — mars doesn't need to precompute unless you want a specific 2-hop closest-K materialised. Ping if so.
Your panel estimate for Person Relationships should move 35% → ~85%.
mars.lp_commitments_ui_v2 (task #275, 1,033 rows)Flat serving view for the LP-commitment screen. 5 pensions × 795 funds × 14 GPs × $217B committed. 82.6% have a latest performance snapshot (IRR/TVPI/DPI/MOIC).
Grain PK: (lp_id, fund_id, commitment_reported_as_of). funds_v2 is the canonical fund table (which you noted); this MV is the pre-joined form for a screen.
-- Commitments per LP, ordered by size
SELECT * FROM mars.lp_commitments_ui_v2
WHERE lp_id = :calpers_id
ORDER BY commitment_usd DESC;
Extends as lp_data Phase 2 tier-2 adds more pensions (task #241, in-progress — 25 more pensions queued).
mars.company_thermal_footprint_v2 (task #284, 258 companies)Per-company thermal-asset rollup — for the THM vertical chip. 258 companies (utilities + steel mills + oil/gas). Non-flare companies get NULL — render "no thermal footprint" for those.
SELECT * FROM mars.company_thermal_footprint_v2 WHERE company_id = :focus;
Note the recent flare↔power-sec coord: power-sec is joining as a contributor to mars.thermal_assets_v2 for steel mills + refineries + LNG terminals etc. — so this coverage will grow substantially in coming weeks.
mars.companies_v2.firm_type (task #287, 2,088 classified)For the wirehouse filter. Values: wirehouse (7) · bank (12) · insurer (7) · pe (898) · hedge_fund (706) · vc (137) · real_estate (172) · asset_manager (7) · securitized (37) · liquidity_fund (7).
-- "Prior employer = wirehouse" career filter
SELECT DISTINCT p.person_id, p.primary_name
FROM mars.persons_v2 p
JOIN mars.person_work_history_v2 wh ON wh.person_id = p.person_id
JOIN mars.companies_v2 c ON c.company_id = wh.company_id
WHERE c.firm_type = 'wirehouse' AND wh.is_current = FALSE;
mars.investor_status_v2 (task #197, 100,161 investors)Derived status: active / dormant / wound_down / new / unknown.
Sanity: Sequoia 87 deals last 12mo, Accel 73, Kleiner Perkins 44, Founders Fund 43 — all active.
-- "Actively investing" chip on investor profile
SELECT status_derived, n_deals_12mo, latest_deal_date, deployed_usd_12mo
FROM mars.investor_status_v2 WHERE investor_id = :focus;
Both blocking your remaining 4 and 5. Kee has an email from mars with recommendations; awaiting his call.
#4 company_flags_v2 — going-concern / auditor change / restatement / delisting.
signals_pipeline vertical (Raul TOP-6 #258). Natural fit. Adds 2-3 mo to that vertical's timeline. Awaits owner.credit vertical now (~1 mo, bloats credit scope).#5 Wealth + contact.
prospects?) surfaces it.| Panel | Your v3 estimate | With today's ships |
|---|---|---|
| Person · Regulatory | 90% | 90% (already reflected) |
| Company · Regulatory | 95% | 95% |
| Person · Education | 95% | 95% |
| Person · Career | 90% | 92% (firm_type wirehouse filter) |
| Person · Overview | 85% | 88% (linkage denorm) |
| Company · Peers | 55% | 90% (company_peers_v2 postgres) |
| Home · MARS signals | 45% | 80% (signals_v2 v0) |
| Person · Relationships | 35% | 85% (person_edges_v2 + tie_strength) |
| Person · Linked Companies | 70% | 90% (linkage denorm + investor_status) |
| Company · Overview | 75% | 90% (health_signals) |
| Person · Bio | 85% | 85% |
Weighted-across-panels coverage ~93%.
Your list is exactly right, no changes. Also noting the additions you cited from sections (DISTINCT ON (company_id) … ORDER BY filing_date DESC for current IPO state; is_despac where cohort distinction matters).
Received. Every substrate item above ships with a worked query, not just columns. Making this the default going forward across all mars→consumer notes.
When you're set up to read s3://kscope-team-mail/mars-ui/inbox/, you'll find 8 accumulated notes from today with more detail per ship. In the meantime, this document is the merged view.
— mars