Epic 3: Automated Returns Reconciliation

Status: ✅ Complete (January 2026)

Overview

Automated system that compares Sable vs BTIG monthly returns and flags discrepancies exceeding 10 bps for human review.

Workflow

┌─────────────────────────────────────────────────────────────┐
│                    AUTOMATED WORKFLOW                        │
│                                                             │
│   Daily/Weekly Sync                                         │
│         ↓                                                   │
│   gold.v_returns_monthly_summary                            │
│   (aggregates daily returns by month)                       │
│         ↓                                                   │
│   ┌─────────────────┐     ┌─────────────────┐              │
│   │  ≤ 10 bps      │     │  > 10 bps       │              │
│   │  Auto-approve ✓│     │  Flag for review│              │
│   └─────────────────┘     └────────┬────────┘              │
│                                    ↓                        │
│                           sable.returns_discrepancy_reviews │
│                           (creates pending_review record)   │
│                                    ↓                        │
│                           Dashboard Widget                  │
│                           (admin reviews & resolves)        │
└─────────────────────────────────────────────────────────────┘

Components

1. Database Schema

Table: sable.returns_discrepancy_reviews

Tracks discrepancies requiring human review.

CREATE TABLE sable.returns_discrepancy_reviews (
  id UUID PRIMARY KEY,
  org_id UUID NOT NULL,
  entity_id INTEGER NOT NULL,
  review_month DATE NOT NULL,  -- First day of month

  -- Snapshot metrics
  avg_daily_diff_bps NUMERIC,
  max_daily_diff_bps NUMERIC,
  sable_monthly_return NUMERIC,
  btig_monthly_return NUMERIC,
  monthly_diff_bps NUMERIC,
  days_compared INTEGER,

  -- Review status
  status TEXT DEFAULT 'pending_review'
    CHECK (status IN ('pending_review', 'approved', 'acknowledged', 'ignored')),

  -- Review details
  reviewed_by TEXT,
  reviewed_at TIMESTAMPTZ,
  review_notes TEXT,

  UNIQUE (org_id, entity_id, review_month)
);

Status Values:

Status	Meaning
pending_review	Awaiting admin action
approved	Difference accepted as valid
acknowledged	Issue noted but accepted
ignored	False positive or data issue

2. Views

gold.v_returns_monthly_summary

Aggregates daily returns by month with discrepancy statistics.

Column	Description
entity_name	Entity name
month	First day of month
sable_return_pct	Sum of Sable daily returns
btig_return_pct	Sum of BTIG daily returns
monthly_diff_bps	Difference in basis points
avg_abs_diff_bps	Average daily
max_abs_diff_bps	Maximum daily
rmse_bps	Root mean square error
exceeds_threshold	TRUE if > 10 bps
reconciliation_status	'auto_approved' or 'needs_review'

gold.v_returns_discrepancies

Shows only items exceeding threshold with review status.

3. Sync Function

SELECT * FROM sable.sync_return_discrepancies();
-- Returns: (inserted_count, existing_count)

Call this function daily/weekly to sync new discrepancies to the review table.

4. API Endpoints

Method	Endpoint	Description
GET	/api/returns/discrepancies	List discrepancies
POST	/api/returns/discrepancies	Trigger sync
PATCH	/api/returns/discrepancies/[id]	Update status
GET	/api/returns/discrepancies/[id]	Get single item
GET	/api/returns/monthly-summary	Monthly summaries

Query Parameters:

• org_id (required)
• status - Filter by review status
• entity_id - Filter by entity
• include_resolved - Include approved/acknowledged items

5. Dashboard Widget

Location: /protected/btig/rec → "Returns" tab

Component: ReturnsDiscrepancyWidget

Features:

• Shows pending discrepancy count with badge
• Table with entity, month, returns, diff
• Color-coded severity (>50 bps red, >20 bps amber)
• Dropdown actions: Approve, Acknowledge, Ignore, Reset
• Sync button to refresh data

Current Data

As of January 2026:

