Canonical schema for the fully agentic deal lifecycle — from origination through closing — across Industrial, Multifamily, Office, Retail, and Land. Aligned to the Agent Creation Playbook, ADRs 0001–0003, and the IN-01 pilot: four-tenant RLS isolation, governed metrics via dbt MetricFlow, role-based approvals, and classification-driven tier routing. v2.1 adds the upstream Origination and Capital Markets domains.
Conflating these is the most common modeling mistake. Every table carries the first two; the third is deal data. Keeping them separate is what makes RLS, tier routing, and legal-entity reporting independently correct.
coe · mf · industrial · healthcare. The Postgres RLS key, sourced from the JWT — who may see the row. Never trusts a client header. Enforced by OPA 8×4 (role × module × tenant).internal · confidential · restricted_mnpi. A label that routes the handling agent to a deployment tier (1 relay / 2 ACA / 3 air-gapped). It is not itself a tier.ref_entities. The legal entity holding the asset (balance-sheet LLC, fund vehicle, JV). One tenant transacts across many entities — so this is independent of tenant_id.origination_leads); a lead advances into a dealapproval_logorigination_lead_id links back). All agents read/write here; every table links via deal_id and carries tenant_id.Layered layout, left → right: the Origination funnel feeds deals (center hub) via the advance arrow; deal-domain children sit left, versioned stages and Capital Markets sit right. All connectors are orthogonal and enter deals at distinct anchor points — no crossings. Dashed red = the tenant_id RLS scope that applies to every table.
Click any table header to expand its field definitions. 🔒 RLS marks the tenant_id isolation key present on every operational table. v2 marks columns/decisions changed during the alignment pass.
| Column | Type | Required | Notes |
|---|---|---|---|
| buy_box_idPK | UUID | ✓ | |
| tenant_idFK🔒 RLS | ENUM | ✓ | → ref_tenants |
| buy_box_version | INTEGER | ✓ | Increments on revision |
| is_active | BOOLEAN | ✓ | Exactly one active version per tenant |
| asset_classesJSONB | JSONB | ✓ | e.g. ["industrial","multifamily"] |
| target_marketsJSONB | JSONB | ○ | MSAs / submarkets in scope |
| min_deal_size · max_deal_size | BIGINT | ○ | cents |
| min_cap_rate | DECIMAL(5,4) | ○ | Going-in cap-rate floor |
| target_irr | DECIMAL(5,4) | ○ | Return hurdle |
| target_hold_period_years | DECIMAL(5,2) | ○ | |
| excluded_criteriaJSONB | JSONB | ○ | Hard exclusions (flood zone, ground lease…) |
| effective_date | DATE | ✓ | When this version took effect |
| set_by | VARCHAR(255) | ✓ | Originator / IC who set it |
| data_classification | ENUM | ✓ | confidential |
| Column | Type | Required | Notes |
|---|---|---|---|
| lead_idPK | UUID | ✓ | Carried onto deals.origination_lead_id on advance |
| tenant_idFK🔒 RLS | ENUM | ✓ | → ref_tenants |
| lead_name | VARCHAR(255) | ✓ | e.g. "Eastvale Logistics — off-market" |
| asset_classENUM | ENUM | ○ | Same enum as deals; nullable early |
| lead_stageENUM | ENUM | ✓ | sourced, scouting, screening, advanced, passed, dead |
| lead_statusENUM | ENUM | ✓ | active, on_hold, advanced, rejected |
| source_typeENUM | ENUM | ✓ | off_market_scan, broker, referral, inbound, direct, relationship |
| street_address · city · state · submarket · msa | VARCHAR | ○ | May be approximate / undisclosed early |
| estimated_deal_size | BIGINT | ○ | cents — rough early sizing |
| buy_box_idFK | UUID | ○ | → buy_box_criteria · version scored against |
| buy_box_fit_score | DECIMAL(5,2) | ○ | 0–100, set by RE-OffMarket-Scout |
| pipeline_health_score | DECIMAL(5,2) | ○ | 0–100, set by RE-Pipeline-Scout |
| next_best_action | VARCHAR(500) | ○ | RE-Pipeline-Scout recommendation (advisory) |
| assigned_originator | VARCHAR(255) | ○ | Owning originator / acquisitions lead |
| broker_name · broker_contact_email | VARCHAR(255) | ○ | If broker-sourced |
| sourced_date | DATE | ✓ | When the lead first appeared |
| advanced_date | DATE | ○ | When promoted to a deal |
| pass_reason | TEXT | ○ | Why killed — kept for funnel analytics |
| data_classification | ENUM | ✓ | confidential |
| Column | Type | Required | Notes |
|---|---|---|---|
| screening_idPK | UUID | ✓ | |
| tenant_id · lead_idFK🔒 | ENUM / UUID | ✓ | → ref_tenants · origination_leads |
| screening_version | INTEGER | ✓ | Increments on re-screen |
| screening_statusENUM | ENUM | ✓ | draft, complete, advance_recommended, pass_recommended |
| headline_economicsJSONB | JSONB | ○ | Early figures as captured — inputs, not governed |
| market_fit_notes | TEXT | ○ | RE-PreScreen-Writer narrative |
| risk_flagsJSONB | JSONB | ○ | Early risk flags |
| avm_value_estimate | BIGINT | ○ | Green Street AVM point estimate, cents (source = green_street_avm) |
| avm_value_low · avm_value_high | BIGINT | ○ | AVM range, cents |
| avm_as_of_date | DATE | ○ | |
| screening_memo_doc_idFK | UUID | ○ | → deal_documents · generated PDF/PPTX memo |
| recommendationENUM | ENUM | ○ | advance, pass, hold |
| data_classification | ENUM | ✓ | confidential |
| Column | Type | Required | Notes |
|---|---|---|---|
| ── lead_sources ── | |||
| source_idPK | UUID | ✓ | |
| tenant_id · lead_idFK🔒 | ENUM / UUID | ✓ | → ref_tenants · origination_leads |
| source_agent | VARCHAR(100) | ○ | RE-OffMarket-Scout, RE-Broker-CRM |
| source_channelENUM | ENUM | ✓ | crexi, green_street, web_signal, broker_email, crm, referral, manual |
| signal_payloadJSONB | JSONB | ○ | Raw signal that triggered the lead |
| signal_strength | DECIMAL(5,2) | ○ | Agent confidence in the signal |
| ── lead_activity ── | |||
| activity_idPK | UUID | ✓ | |
| tenant_id · lead_idFK🔒 | ENUM / UUID | ✓ | → ref_tenants · origination_leads |
| activity_typeENUM | ENUM | ✓ | outreach, call, email, meeting, site_visit, note, status_change |
| activity_date · actor_id | TIMESTAMP / VARCHAR | ✓ | When + who (user or agent) |
| summary | TEXT | ○ | What happened |
| suggested_followup | VARCHAR(500) | ○ | RE-Broker-CRM / Pipeline-Scout suggestion (advisory) |
| Column | Type | Required | Notes |
|---|---|---|---|
| tenant_idPKENUM | ENUM | ✓ | coe, mf, industrial, healthcare |
| tenant_display_name | VARCHAR(100) | ✓ | "Center of Excellence", etc. |
| key_vault_ref | VARCHAR(100) | ✓ | kv-coe / kv-mf / kv-ind / kv-hc |
| postgres_schema | VARCHAR(100) | ✓ | Per-tenant RLS schema name |
| created_at | TIMESTAMP | ✓ |
| Column | Type | Required | Notes |
|---|---|---|---|
| entity_idPK | UUID | ✓ | |
| tenant_idFK🔒 RLS | ENUM | ✓ | → ref_tenants · owning tenant |
| entity_legal_name | VARCHAR(255) | ✓ | "Leon Industrial LLC", "VaLeon Fund III LP" |
| entity_typeENUM | ENUM | ✓ | balance_sheet, fund_vehicle, jv, construction, corporate |
| is_active | BOOLEAN | ✓ | |
| created_at | TIMESTAMP | ✓ |
| Column | Type | Required | Notes |
|---|---|---|---|
| deal_idPK | UUID | ✓ | Primary key, auto-generated |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants · isolation key, from JWT |
| deal_name | VARCHAR(255) | ✓ | e.g. "Beaumont Pointe - IE East" |
| asset_classENUM | ENUM | ✓ | industrial, multifamily, office, retail, land, mixed_use |
| deal_stageENUM | ENUM | ✓ | intake → underwriting → legal_review → compliance_screen → treasury_review → closing → closed → dead |
| deal_statusENUM | ENUM | ✓ | active, on_hold, approved, rejected, closed |
| broker_name | VARCHAR(255) | ○ | Originating broker or firm |
| intake_date | DATE | ✓ | Date OM or deal data was received |
| target_close_date | DATE | ○ | |
| actual_close_date | DATE | ○ | Populated at closing |
| deal_sourceENUM | ENUM | ○ | broker, direct, referral, off_market |
| priority_tierENUM | ENUM | ○ | high, medium, low |
| data_classificationSENSITIVITY | ENUM | ✓ | internal, confidential, restricted_mnpi → drives deployment-tier routing |
| entity_owner_idFKv2 | UUID | ○ | → ref_entities · legal owner (was free-text enum in v1; nullable until treasury sets it) |
| created_by | VARCHAR(255) | ✓ | User or agent ID |
| created_at | TIMESTAMP | ✓ | Auto |
| updated_at | TIMESTAMP | ✓ | Auto |
| notes | TEXT | ○ | Free-form deal notes |
| Column | Type | Required | Notes |
|---|---|---|---|
| property_idPK | UUID | ✓ | |
| deal_idFK | UUID | ✓ | → deals |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants |
| street_address | VARCHAR(255) | ✓ | |
| city, state, zip | VARCHAR | ✓ | |
| submarket | VARCHAR(100) | ○ | e.g. "Inland Empire East" |
| msa | VARCHAR(100) | ○ | Metro Statistical Area |
| parcel_number | VARCHAR(100) | ○ | APN |
| zoning | VARCHAR(100) | ○ | |
| land_area_acres | DECIMAL(10,4) | ○ | |
| gross_building_area_sqft | INTEGER | ○ | GBA |
| net_rentable_area_sqft | INTEGER | ○ | NRA |
| year_built | INTEGER | ○ | |
| number_of_units | INTEGER | ○ | Multifamily specific |
| clear_height_ft | DECIMAL(5,2) | ○ | Industrial specific |
| dock_doors | INTEGER | ○ | Industrial specific |
| drive_in_doors | INTEGER | ○ | Industrial specific |
| property_classENUM | ENUM | ○ | class_a, class_b, class_c |
| amenitiesJSONB | JSONB | ○ | Flexible amenity list |
| extraction_confidence | DECIMAL(4,3) | ○ | 0.000–1.000, set by intake agent |
| extraction_sourceENUM | ENUM | ○ | om_extracted, broker_provided, manual_entry |
| data_classification | ENUM | ✓ | Typically internal |
| Column | Type | Required | Notes |
|---|---|---|---|
| snapshot_idPK | UUID | ✓ | |
| deal_idFK | UUID | ✓ | → deals |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants |
| asking_price | BIGINT | ○ | USD cents — avoids float precision issues |
| price_per_sqft | DECIMAL(10,2) | ○ | |
| stated_cap_rate | DECIMAL(5,4) | ○ | As printed in OM. e.g. 0.0550 = 5.50% |
| stated_noi | BIGINT | ○ | Broker-stated T12 NOI, cents |
| gross_potential_rent | BIGINT | ○ | cents |
| effective_gross_income | BIGINT | ○ | cents |
| total_operating_expenses | BIGINT | ○ | cents |
| occupancy_rate_stated | DECIMAL(5,4) | ○ | e.g. 0.9500 = 95% |
| wale_years | DECIMAL(5,2) | ○ | Weighted Average Lease Expiry |
| debt_assumption_amount | BIGINT | ○ | cents, if assumable debt exists |
| pro_forma_noi | BIGINT | ○ | Broker's projected NOI |
| pro_forma_cap_rate | DECIMAL(5,4) | ○ | Broker-stated |
| snapshot_date | DATE | ✓ | Date of OM financials |
| extraction_confidence | DECIMAL(4,3) | ○ | 0.000–1.000 |
| data_classification | ENUM | ✓ | confidential |
| Column | Type | Required | Notes |
|---|---|---|---|
| rentroll_idPKv2 | UUID | ✓ | Renamed PK (was tenant_id in v1 — collided with RLS key) |
| deal_idFK | UUID | ✓ | → deals |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants · the isolation tenant, NOT the lessee |
| lessee_namev2 | VARCHAR(255) | ✓ | Renamed from tenant_name — disambiguates RE lessee from isolation tenant |
| tenant_typeENUM | ENUM | ○ | anchor, major, inline, ground_floor, office |
| credit_rating | VARCHAR(20) | ○ | S&P / Moody's if available |
| leased_sqft | INTEGER | ○ | |
| lease_start_date | DATE | ○ | |
| lease_expiry_date | DATE | ○ | |
| lease_typeENUM | ENUM | ○ | nnn, gross, modified_gross, absolute_net |
| base_rent_annual | BIGINT | ○ | cents |
| base_rent_per_sqft | DECIMAL(8,2) | ○ | |
| rent_escalation_rate | DECIMAL(5,4) | ○ | Annual escalation |
| rent_escalation_typeENUM | ENUM | ○ | fixed, cpi, stepped |
| renewal_optionsJSONB | JSONB | ○ | Option terms and notice periods |
| tenant_improvement_allowance | BIGINT | ○ | cents |
| personal_guarantee | BOOLEAN | ○ | |
| termination_option | BOOLEAN | ○ | |
| extraction_confidence | DECIMAL(4,3) | ○ | Set by intake agent |
| data_classification | ENUM | ✓ | confidential |
| Column | Type | Required | Notes |
|---|---|---|---|
| underwriting_idPK | UUID | ✓ | |
| deal_idFK | UUID | ✓ | → deals |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants |
| underwriting_version | INTEGER | ✓ | Increments on revision — never delete prior versions |
| underwriting_statusENUM | ENUM | ✓ | draft, in_review, approved, rejected |
| underwritten_by | VARCHAR(255) | ✓ | Agent ID or user |
| purchase_price_lcg | BIGINT | ○ | LCG's target price, cents (input) |
| vacancy_assumption | DECIMAL(5,4) | ○ | input |
| management_fee_pct | DECIMAL(5,4) | ○ | input |
| market_rent_per_sqft | DECIMAL(8,2) | ○ | LCG's market-rent view (input) |
| rent_growth_assumption | DECIMAL(5,4) | ○ | input |
| expense_growth_assumption | DECIMAL(5,4) | ○ | input |
| hold_period_years | DECIMAL(5,2) | ○ | input |
| exit_cap_rate_assumption | DECIMAL(5,4) | ○ | Assumed at disposition (input) |
| loan_to_value_assumption | DECIMAL(5,4) | ○ | Target LTV (input) |
| capex_year1 | BIGINT | ○ | cents (input) |
| capex_total | BIGINT | ○ | cents (input) |
| risk_flagsJSONB | JSONB | ○ | Agent-identified risks |
| agent_commentary | TEXT | ○ | Underwriting agent narrative |
| data_classificationMNPI | ENUM | ✓ | confidential or restricted_mnpi |
uw_metric_snapshot table marked source = 'metricflow' — read-only, MetricFlow remains source of truth.
| Column | Type | Required | Notes |
|---|---|---|---|
| legal_review_idPK | UUID | ✓ | |
| deal_idFK | UUID | ✓ | → deals |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants |
| review_version | INTEGER | ✓ | Increments on revision |
| review_statusENUM | ENUM | ✓ | pending, in_review, cleared, hold, rejected |
| title_statusENUM | ENUM | ○ | clear, exceptions_noted, hold, pending |
| title_exceptionsJSONB | JSONB | ○ | Array of title exception descriptions |
| entity_structure_recommended | VARCHAR(255) | ○ | e.g. "Delaware LLC → TX Operating Entity" |
| purchase_agreement_statusENUM | ENUM | ○ | not_received, received, reviewed, redlined, executed |
| environmental_statusENUM | ENUM | ○ | phase1_required, phase1_complete, phase2_required, clear |
| earnest_money_amount | BIGINT | ○ | cents |
| earnest_money_hard_date | DATE | ○ | When EM goes hard |
| lease_review_flagsJSONB | JSONB | ○ | Issues found in lease abstracts |
| open_itemsJSONB | JSONB | ○ | Array of unresolved legal items |
| privileged_content_flagMNPI | BOOLEAN | ✓ | true → data_classification = restricted_mnpi → Tier 3 |
| attorney_sign_off | VARCHAR(255) | ○ | Signing attorney name (decision recorded in approval_log) |
| Column | Type | Required | Notes |
|---|---|---|---|
| compliance_idPK | UUID | ✓ | |
| deal_idFK | UUID | ✓ | → deals |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants |
| screen_version | INTEGER | ✓ | |
| screen_statusENUM | ENUM | ✓ | pending, in_review, cleared, hold, escalated |
| mnpi_flagMNPI | BOOLEAN | ✓ | true → classification = restricted_mnpi → Tier 3 |
| mnpi_description | TEXT | ○ | Populated if flagged |
| lp_conflict_flag | BOOLEAN | ✓ | LP conflict of interest detected |
| sec_finra_flag | BOOLEAN | ✓ | SEC/FINRA regulatory concern |
| sanctions_screen_statusENUM | ENUM | ○ | pass, fail, pending |
| aml_screen_statusENUM | ENUM | ○ | pass, fail, pending |
| investment_committee_required | BOOLEAN | ✓ | IC approval needed |
| data_classificationMNPI | ENUM | ✓ | restricted_mnpi when mnpi_flag |
| Column | Type | Required | Notes |
|---|---|---|---|
| treasury_idPK | UUID | ✓ | |
| deal_idFK | UUID | ✓ | → deals |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants |
| entity_owner_idFKv2 | UUID | ○ | → ref_entities · closing legal entity (replaces free-text) |
| decision_version | INTEGER | ✓ | |
| total_capitalization | BIGINT | ○ | Total deal cost, cents |
| equity_required | BIGINT | ○ | cents |
| debt_required | BIGINT | ○ | cents |
| equity_sourceENUM | ENUM | ○ | lcg_balance_sheet, fund_vehicle, lp_capital, jv_partner, mixed |
| fund_vehicle_name | VARCHAR(255) | ○ | Display label only — entity is entity_owner_id |
| lender_name | VARCHAR(255) | ○ | |
| loan_typeENUM | ENUM | ○ | permanent, bridge, construction, mezzanine |
| interest_rate | DECIMAL(5,4) | ○ | |
| funding_scheduleJSONB | JSONB | ○ | Milestone-based funding timeline |
| wire_instructions_verified | BOOLEAN | ✓ | Default false — must be explicitly set |
| data_classificationMNPI | ENUM | ✓ | Almost always restricted_mnpi → Tier 3 |
| Column | Type | Required | Notes |
|---|---|---|---|
| closing_idPK | UUID | ✓ | |
| deal_idFK | UUID | ✓ | → deals |
| tenant_idFK🔒 RLSv2 | ENUM | ✓ | → ref_tenants |
| closing_statusENUM | ENUM | ✓ | pre_closing, closing_in_progress, closed, failed |
| scheduled_closing_date | DATE | ○ | |
| actual_closing_date | DATE | ○ | |
| purchase_agreement_executed | BOOLEAN | ✓ | Default false |
| title_insurance_bound | BOOLEAN | ✓ | Default false |
| environmental_clear | BOOLEAN | ✓ | Default false |
| loan_documents_executed | BOOLEAN | ✓ | Default false |
| deed_recorded | BOOLEAN | ✓ | Default false |
| funds_wired | BOOLEAN | ✓ | Default false |
| wire_confirmation_number | VARCHAR(255) | ○ | |
| asset_management_record_created | BOOLEAN | ✓ | Handoff to Asset Management Domain |
| post_closing_itemsJSONB | JSONB | ○ | Outstanding items after close |
| data_classification | ENUM | ✓ |
| Column | Type | Required | Notes |
|---|---|---|---|
| document_idPK | UUID | ✓ | deal_documents |
| deal_id · tenant_idFK🔒 | UUID / ENUM | ✓ | → deals · ref_tenants |
| document_typeENUM | ENUM | ✓ | offering_memorandum, rent_roll, broker_opinion_value, lease_abstract, title_report, environmental_report, purchase_agreement, loan_document, other |
| file_path | VARCHAR(1000) | ✓ | Azure Blob / ADLS path |
| processing_statusENUM | ENUM | ✓ | pending, processing, extracted, failed, needs_review |
| ── extraction_results (1:1 with document) ── | |||
| extraction_idPK | UUID | ✓ | |
| document_id · deal_idFK | UUID | ✓ | → deal_documents · deals |
| extraction_model | VARCHAR(100) | ✓ | e.g. claude-sonnet-4-6 |
| extracted_dataJSONB | JSONB | ✓ | Full structured extraction output |
| overall_confidence | DECIMAL(4,3) | ✓ | 0.000–1.000 |
| low_confidence_fieldsJSONB | JSONB | ○ | Fields with confidence < 0.80 |
| human_review_required | BOOLEAN | ✓ | Flagged if confidence < 0.75 |
| corrections_appliedJSONB | JSONB | ○ | Human corrections to extraction |
| Column | Type | Required | Notes |
|---|---|---|---|
| ── coinvestor_prospects (RE-CoInvest-Matcher AI-11) ── | |||
| prospect_idPK | UUID | ✓ | |
| tenant_id · deal_idFK🔒 | ENUM / UUID | ✓ | → ref_tenants · deals |
| coinvestor_name · coinvestor_type | VARCHAR / ENUM | ✓ | institutional, family_office, hnw, fund_of_funds, strategic |
| appetite_match_score | DECIMAL(5,2) | ○ | 0–100 vs deal profile (FinTrx/MadeMarket) |
| outreach_draft | TEXT | ○ | Never auto-sent |
| outreach_statusENUM | ENUM | ✓ | drafted, sent, responded, declined, committed |
| data_classification→ MNPI | ENUM | ✓ | confidential → restricted_mnpi if LP terms attached |
| ── leverage_matches (RE-Leverage-Matcher AI-12) ── | |||
| match_idPK | UUID | ✓ | |
| tenant_id · deal_idFK🔒 | ENUM / UUID | ✓ | → ref_tenants · deals |
| lender_name · lender_type | VARCHAR / ENUM | ✓ | bank, debt_fund, agency, clo_desk, life_co, bridge |
| loan_productENUM | ENUM | ○ | permanent, bridge, construction, mezzanine, back_leverage |
| criteria_fit_score | DECIMAL(5,2) | ○ | 0–100 vs deal structure |
| indicative_termsJSONB | JSONB | ○ | Rate/LTV/term as indicated (inputs, not governed) |
| term_sheet_request_draft | TEXT | ○ | Drafted request letter — team sends |
| match_statusENUM | ENUM | ✓ | identified, requested, term_sheet_received, selected, declined |
| ── capmarkets_intel (RE-CapMarkets-Watch AI-13) ── | |||
| intel_idPK | UUID | ✓ | |
| tenant_idFK | ENUM | ○ | Nullable / shared — market data not tenant-private |
| deal_idFK | UUID | ○ | Set only when tied to a deal's financing |
| intel_type · headlineENUM | ENUM / VARCHAR | ✓ | rate_move, spread_change, agency_pricing, execution_window, weekly_brief |
| is_act_now_alert | BOOLEAN | ✓ | Threshold-triggered execution alert |
| source · as_of_date | VARCHAR / DATE | ✓ | bloomberg, web_feed, internal_rate_history |
| Column | Type | Required | Notes |
|---|---|---|---|
| audit_idPK | UUID | ✓ | audit_trail |
| deal_id · tenant_idFK🔒 | UUID / ENUM | ○ | deal_id nullable for system events |
| entity_type · entity_id | VARCHAR / UUID | ✓ | Table name + PK of modified record |
| actionENUM | ENUM | ✓ | create, update, delete, approve, reject, flag, read |
| actor_typeENUM | ENUM | ✓ | agent, human, system |
| old_value · new_value | TEXT | ○ | Before/after on update |
| worm_replicated_atv2 | TIMESTAMP | ○ | Set when flushed to ADLS Gen2 Delta |
| timestamp | TIMESTAMP | ✓ | Immutable |
| ── approval_log (single home for all sign-offs) ── | |||
| approval_idPK | UUID | ✓ | |
| deal_id · tenant_idFK🔒 | UUID / ENUM | ✓ | → deals · ref_tenants |
| approval_stageENUM | ENUM | ✓ | underwriting, legal, compliance, treasury, closing |
| approver_roleFKv2 | ENUM | ✓ | → ref_roles (COMP, TX, RE-CEO…). Was free-text in v1 |
| approver_userv2 | VARCHAR(255) | ○ | Resolved at sign-off — who acted, not schema-bound |
| approval_statusENUM | ENUM | ✓ | pending, approved, rejected, delegated |
| conditionsJSONB | JSONB | ○ | Conditions attached to approval |
Aligned to the six archetypes. All agents write audit_trail; sign-offs are rows in approval_log. The Underwriting Agent writes inputs only — governed metrics resolve through MetricFlow. Gold-icon agents work the origination funnel; green-icon are Capital Markets.
data_classification is a column; the tier is where the handling agent runs. The mapping below is the routing rule — not a redefinition of the column. (Tier 3 = air-gapped private LLM, the one runtime Foundry cannot serve — ADR 0001.)
| Data Type | Classification | Tier | Runtime |
|---|---|---|---|
| Property basics, broker info, market comps, submarket trends | internal | Tier 1 / 2 | Claude Enterprise relay / API |
| Capital-markets intel (rates/spreads, public market data) | internal | Tier 1 / 2 | Claude Enterprise relay / API |
| Origination leads, buy-box, screening, lead activity | confidential | Tier 2 | Claude API + LangGraph on Azure Container Apps |
| Co-investor prospects, leverage matches | confidential | Tier 2 → 3 | Tier 3 if LP terms attached |
| Financial snapshot (OM), rent roll, underwriting inputs | confidential | Tier 2 | Claude API + LangGraph on Azure Container Apps |
| LP capital structure, fund-vehicle terms | restricted_mnpi | Tier 3 | Air-gapped private LLM, mTLS |
| Privileged legal content (privileged_content_flag) | restricted_mnpi | Tier 3 | Air-gapped — no external API |
| Compliance MNPI flags (mnpi_flag) | restricted_mnpi | Tier 3 | Air-gapped — no external API |
| Treasury / wire details | restricted_mnpi | Tier 3 | Air-gapped — Farhan + Matt exec gate |
| Audit trail | inherited | Tier 2 | Append-only, WORM → ADLS Gen2 Delta (17a-4) |
Postgres with per-tenant RLS schemas is the transactional source of truth. DuckDB provides read-side analytics views over Postgres — an accelerator, not a parallel SoR. dbt handles migrations, one model group per domain.dbt MetricFlow Semantic API is the single source for NOI, DSCR, LTV, cap rate, IRR, EM, waterfall yield. No table stores these as authoritative. WrenAI resolves governed metrics through MetricFlow (ADR 0003).tenant_id is the RLS predicate; OPA 8×4 authorizes role × module × tenant. No cross-tenant joins. Per IN-01 pilot G3: no isolation guarantee rests on a system prompt — RLS + OPA are structural.audit_trail + approval_log replicate to ADLS Gen2 Delta, WORM, SEC 17a-4. Append-only — never updated or deleted. All sign-offs live in approval_log, keyed by role.BIGINT in USD cents (e.g. $5,500,000 = 550000000) to avoid float precision issues. Rates as DECIMAL(5,4) (0.0550 = 5.50%).underwriting_assumptions, legal_review, compliance_screen, treasury_decision keep an integer version column — never delete prior versions. Extraction schema versioned separately (evolves faster).v1 predated the Agent Creation Playbook + ADRs 0001–0003 + the IN-01 pilot; v2 (rows 1–7) reconciled the model against them. v2.1 (rows 8–10) adds the upstream Origination and Capital Markets domains the original model omitted — the sourcing/scouting funnel before a deal exists.
| # | Change | Why |
|---|---|---|
| 1 | Added tenant_id (4 tenants) to every table; new ref_tenants | v1 had no isolation key; RLS can't scope rows it can't see |
| 2 | entity_owner enum → entity_owner_id FK → ref_entities | Legal ownership is a separate axis from tenancy |
| 3 | Governed metrics removed as stored columns; table holds inputs only | "No agent computes its own NOI" — metrics from MetricFlow |
| 4 | Person-named approval columns → role-based rows in approval_log | Names in schema = migration on every role change |
| 5 | Classification vs. deployment tier separated | v1 conflated a sensitivity label with a runtime tier |
| 6 | tenants → tenants_rentroll; tenant_name → lessee_name | "tenant" was overloaded (isolation vs RE lessee) |
| 7 | Named green_street_snapshots; WORM/ADLS audit replication stated | Provenance to greenstreet/; SEC 17a-4 |
| 8 | Added Origination domain — buy_box_criteria, origination_leads, lead_sources, lead_screening, lead_activity | The model began at intake; the sourcing/scouting funnel before a deal existed was unrepresented (AI-25/01/08/22, HY-10) |
| 9 | Lead → deal promotion — deals.origination_lead_id FK; leads kept separate from deals | Most leads die in the funnel; deal pipeline must not carry dead scouting noise. Advance is one-way + an approval_log gate |
| 10 | Added Capital Markets domain — coinvestor_prospects, leverage_matches, capmarkets_intel | Co-invest / back-leverage / rate-window work runs parallel to the deal lifecycle and was unmodeled (AI-11/12/13) |