Entity Relationship Diagrams¶

graph TB subgraph "Core (3 tables)" U[USERS] UP[USER_PREFERENCES] SUB[SUBSCRIPTIONS] end subgraph "Deals (5 tables)" P[PROPERTIES] D[DEALS] DA[DEAL_ANALYSES] PH[PHOTOS] C[COMPS] end subgraph "AI - Cameron Unique (3 tables)" DS[DEAL_SCORES] SE[SCORE_EXPLANATIONS] VM[VIDEO_MODULES] end subgraph "Estimation (3 tables)" SOW[SCOPE_OF_WORK] SLI[SOW_LINE_ITEMS] PDB[PRICING_DATABASE] end subgraph "Portfolio (5 tables)" PORT[PORTFOLIOS] RP[RENTAL_PROPERTIES] TEN[TENANTS] INC[INCOME_RECORDS] EXP[EXPENSE_RECORDS] end subgraph "Capital - PFOS (2 tables)" CR[CAPITAL_RECORDS] CD[CAPITAL_DEPLOYMENTS] end subgraph "CRM (2 tables)" CON[CONTACTS] CI[CONTACT_INTERACTIONS] end U --> UP U --> SUB U --> P U --> D U --> CR U --> CON P --> D D --> DA D --> DS D --> SOW DS --> SE SE --> VM SOW --> SLI SLI --> PDB PORT --> RP RP --> TEN RP --> INC RP --> EXP CR --> CD CON --> CI style DS fill:#22c55e,color:#fff style SE fill:#22c55e,color:#fff style VM fill:#22c55e,color:#fff style CR fill:#22c55e,color:#fff style CD fill:#22c55e,color:#fff

