Database Schema

The application uses PostgreSQL hosted on Supabase. The schema is designed with Row Level Security (RLS) enabled on all tables to ensure data privacy and secure role-based access.

Tables

public.profiles

Extends the default auth system to store user roles and additional user metadata.

Column	Type	Description
id	UUID	Primary Key, references auth.users
email	Text	User's email address (synced for Admin view)
role_id	BigInt	References roles.id (RBAC)
bookkeeping_enabled	Boolean	Feature flag for bookkeeping module
created_at	Timestamp	Creation date

public.roles & public.permissions (RBAC)

Defines the Role-Based Access Control system.

Table	Description
roles	Defines roles like 'Owner', 'Project Manager', 'Field Worker'.
permissions	Granular flags like 'view_financials' or 'manage_renovation'.
role_permissions	Join table linking roles to permissions.

public.real_estate_properties

Stores the core real estate deal analysis data.

Column	Type	Description
id	BigInt	Primary Key
owner_id	UUID	References profiles.id
title	Text	Name of the property
financial_inputs	JSONB	Dynamic financial data (price, sqft, roi, assumption metrics)
status	Text	'draft', 'active', etc.
asking_price	Numeric	Listing price
offer_price	Numeric	Proposed offer price
arv	Numeric	After Repair Value

public.financial_accounts

Stores accounts for the bookkeeping module.

Column	Type	Description
id	UUID	Primary Key
user_id	UUID	References profiles.id
name	Text	Account name (e.g., "Chase Checking")
type	Text	'checking', 'savings', 'credit', etc.
balance	Numeric	Current balance (manual tracking)

public.transactions

Records income and expenses linked to accounts and properties.

Column	Type	Description
id	BigInt	Primary Key
user_id	UUID	References profiles.id
property_id	BigInt	Optional link to real_estate_properties
account_id	UUID	Link to financial_accounts
amount	Numeric	Transaction amount
type	Text	'income' or 'expense'
category	Text	Categorization tag (e.g., "Repairs")

public.comments

Facilitates collaboration on property analyses.

Column	Type	Description
id	BigInt	Primary Key
property_id	BigInt	Link to real_estate_properties
user_id	UUID	Author of the comment
content	Text	The comment text
field_path	Text	Optional JSON path for specific field comments

public.projects

Manages the "Live" phase of a property deal.

Column	Type	Description
id	UUID	Primary Key
property_id	BigInt	Links to the locked Pro Forma property
status	Text	'active', 'completed'
start_date	Date	Project kickoff

public.project_budget_items

The granular budget buckets.

Column	Type	Description
project_id	UUID	Link to Project
name	Text	Category name (e.g., "Kitchen")
budget_amount	Numeric	The authorized budget
tax_category	Text	IRS classification

public.project_tasks

Task management for field execution.

Column	Type	Description
project_id	UUID	Link to Project
budget_item_id	UUID	Optional link to budget item
assigned_to	UUID	User assigned (Field Worker)
photos	Text[]	Array of completion photo URLs
status	Text	'todo', 'in_progress', 'done'

public.notifications

System-wide alert system.

Column	Type	Description
user_id	UUID	Target user (null for global role-based)
type	Text	'overage', 'system'
read_by	JSONB	Array of User UUIDs who have read it

Security Policies (RLS)

• Profiles:
• SELECT: Users can see their own profile. Admins can see all profiles.

• UPDATE: Admins only.

• Real Estate Properties:

• SELECT: Users see their own properties. Admins see all properties.
• INSERT: All users can create properties.

• UPDATE/DELETE: Users can manage their own unlocked properties. Admins extend full control.

• Bookkeeping (Accounts & Transactions):

• ALL: Users manage their own records. Admins have full access for support/oversight.

• Comments:

• SELECT: Authenticated users can view comments (Team visibility).
• INSERT: Authenticated users can comment.
• UPDATE/DELETE: Users manage their own comments. Admins can moderate.