# Data model

## Entities

### User

- id: uuid
- email: text
- role: text
- created_at: timestamptz

Relationships: has one WalkerProfile if role=walker; has many Bookings as owner or walker; has many Reviews; has many Messages

### WalkerProfile

- user_id: uuid
- bio: text
- hourly_rate: numeric
- service_postcode: text
- service_radius_km: numeric
- lat: numeric
- lng: numeric
- approval_status: text
- photo_url: text

Relationships: belongs to User; has many Bookings; has many Reviews; has one PayoutAccount

### Booking

- id: uuid
- owner_id: uuid
- walker_id: uuid
- status: text
- start_time: timestamptz
- end_time: timestamptz
- dog_size: text
- price: numeric
- expires_at: timestamptz

Relationships: belongs to owner User; belongs to walker User; has one Payment; has one Review

### Payment

- id: uuid
- booking_id: uuid
- stripe_payment_intent_id: text
- amount: numeric
- status: text
- created_at: timestamptz

Relationships: belongs to Booking

### PayoutAccount

- walker_id: uuid
- stripe_account_id: text
- onboarding_status: text

Relationships: belongs to WalkerProfile

### Review

- id: uuid
- booking_id: uuid
- rating: integer
- comment: text
- flagged: boolean
- created_at: timestamptz

Relationships: belongs to Booking; belongs to WalkerProfile (via booking)

### Message

- id: uuid
- booking_id: uuid
- sender_id: uuid
- body: text
- read_at: timestamptz
- created_at: timestamptz

Relationships: belongs to Booking; belongs to sender User

### AdminAuditLog

- id: uuid
- admin_id: uuid
- action: text
- target_type: text
- target_id: uuid
- created_at: timestamptz

Relationships: belongs to admin User

## DDL sketch

```sql
CREATE TABLE users (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), email text UNIQUE NOT NULL, role text NOT NULL CHECK (role IN ('owner','walker','admin')), created_at timestamptz DEFAULT now()); CREATE TABLE walker_profiles (user_id uuid PRIMARY KEY REFERENCES users(id), bio text, hourly_rate numeric NOT NULL, service_postcode text, service_radius_km numeric, lat numeric, lng numeric, approval_status text DEFAULT 'pending', photo_url text); CREATE TABLE payout_accounts (walker_id uuid PRIMARY KEY REFERENCES walker_profiles(user_id), stripe_account_id text, onboarding_status text DEFAULT 'incomplete'); CREATE TABLE bookings (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), owner_id uuid REFERENCES users(id), walker_id uuid REFERENCES users(id), status text NOT NULL DEFAULT 'requested', start_time timestamptz NOT NULL, end_time timestamptz NOT NULL, dog_size text, price numeric NOT NULL, expires_at timestamptz, created_at timestamptz DEFAULT now()); CREATE TABLE payments (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), booking_id uuid REFERENCES bookings(id), stripe_payment_intent_id text, amount numeric NOT NULL, status text NOT NULL, created_at timestamptz DEFAULT now()); CREATE TABLE reviews (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), booking_id uuid REFERENCES bookings(id), rating integer NOT NULL CHECK (rating BETWEEN 1 AND 5), comment text, flagged boolean DEFAULT false, created_at timestamptz DEFAULT now()); CREATE TABLE messages (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), booking_id uuid REFERENCES bookings(id), sender_id uuid REFERENCES users(id), body text NOT NULL, read_at timestamptz, created_at timestamptz DEFAULT now()); CREATE TABLE admin_audit_log (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), admin_id uuid REFERENCES users(id), action text NOT NULL, target_type text, target_id uuid, created_at timestamptz DEFAULT now());
```