• 18 historical discrepancies synced to review queue
• Most from 2022-2025 period
• Recent months mostly within threshold

Monthly Summary (Sample)

Entity	Month	Sable Return	BTIG Return	Diff (bps)	Status
Anthracite Holdings LP	Jan 2026	0.28%	0.13%	15.11	needs_review
Anthracite Holdings LP	Dec 2025	0.77%	0.80%	-2.91	auto_approved
Anthracite Holdings LP	Nov 2025	0.46%	0.63%	-16.70	needs_review
Anthracite Holdings LP	Oct 2025	0.49%	0.64%	-15.44	needs_review
Anthracite Holdings LP	Sep 2025	0.21%	0.19%	2.62	auto_approved

Pending Discrepancies

Entity	Month	Diff (bps)	Status
Anthracite Holdings LP	Jan 2026	15.11	pending_review
Anthracite Realty Partners	Dec 2025	-53.55	pending_review
Anthracite Holdings LP	Nov 2025	-16.70	pending_review
Anthracite Holdings LP	Oct 2025	-15.44	pending_review

How to Query and Access the Math

View: gold.v_returns_monthly_summary

Aggregates daily returns by month with reconciliation status.

SELECT
  entity_name,
  month,
  sable_return_pct,
  btig_return_pct,
  monthly_diff_bps,
  reconciliation_status
FROM gold.v_returns_monthly_summary
WHERE org_id = 'your-org-id'
ORDER BY month DESC;

View: gold.v_returns_discrepancies

Shows only items exceeding the 10 bps threshold.

SELECT
  entity_name,
  review_month,
  sable_monthly_return,
  btig_monthly_return,
  monthly_diff_bps,
  status,
  review_notes
FROM gold.v_returns_discrepancies
WHERE org_id = 'your-org-id'
  AND status = 'pending_review';

View: gold.v_dietz_daily

Daily return calculations with method transparency.

SELECT
  entity_name,
  trade_date,
  daily_pnl,
  begin_nav,
  capital_amount,
  daily_return,
  return_method,
  btig_return,
  diff_bps
FROM gold.v_dietz_daily
WHERE entity_id = 123
  AND trade_date >= '2025-01-01'
ORDER BY trade_date;

Key columns:

• daily_return - Calculated based on entity's return_method setting
• return_method - Shows which formula is being used
• diff_bps - Difference from BTIG in basis points

Sync Function

Manually trigger a sync of new discrepancies:

SELECT * FROM sable.sync_return_discrepancies();
-- Returns: (inserted_count, existing_count)

Loss Function (CLI)

Calculate the aggregate loss across all entities:

# Total loss (sum of squared differences)
sable returns loss --account AV7K

# Breakdown by entity/month
sable returns loss --account AV7K --breakdown

# Side-by-side comparison
sable returns compare --account AV7K --month 2025-01

API Access

From the Sable frontend:

// List discrepancies
const { data } = await supabase
  .from('returns_discrepancy_reviews')
  .select('*')
  .eq('org_id', orgId)
  .eq('status', 'pending_review');

// Monthly summary
const { data } = await supabase
  .from('v_returns_monthly_summary')
  .select('*')
  .eq('org_id', orgId)
  .order('month', { ascending: false });

Threshold Rationale

10 basis points was chosen because:

• Standard industry tolerance for return reconciliation
• Balances precision vs. practicality
• Accounts for timing differences in data sources
• Below typical investor reporting precision (1 decimal place %)

Implementation Files

File	Purpose
supabase/migrations/20260114213118_*.sql	Tables, views, function
app/api/returns/discrepancies/route.ts	List/sync endpoints
app/api/returns/discrepancies/[id]/route.ts	Update endpoint
app/api/returns/monthly-summary/route.ts	Summary endpoint
components/returns/returns-discrepancy-widget.tsx	Dashboard widget
app/protected/btig/rec/page.tsx	Returns tab integration

Related

• Epic 1: CLI Loss Function - Measuring differences
• Epic 2: Method Configuration - Per-entity toggle
• Data Lineage - Full data flow diagram