---
created: 2026-03-12
source: Growth-Engine
tags: [agent-archive, growth-engine]
---

# PHASE 4: DATABASE SCHEMA ANALYSIS
**Date:** February 3, 2026  
**Focus:** Complete Database Structure, Relationships, Migration Needs

## 🎯 DATABASE OVERVIEW

### **Database Technology**
- **Type**: PostgreSQL (Fly.io managed)
- **ORM**: SQLAlchemy 2.0 + Alembic migrations
- **Tables**: 20+ tables with full relationships
- **Size**: Production database with real data

### **Key Characteristics**
- **Referential integrity**: Foreign keys with CASCADE/SET NULL
- **Audit trails**: `created_at`, `updated_at` timestamps
- **Soft deletes**: Not implemented (hard deletes)
- **Indexing**: Primary keys, foreign keys, unique constraints

## 📊 COMPLETE TABLE LIST

### **CORE TABLES (Essential for Marketplace)**

#### **1. `users`** - User accounts
- **Columns**: `id`, `email`, `first_name`, `last_name`, `user_type` (contractor/worker)
- **Key fields**: `abn`, `gst_registered`, `public_liability_insurance`
- **Relationships**: Posted jobs, applications, contracts, ratings

#### **2. `jobs`** - Job listings
- **Columns**: `id`, `title`, `description`, `contractor_id`, `category_id`, `location`
- **Payment fields**: `budget_min`, `budget_max`, `hourly_rate`
- **Status**: `status` (open, assigned, completed, cancelled)

#### **3. `applications`** - Job applications
- **Columns**: `job_id`, `worker_id`, `status` (pending, accepted, rejected)
- **Proposal**: `proposed_rate`, `cover_letter`
- **Unique constraint**: One application per worker per job

#### **4. `contracts`** - Legal contracts
- **Columns**: `job_id`, `contractor_id`, `worker_id`, `agreed_rate`
- **Status fields**: `status`, `contractor_signed`, `worker_signed`
- **Timeline**: `start_date`, `end_date`, `completion_date`

#### **5. `payments`** - Escrow payments
- **Columns**: `contract_id`, `stripe_payment_intent_id`, `gross_amount`
- **Fee breakdown**: `platform_fee`, `gst_amount`, `net_to_worker`
- **Status**: `status` (pending, held_escrow, released, transferred)

#### **6. `invoices`** - GST invoices
- **Columns**: `payment_id`, `invoice_number`, `amount_ex_gst`, `gst_amount`
- **Compliance**: `supplier_abn`, `buyer_abn`, `gst_rate` (10%)
- **Status**: `status` (draft, sent, paid, overdue)

### **SUPPORTING TABLES**

#### **7. `categories`** - Trade categories
- **Columns**: `name`, `slug`, `description`, `parent_id`
- **Compliance**: `whs_risk_level`, `insurance_requirements`

#### **8. `ratings`** - Mutual ratings
- **Columns**: `contract_id`, `rater_id`, `rated_id`, `score`, `comment`
- **Bidirectional**: Both contractor and worker rate each other

#### **9. `messages`** - In-app messaging
- **Columns**: `sender_id`, `receiver_id`, `contract_id`, `content`
- **Status**: `read`, `deleted`

#### **10. `notifications`** - System notifications
- **Columns**: `user_id`, `type`, `title`, `content`, `read`
- **Categories**: Job alerts, payment updates, system messages

#### **11. `user_sessions`** - Login sessions
- **Columns**: `user_id`, `session_token`, `expires_at`
- **Security**: Session management

### **GAMIFICATION & ANALYTICS**

#### **12. `user_points`** - Gamification points
- **Columns**: `user_id`, `points`, `level`, `badges`
- **Activities**: Job completion, ratings, referrals

#### **13. `referrals`** - Referral system
- **Columns**: `referrer_id`, `referred_id`, `referral_code`
- **Discount**: 3% platform fee discount for matched referrals

