Data model
Core relationship
An owner books a walker's available slot, which becomes a booking that is paid via Stripe and later rated.
Entities
accounts
A single login identity for an owner, walker, or admin, including their approval status if they are a walker.
Fields
- id: uuid
- email: text
- role: text
- approval_status: text
- photo_url: text
- created_at: timestamptz
Relationships: accounts 1—1 walker_profiles (when role is walker); accounts 1—* bookings (as owner)
Owned by: TB-001, TB-003, TB-025, TB-029
walker_profiles
The public-facing listing for a walker, holding their bio, service area, and hourly rate.
Fields
- id: uuid
- account_id: uuid
- bio: text
- service_area_zip: text
- service_area_radius_miles: integer
- hourly_rate_cents: integer
- rating_score: numeric
- is_live: boolean
Relationships: walker_profiles —1 accounts; walker_profiles 1— availability_slots; walker_profiles 1—* bookings (as walker)
Owned by: TB-002, TB-004, TB-006, TB-028
availability_slots
A block of time a walker has declared themselves free to take a booking.
Fields
- id: uuid
- walker_profile_id: uuid
- start_at: timestamptz
- end_at: timestamptz
- is_booked: boolean
Relationships: availability_slots *—1 walker_profiles; availability_slots 1—1 bookings (when booked)
Owned by: TB-005, TB-007, TB-008, TB-011
bookings
A single requested, accepted, completed, or declined walk transaction between an owner and a walker.
Fields
- id: uuid
- owner_id: uuid
- walker_profile_id: uuid
- availability_slot_id: uuid
- dog_size: text
- status: text
- requested_at: timestamptz
- completed_at: timestamptz
Relationships: bookings —1 accounts (owner); bookings —1 walker_profiles; bookings 1—1 ratings; bookings 1— payments; bookings 1— messages
Owned by: TB-009, TB-010, TB-011, TB-012, TB-013, TB-028
ratings
The 1-5 star score and written review an owner leaves for a walker after a completed booking.
Fields
- id: uuid
- booking_id: uuid
- stars: integer
- review_text: text
- flagged: boolean
- created_at: timestamptz
Relationships: ratings 1—1 bookings; ratings *—1 walker_profiles (via booking)
Owned by: TB-013, TB-014, TB-015, TB-016, TB-026
payments
A record of a charge, saved payment method, or refund tied to a booking.
Fields
- id: uuid
- booking_id: uuid
- stripe_payment_intent_id: text
- amount_cents: integer
- status: text
- is_saved_method: boolean
- refunded_amount_cents: integer
Relationships: payments *—1 bookings
Owned by: TB-017, TB-019, TB-020
payouts
The transfer of earned funds from the platform to a walker's Stripe connected account for a completed booking.
Fields
- id: uuid
- walker_profile_id: uuid
- booking_id: uuid
- stripe_connected_account_id: text
- amount_cents: integer
- status: text
- scheduled_at: timestamptz
Relationships: payouts *—1 walker_profiles; payouts *—1 bookings
Owned by: TB-018, TB-020
notifications
A record of a push or email notification sent to an account about a booking or message event.
Fields
- id: uuid
- account_id: uuid
- channel: text
- type: text
- sent_at: timestamptz
- payload: jsonb
Relationships: notifications *—1 accounts
Owned by: TB-012, TB-022
messages
A single in-app chat message exchanged between an owner and a walker about a booking.
Fields
- id: uuid
- booking_id: uuid
- sender_account_id: uuid
- body: text
- read_at: timestamptz
- flagged: boolean
- created_at: timestamptz
Relationships: messages *—1 bookings; messages *—1 accounts (sender)
Owned by: TB-021, TB-022, TB-023, TB-026
admin_audit_log
A durable record of every action an admin takes, including walker approvals and account suspensions.
Fields
- id: uuid
- admin_account_id: uuid
- action: text
- target_type: text
- target_id: uuid
- created_at: timestamptz
Relationships: admin_audit_log *—1 accounts (admin)
Owned by: TB-024, TB-025, TB-027, TB-029, TB-030
Relationship diagram
accounts 1—1 walker_profiles
accounts 1—* bookings
walker_profiles 1—* bookings
walker_profiles 1—* availability_slots
bookings 1—1 ratings
bookings 1—* payments
walker_profiles 1—* payouts
accounts 1—* notifications
bookings 1—* messages
accounts 1—* admin_audit_log
End-to-end trace
- Owner searches walker_profiles filtered by availability_slots, price, and dog size — no write occurs yet.
- Owner submits a booking request against a walker's availability_slots entry — bookings.status becomes 'requested'.
- Walker accepts the booking — bookings.status becomes 'accepted' and the linked availability_slots row is marked is_booked.
- Owner's saved payment method is charged via Stripe at confirmation — a payments row is created with status 'captured'.
- The walk occurs and the booking is marked complete — bookings.status becomes 'completed' and completed_at is set.
- Owner is prompted and submits a rating — a ratings row is created linked to bookings.id with a star value and review text.
- walker_profiles.rating_score recalculates using the new rating plus existing ratings weighted by recency and volume.
- A payout for the completed booking is scheduled — a payouts row moves from 'pending' to 'paid' to the walker's connected account.
DDL sketch
CREATE TABLE accounts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
email text UNIQUE NOT NULL,
role text NOT NULL CHECK (role IN ('owner','walker','admin')),
approval_status text DEFAULT 'pending',
photo_url text,
created_at timestamptz DEFAULT now()
);
CREATE TABLE walker_profiles (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
account_id uuid REFERENCES accounts(id),
bio text,
service_area_zip text,
service_area_radius_miles integer,
hourly_rate_cents integer,
rating_score numeric DEFAULT 0,
is_live boolean DEFAULT false
);
CREATE TABLE availability_slots (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
walker_profile_id uuid REFERENCES walker_profiles(id),
start_at timestamptz NOT NULL,
end_at timestamptz NOT NULL,
is_booked boolean DEFAULT false
);
CREATE TABLE bookings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid REFERENCES accounts(id),
walker_profile_id uuid REFERENCES walker_profiles(id),
availability_slot_id uuid REFERENCES availability_slots(id),
dog_size text,
status text NOT NULL DEFAULT 'requested',
requested_at timestamptz DEFAULT now(),
completed_at timestamptz
);
CREATE TABLE ratings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
booking_id uuid UNIQUE REFERENCES bookings(id),
stars integer CHECK (stars BETWEEN 1 AND 5),
review_text text,
flagged boolean DEFAULT false,
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_cents integer,
status text,
is_saved_method boolean DEFAULT false,
refunded_amount_cents integer DEFAULT 0
);
CREATE TABLE payouts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
walker_profile_id uuid REFERENCES walker_profiles(id),
booking_id uuid REFERENCES bookings(id),
stripe_connected_account_id text,
amount_cents integer,
status text DEFAULT 'pending',
scheduled_at timestamptz
);
CREATE TABLE notifications (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
account_id uuid REFERENCES accounts(id),
channel text,
type text,
sent_at timestamptz DEFAULT now(),
payload jsonb
);
CREATE TABLE messages (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
booking_id uuid REFERENCES bookings(id),
sender_account_id uuid REFERENCES accounts(id),
body text,
read_at timestamptz,
flagged boolean DEFAULT false,
created_at timestamptz DEFAULT now()
);
CREATE TABLE admin_audit_log (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
admin_account_id uuid REFERENCES accounts(id),
action text,
target_type text,
target_id uuid,
created_at timestamptz DEFAULT now()
);