erDiagram USERS { uuid id PK string email UK string password_hash string first_name string last_name string phone string company_name timestamp created_at timestamp last_login_at boolean is_active string stripe_customer_id string subscription_tier } USER_PREFERENCES { uuid user_id PK string default_market decimal target_coc_return decimal min_cash_flow_per_door decimal max_arv decimal max_ltv_percent decimal min_flip_profit jsonb preferred_strategies jsonb notification_settings } SUBSCRIPTIONS { uuid id PK uuid user_id FK string stripe_subscription_id UK string tier string status timestamp current_period_start timestamp current_period_end boolean cancel_at_period_end } USERS ||--|| USER_PREFERENCES : has USERS ||--o{ SUBSCRIPTIONS : subscribes

erDiagram PROPERTIES { uuid id PK uuid user_id FK string address_line1 string city string state string zip_code string property_type int bedrooms decimal bathrooms int sqft int year_built decimal current_value_estimate decimal arv_estimate string status string source } DEALS { uuid id PK uuid user_id FK uuid property_id FK string deal_name string strategy string status decimal asking_price decimal offer_price decimal purchase_price decimal arv decimal rehab_estimate decimal mao decimal all_in_cost decimal expected_profit uuid deal_score_id FK } DEAL_ANALYSES { uuid id PK uuid deal_id FK string analysis_name string analysis_type decimal purchase_price decimal arv decimal rehab_cost decimal net_profit decimal roi_percent decimal monthly_rent decimal cash_on_cash_return decimal cap_rate } PHOTOS { uuid id PK uuid property_id FK string url string category string caption boolean is_before timestamp uploaded_at } COMPS { uuid id PK uuid property_id FK string address decimal sale_price date sale_date int sqft int bedrooms decimal bathrooms decimal price_per_sqft decimal distance_miles } PROPERTIES ||--o{ DEALS : involves PROPERTIES ||--o{ PHOTOS : has PROPERTIES ||--o{ COMPS : has DEALS ||--o{ DEAL_ANALYSES : has

erDiagram DEAL_SCORES { uuid id PK uuid deal_id FK string overall_score decimal confidence_level string arv_score string rehab_score string market_score string profit_score string risk_score jsonb score_breakdown timestamp calculated_at } SCORE_EXPLANATIONS { uuid id PK uuid deal_score_id FK string category string score text explanation text recommendation uuid video_module_id FK int video_timestamp_seconds } VIDEO_MODULES { uuid id PK uuid methodology_id FK string title int module_number text description int duration_seconds string video_url text transcript string embedding_id jsonb topics } DEAL_SCORES ||--o{ SCORE_EXPLANATIONS : has SCORE_EXPLANATIONS }o--|| VIDEO_MODULES : links_to

flowchart LR S[Score: YELLOW
ARV Risk] --> E[Explanation:
ARV 15% above comps] E --> V[Video Link:
Module 4 @ 12:35] V --> U[User watches
methodology]

erDiagram SCOPE_OF_WORK { uuid id PK uuid deal_id FK uuid property_id FK int version string status decimal total_estimate decimal total_actual int estimated_days int actual_days timestamp created_at } SOW_LINE_ITEMS { uuid id PK uuid sow_id FK string category string subcategory text description decimal quantity string unit decimal unit_price decimal estimated_cost decimal actual_cost uuid contractor_id FK string status } PRICING_DATABASE { uuid id PK string category string subcategory string item_name string unit decimal min_price decimal max_price decimal typical_price string market uuid user_id FK string source } SCOPE_OF_WORK ||--o{ SOW_LINE_ITEMS : contains SOW_LINE_ITEMS }|--|| PRICING_DATABASE : uses

erDiagram PORTFOLIOS { uuid id PK uuid user_id FK string name text description timestamp created_at } RENTAL_PROPERTIES { uuid id PK uuid portfolio_id FK uuid property_id FK date acquisition_date decimal acquisition_price decimal current_value decimal monthly_rent decimal monthly_cash_flow decimal cap_rate decimal coc_return string status } TENANTS { uuid id PK uuid rental_property_id FK string first_name string last_name string email string phone date lease_start date lease_end decimal monthly_rent decimal security_deposit string status } INCOME_RECORDS { uuid id PK uuid rental_property_id FK uuid tenant_id FK date record_date decimal amount string income_type text notes } EXPENSE_RECORDS { uuid id PK uuid rental_property_id FK date record_date decimal amount string expense_type string vendor boolean is_recurring text notes } PORTFOLIOS ||--o{ RENTAL_PROPERTIES : contains RENTAL_PROPERTIES ||--o{ TENANTS : has RENTAL_PROPERTIES ||--o{ INCOME_RECORDS : tracks RENTAL_PROPERTIES ||--o{ EXPENSE_RECORDS : tracks

erDiagram CAPITAL_RECORDS { uuid id PK uuid user_id FK date record_date decimal total_capital decimal available_capital decimal deployed_capital decimal pending_capital decimal reserved_capital decimal total_debt decimal monthly_debt_payments decimal monthly_income decimal dti_ratio } CAPITAL_DEPLOYMENTS { uuid id PK uuid capital_record_id FK uuid deal_id FK string description decimal amount string deployment_type string source date deployed_date date expected_return_date decimal expected_return_amount string status } USERS ||--o{ CAPITAL_RECORDS : tracks CAPITAL_RECORDS ||--o{ CAPITAL_DEPLOYMENTS : has

erDiagram CONTACTS { uuid id PK uuid user_id FK string contact_type string first_name string last_name string company_name string email string phone string address jsonb contractor_specialty string lender_type decimal max_ltv decimal typical_points jsonb tags int rating string status text notes } CONTACT_INTERACTIONS { uuid id PK uuid contact_id FK uuid user_id FK timestamp interaction_date string interaction_type string direction string subject text content string outcome uuid deal_id FK } CONTACTS ||--o{ CONTACT_INTERACTIONS : logs

sequenceDiagram participant U as User participant F as Frontend participant A as API participant S as Scorer participant Q as Qdrant participant C as Claude participant DB as PostgreSQL U->>F: Submit deal for analysis F->>A: POST /deals/score A->>DB: Fetch deal data A->>S: Request scoring S->>Q: Get similar deals (RAG) Q-->>S: Context chunks S->>C: Score with methodology C-->>S: GREEN/YELLOW/RED + Explanation S->>DB: Save score + explanations S->>A: Return score with video links A->>F: Display results F->>U: Show score + "Learn Why" links

-- Example: Check if user can access Portfolio features
SELECT
    CASE
        WHEN s.tier IN ('pro', 'team') THEN true
        ELSE false
    END as has_portfolio_access
FROM users u
JOIN subscriptions s ON u.id = s.user_id
WHERE u.id = :user_id
  AND s.status = 'active';

MAO = (ARV × 0.7) - REHAB - BSCCC - HOLDCO - SSCC

Where:
  ARV     = After Repair Value
  REHAB   = Renovation costs (from SOW)
  BSCCC   = Buying-Side Closing Costs (~2-3%)
  HOLDCO  = Holding Costs (months × carrying costs)
  SSCC    = Selling-Side Closing Costs (~8-10%)