#### **14. `whs_assessments`** - Safety assessments
- **Columns**: `job_id`, `risk_level`, `hazards`, `control_measures`
- **Compliance**: Australian WHS requirements

## 🔗 KEY RELATIONSHIPS

### **Primary Relationships**
```
users (contractor) 1───* jobs
                     │
                     * applications
                     │
                     * contracts 1───1 payments
                                   │
                                   1───* invoices
                                   │
                                   * ratings (bidirectional)
```

### **User Type Relationships**
```
users (type: 'contractor')
  ├── posted_jobs (1:* jobs)
  ├── contracts_as_contractor (1:* contracts)
  └── ratings_given (1:* ratings)

users (type: 'worker')
  ├── job_applications (1:* applications)
  ├── contracts_as_worker (1:* contracts)
  └── ratings_received (1:* ratings)
```

### **Payment Flow Relationships**
```
contracts (1:1) payments (1:*) invoices
  │
  ├── job (1:1) jobs
  ├── contractor (1:1) users
  └── worker (1:1) users
```

## 🎯 DATABASE CHANGES FOR £50+£10 MODEL

### **1. Modify Existing Tables**

#### **`payments` table - Add flat fee tracking**
```sql
ALTER TABLE payments ADD COLUMN hire_fee_paid BOOLEAN DEFAULT FALSE;
ALTER TABLE payments ADD COLUMN hire_fee_amount DECIMAL(10,2) DEFAULT 50.00;
ALTER TABLE payments ADD COLUMN hire_fee_stripe_intent_id VARCHAR(255);
```

#### **`contracts` table - Add insurance tracking**
```sql
ALTER TABLE contracts ADD COLUMN insurance_subscription_id INTEGER;
ALTER TABLE contracts ADD COLUMN insurance_claim_filed BOOLEAN DEFAULT FALSE;
ALTER TABLE contracts ADD COLUMN insurance_claim_amount DECIMAL(10,2);
```

### **2. Create New Tables**

#### **`insurance_subscriptions`** - £10/week subscriptions
```sql
CREATE TABLE insurance_subscriptions (
    id SERIAL PRIMARY KEY,
    worker_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    stripe_subscription_id VARCHAR(255) UNIQUE,
    stripe_customer_id VARCHAR(255),
    weekly_amount DECIMAL(10,2) DEFAULT 10.00,
    status VARCHAR(20) DEFAULT 'active',  -- active, canceled, paused
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_insurance_subscriptions_worker ON insurance_subscriptions(worker_id);
CREATE INDEX idx_insurance_subscriptions_status ON insurance_subscriptions(status);
```

#### **`insurance_claims`** - Worker claims
```sql
CREATE TABLE insurance_claims (
    id SERIAL PRIMARY KEY,
    worker_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    contract_id INTEGER REFERENCES contracts(id) ON DELETE CASCADE,
    amount_claimed DECIMAL(10,2),
    amount_paid DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',  -- pending, approved, paid, rejected
    evidence TEXT,
    paid_date TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_insurance_claims_worker ON insurance_claims(worker_id);
CREATE INDEX idx_insurance_claims_contract ON insurance_claims(contract_id);
CREATE INDEX idx_insurance_claims_status ON insurance_claims(status);
```

#### **`insurance_fund`** - Fund management
```sql
CREATE TABLE insurance_fund (
    id SERIAL PRIMARY KEY,
    balance DECIMAL(10,2) DEFAULT 0.00,
    total_premiums DECIMAL(10,2) DEFAULT 0.00,
    total_claims DECIMAL(10,2) DEFAULT 0.00,
    total_recoveries DECIMAL(10,2) DEFAULT 0.00,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

#### **`legal_actions`** - Lawyer pursuit tracking
```sql
CREATE TABLE legal_actions (
    id SERIAL PRIMARY KEY,
    claim_id INTEGER REFERENCES insurance_claims(id) ON DELETE CASCADE,
    contractor_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    action_type VARCHAR(50),  -- demand_letter, small_claims, debt_collection
    status VARCHAR(20) DEFAULT 'pending',
    amount_demanded DECIMAL(10,2),
    amount_recovered DECIMAL(10,2),
    lawyer_cost DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

### **3. Update Existing Models**

#### **`Payment` model changes**
```python
# In app/models/contract.py
class Payment(BaseModel):
    # Add new fields
    hire_fee_paid = db.Column(db.Boolean, default=False)
    hire_fee_amount = db.Column(db.Numeric(10, 2), default=50.00)
    hire_fee_stripe_intent_id = db.Column(db.String(255))
    
    # Modify calculate_amounts() method
    def calculate_amounts(self):
        # Change from 7% to £50
        # self.platform_fee = base_amount * 0.07  # OLD
        self.platform_fee = 50.00  # NEW: £50 flat fee
```

#### **New `InsuranceSubscription` model**
```python
# New file: app/models/insurance.py
class InsuranceSubscription(BaseModel):
    __tablename__ = 'insurance_subscriptions'
    
    worker_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    stripe_subscription_id = db.Column(db.String(255), unique=True)
    stripe_customer_id = db.Column(db.String(255))
    weekly_amount = db.Column(db.Numeric(10, 2), default=10.00)
    status = db.Column(db.String(20), default='active')
    start_date = db.Column(db.DateTime)
    end_date = db.Column(db.DateTime)
    
    # Relationships
    worker = db.relationship('User', backref='insurance_subscriptions')
    claims = db.relationship('InsuranceClaim', backref='subscription', lazy='dynamic')
```

## ⚠️ MIGRATION CONSIDERATIONS

### **Data Migration Strategy**
1. **Backup first**: Full database backup before changes
2. **Test migration**: Run on staging database first
3. **Rollback plan**: Have SQL scripts to revert changes
4. **Data preservation**: Ensure no data loss during schema changes

### **Migration Order**
```
1. Backup database
2. Create new tables (insurance_subscriptions, insurance_claims, etc.)
3. Add columns to existing tables (ALTER TABLE)
4. Update application code to use new schema
5. Test thoroughly
6. Deploy to production
7. Monitor for issues
```

### **Backward Compatibility**
- **Old contracts**: Keep 7% fee calculation for existing contracts
- **New contracts**: Use £50 fee for new contracts
- **Transition period**: Support both models during migration

## 📈 DATABASE PERFORMANCE IMPACT

### **Indexing Strategy**
```sql
-- New indexes for performance
CREATE INDEX idx_payments_hire_fee ON payments(hire_fee_paid);
CREATE INDEX idx_contracts_insurance ON contracts(insurance_subscription_id, insurance_claim_filed);
CREATE INDEX idx_insurance_claims_dates ON insurance_claims(created_at, status);
```

### **Query Optimization**
- **Insurance queries**: Will need efficient joins between users, contracts, claims
- **Fund tracking**: Regular balance updates need transaction safety
- **Reporting**: Aggregate queries for insurance fund analytics

### **Storage Requirements**
- **Estimated growth**: 1-2MB per 1000 insurance subscriptions
- **Claim evidence**: Store as text/JSON, consider separate storage for large files
- **Audit trails**: Keep 7+ years for legal compliance

## 🔧 IMPLEMENTATION CHECKLIST

### **Database Changes**
- [ ] Create migration scripts for new tables
- [ ] Add columns to existing tables
- [ ] Create indexes for performance
- [ ] Write data migration for existing contracts (if needed)
- [ ] Test migrations on staging database

### **Model Updates**
- [ ] Update Payment model for £50 fee
- [ ] Create InsuranceSubscription model
- [ ] Create InsuranceClaim model
- [ ] Create InsuranceFund model
- [ ] Update Contract model for insurance tracking

### **Data Integrity**
- [ ] Foreign key constraints
- [ ] Unique constraints (prevent duplicate subscriptions)
- [ ] Check constraints (valid status values)
- [ ] Default values (sensible defaults)

---

**NEXT**: Phase 5 - Gap Analysis & Implementation Plan