Welpenmatch — Database Schema Explorer

👤 Users & Authentication 3 tables

users

RLS 14 cols ▼

Master user record for all user types (breeder, buyer, admin). Links to Supabase Auth via auth_uid.

Column	Type	Info
id	UUID	PK, gen_random_uuid()
auth_uid	UUID	UNIQUE — Supabase Auth link
email	TEXT	UNIQUE NOT NULL, regex check
user_type	TEXT	CHECK: breeder \| buyer \| admin
display_name	TEXT
photo_url	TEXT
phone_number	TEXT
is_active	BOOLEAN	DEFAULT true
email_verified	BOOLEAN	DEFAULT false
phone_verified	BOOLEAN	DEFAULT false
created_at	TIMESTAMPTZ	DEFAULT NOW()
updated_at	TIMESTAMPTZ	DEFAULT NOW()
last_login_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ	Soft delete

RLS Policies

• Users can view own profile (SELECT where auth.uid() = auth_uid)
• Users can update own profile (UPDATE where auth.uid() = auth_uid)
• Admins can view all users (SELECT where user_type = 'admin')

Indexes

idx_users_emailidx_users_user_typeidx_users_auth_uid

Repository

UserRepository

breeder_profiles

3 JSONB 36 cols ▼

Extended public profile for breeders: business info, location, verification, gallery, stats. 1:1 with users.

Column	Type	Info
id	UUID	PK
user_id	UUID	FK → users(id) UNIQUE, CASCADE
breeder_name	TEXT	NOT NULL
slug	TEXT	UNIQUE NOT NULL
description	TEXT
bio	TEXT
established_year	INTEGER
street_address	TEXT
city	TEXT
state_province	TEXT
postal_code	TEXT
country	TEXT	DEFAULT 'Switzerland'
latitude	DECIMAL(10,8)	GIST index for geo search
longitude	DECIMAL(11,8)
website_url	TEXT
facebook_url	TEXT
instagram_url	TEXT
whatsapp_number	TEXT
verification_status	TEXT	CHECK: pending \| verified \| rejected \| suspended
verified_at	TIMESTAMPTZ
verification_notes	TEXT
languages	TEXT[]	DEFAULT ['German']
specializations	TEXT[]
certifications	JSONB	[{name, issuer, date, expiry, document_url}]
profile_image_url	TEXT
cover_image_url	TEXT
gallery_images	TEXT[]
is_public	BOOLEAN	DEFAULT true
accepts_applications	BOOLEAN	DEFAULT true
show_exact_location	BOOLEAN	DEFAULT false
notification_preferences	JSONB
total_litters, total_puppies	INTEGER	Denormalized stats
average_rating	DECIMAL(3,2)	Denormalized
total_reviews	INTEGER	Denormalized
onboarding_completed	BOOLEAN
onboarding_step	INTEGER
custom_fields	JSONB
stripe_connect_id	TEXT	Fallback — primary link via stripe_connect_accounts
payments_enabled	BOOLEAN	DEFAULT false
suspended_until	TIMESTAMPTZ	Moderation
created_at	TIMESTAMPTZ	DEFAULT NOW()
updated_at	TIMESTAMPTZ	DEFAULT NOW()

Indexes

idx_breeder_profiles_user_ididx_breeder_profiles_slugidx_breeder_profiles_verification_statusGIST ll_to_earth(lat,lng)

Repository

BreederProfileRepository

buyer_profiles

2 JSONB 33 cols ▼

Buyer profile with household info, lifestyle, dog preferences, and profile completion state. 1:1 with users.

Column	Type	Info
id	UUID	PK
user_id	UUID	FK → users(id) UNIQUE, CASCADE
first_name	TEXT
last_name	TEXT
date_of_birth	DATE
occupation	TEXT
city	TEXT
state_province	TEXT
postal_code	TEXT
country	TEXT	DEFAULT 'Switzerland'
housing_type	TEXT	CHECK: apartment \| house \| farm \| other
has_yard	BOOLEAN
yard_size	TEXT	CHECK: none \| small \| medium \| large
owns_or_rents	TEXT	CHECK: own \| rent
dog_experience_level	TEXT	CHECK: first_time \| some_experience \| experienced \| expert
previous_breeds	TEXT[]
preferred_breeds	TEXT[]
lifestyle_quiz_results	JSONB	Quiz answers + preferences
household_adults	INTEGER
household_children	INTEGER
children_ages	INTEGER[]
other_pets	JSONB	[{type, breed, age, temperament}]
work_schedule	TEXT	CHECK: home \| office_fulltime \| etc.
daily_absence_hours	INTEGER
preferred_gender	TEXT	CHECK: male \| female \| no_preference
preferred_age_range	TEXT	CHECK: puppy \| young \| adult \| any
preferred_size	TEXT	CHECK: small \| medium \| large \| giant \| any
max_price	DECIMAL(10,2)
why_want_dog	TEXT
specific_requirements	TEXT
allergies	TEXT[]
profile_completed	BOOLEAN	DEFAULT false
profile_approved	BOOLEAN	DEFAULT false

Repository

BuyerProfileRepository

🐕 Listings & Breeding 7 tables

dog_breeds

30+ cols▼

Master breed data: size, temperament traits (1-5 scale), compatibility, care needs. Full-text GIN search index.

Column	Type	Info
id	UUID	PK
name	TEXT	UNIQUE NOT NULL
name_de	TEXT	German name
breed_group	TEXT
size_category	TEXT	CHECK: small \| medium \| large \| giant
weight_min_kg, weight_max_kg	DECIMAL(5,2)
height_min_cm, height_max_cm	DECIMAL(5,2)
life_expectancy_min, _max	INTEGER
energy_level, exercise_needs, grooming_needs, shedding_level, trainability, intelligence	INTEGER	CHECK 1–5
good_with_children, _dogs, _cats, _strangers	INTEGER	CHECK 1–5
barking_tendency, prey_drive, wanderlust	INTEGER	CHECK 1–5
apartment_friendly, novice_friendly	BOOLEAN
cold_weather_tolerance, hot_weather_tolerance	INTEGER	CHECK 1–5
description, history, care_instructions	TEXT
health_issues	TEXT[]

Repository

BreedRepository

dogs

3 JSONB27 cols▼

Breeder-owned breeding dogs (sires & dams). Tracks health tests, achievements, breeding status.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id) CASCADE
name	TEXT	NOT NULL
registration_name	TEXT
breed_id	UUID	FK → dog_breeds(id)
breed_name	TEXT	Denormalized
gender	TEXT	CHECK: male \| female
birth_date	DATE	NOT NULL
color	TEXT
weight_kg	DECIMAL(5,2)
registration_number	TEXT
microchip_number	TEXT
pedigree_url	TEXT
health_tests	JSONB	[{test_name, date, result, certificate_url}]
vaccinations	JSONB	[{vaccine, date, next_due}]
breeding_status	TEXT	CHECK: active \| retired \| not_breeding
first_heat_date	DATE
breeding_restrictions	TEXT
profile_image_url	TEXT
achievements	JSONB	[{title, date, org}]
titles	TEXT[]	Show titles
gallery_images	TEXT[]
is_active	BOOLEAN	DEFAULT true
is_public	BOOLEAN	DEFAULT true
created_at	TIMESTAMPTZ	DEFAULT NOW()
updated_at	TIMESTAMPTZ	DEFAULT NOW()
deleted_at	TIMESTAMPTZ	Soft delete

Indexes

idx_dogs_breeder_ididx_dogs_breed_ididx_dogs_genderidx_dogs_breeding_status

Repository

DogRepository

litters

1 JSONB20 cols▼

Planned/active/completed litters with parent pairing, dates, and waitlist status.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id) CASCADE
mother_id	UUID	FK → dogs(id)
father_id	UUID	FK → dogs(id)
father_external_info	JSONB	If father not owned by breeder
litter_name, breed_name	TEXT
breed_id	UUID	FK → dog_breeds(id)
mating_date, expected_date, birth_date, ready_date	DATE
expected_puppies, born_puppies, available_puppies	INTEGER
status	TEXT	CHECK: planned \| pregnant \| born \| ready \| closed
waitlist_open	BOOLEAN
waitlist_spots	INTEGER
images	TEXT[]

Repository

LitterRepository

puppies

RLS4 JSONB30 cols▼

Individual puppies: listing, health, pricing, reservation lifecycle. AI match profile for buyer matching.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id) CASCADE
litter_id	UUID	FK → litters(id) SET NULL
breed_id	UUID	FK → dog_breeds(id)
name, registration_name, breed_name	TEXT
gender	TEXT	CHECK: male \| female
birth_date	DATE	NOT NULL
color, coat_type, expected_size	TEXT
current_weight_kg	DECIMAL(5,2)
personality_traits	JSONB	{playful: 4, calm: 2, …}
ai_match_profile	JSONB	Generated matching profile
vaccinations	JSONB
vet_checks	JSONB
deworming_dates	DATE[]
price	DECIMAL(10,2)
status	TEXT	CHECK: available \| reserved \| sold \| keeping
reserved_by	UUID	FK → users(id)
sold_to	UUID	FK → users(id)
gallery_images, video_urls	TEXT[]

RLS Policies

• Public can view available puppies (status = 'available' AND breeder is_public)
• Breeders can manage own puppies (breeder_id matches auth user)

Indexes

GIN full-text searchidx_puppies_statusidx_puppies_listing (partial composite)idx_puppies_price

Repository

PuppyRepository

rehoming_dogs

28 cols▼

Dogs listed for rehoming by owners. Includes behavior, compatibility, urgency, and requirements.

Column	Type	Info
id	UUID	PK
owner_id	UUID	FK → users(id) CASCADE
breed_id	UUID	FK → dog_breeds(id)
name, breed_name	TEXT
gender	TEXT	CHECK: male \| female
weight_kg, size, color	DECIMAL / TEXT
neutered, vaccinated, microchipped	BOOLEAN
house_trained, crate_trained, leash_trained	BOOLEAN
good_with_children, _dogs, _cats, _strangers	BOOLEAN
rehoming_reason	TEXT	NOT NULL
urgency	TEXT	CHECK: low \| medium \| high \| urgent
rehoming_fee	DECIMAL(10,2)
status	TEXT	CHECK: available \| pending \| rehomed

heat_cycles

Not in schema doc▼

Tracks heat cycles for breeding females. Found in breeder_portal migration.

Repository

HeatCycleRepository

Source

Migration: 20240501000000_breeder_portal.sql

expected_litters

Not in schema doc▼

Pre-litter planning: expected date, pregnancy confirmation, waitlist settings. Used by InquiryRepository.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id)
expected_date	DATE	NOT NULL
date_confirmed	BOOLEAN
pregnancy_confirmed	BOOLEAN
mother_id	UUID	FK → dogs(id)
father_id	UUID	FK → dogs(id)
breed_id	UUID	FK → dog_breeds(id)
expected_puppies, max_waitlist_spots	INTEGER
waitlist_open	BOOLEAN

Repository

InquiryRepository (getExpectedLitters)

💬 Communication & Inquiries 9 tables

inquiries

RLSNot in schema doc▼

Central inquiry system: buyer-to-breeder communication with compatibility scoring, auto-reject, and status workflow.

Column	Type	Info
id	UUID	PK
buyer_id	UUID	FK → users(id) CASCADE
breeder_id	UUID	FK → users(id) CASCADE
puppy_id	UUID	FK → puppies(id) SET NULL
litter_id	UUID	FK → litters(id) SET NULL
status	VARCHAR(50)	open \| weitere_angaben \| warteliste \| abgelehnt \| reserviert \| abgeschlossen
compatibility_score	DECIMAL(3,2)	0.00 to 1.00
breeder_ranking	INTEGER	Breeder preference order
auto_reject_date	TIMESTAMPTZ
initial_message	TEXT
breeder_notes	TEXT
waitlist_expiry_date	TIMESTAMPTZ

Repository

InquiryRepository — the most complex repository with CRUD, messaging, payments, reservations, waitlist, FAQ, and auto-rejection.

inquiry_messages

1 JSONBNot in schema doc▼

Chat messages within an inquiry. Supports AI responses and attachments.

Column	Type	Info
id	UUID	PK
inquiry_id	UUID	FK → inquiries(id) CASCADE
sender_id	UUID	FK → users(id) CASCADE
message	TEXT	NOT NULL
is_ai_response	BOOLEAN	DEFAULT false
attachments	JSONB	[{url, name, type, size}]
read_at	TIMESTAMPTZ
read_by	UUID	FK → users(id)

inquiry_preferences

Not in schema doc▼

Buyer preferences per inquiry: breed, color, sex, count.

Column	Type	Info
inquiry_id	UUID	FK → inquiries(id)
breed_id	UUID
breed_name	TEXT
color_coat_preference	TEXT
sex_preference	TEXT	DEFAULT 'no_preference'
puppy_count	INTEGER	DEFAULT 1

inquiry_status_history

Not in schema doc▼

Audit trail for inquiry status changes.

Column	Type	Info
inquiry_id	UUID	FK → inquiries(id)
old_status	TEXT
new_status	TEXT
changed_by	TEXT
reason	TEXT
metadata	JSONB

chat_rooms

10 cols▼

Legacy direct message rooms with polymorphic context (application/puppy_inquiry).

Column	Type	Info
id	UUID	PK
participant_ids	UUID[]	GIN index
context_type	TEXT	CHECK: general \| application \| puppy_inquiry
context_id	UUID	Polymorphic — not FK enforced
is_active	BOOLEAN
blocked_by	UUID	FK → users(id)
last_message_at	TIMESTAMPTZ
last_message_preview	TEXT

Repository

ChatRoomRepository

messages

RLS2 JSONB▼

Individual chat messages within legacy chat_rooms. Partitioned by month.

Column	Type	Info
id	UUID	PK
chat_room_id	UUID	FK → chat_rooms(id) CASCADE
sender_id	UUID	FK → users(id)
content	TEXT	NOT NULL
message_type	TEXT	CHECK: text \| image \| document \| system
attachments	JSONB	[{url, type, name, size}]
is_read	BOOLEAN
is_edited	BOOLEAN
metadata	JSONB

RLS Policies

• Users can view own messages (sender or chat participant)
• Users can send messages (only to rooms they participate in)

Repository

MessageRepository

notifications

12 cols▼

In-app, email, and SMS notification records with delivery status.

Column	Type	Info
id	UUID	PK
user_id	UUID	FK → users(id) CASCADE
type	TEXT	NOT NULL
title, body	TEXT	NOT NULL
entity_type, entity_id	TEXT / UUID	Context reference
action_url	TEXT
is_read	BOOLEAN	DEFAULT false
channels	TEXT[]	DEFAULT ['in_app']
email_sent, sms_sent	BOOLEAN

breeder_faqs

1 JSONBNot in schema doc▼

Breeder FAQ configurations with AI auto-response capability.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id) CASCADE
is_active	BOOLEAN	DEFAULT true
ai_enabled	BOOLEAN	DEFAULT true
ai_prompt	TEXT	Custom AI instructions
faq_items	JSONB	[{question, answer, category, order}]
auto_response_delay_minutes	INTEGER	DEFAULT 0

auto_rejection_rules

Not in schema doc▼

Configurable auto-rejection rules per breeder based on profile completeness and compatibility scores.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id)
is_active	BOOLEAN
no_profile_photo	BOOLEAN
incomplete_profile	BOOLEAN
no_response_days	INTEGER
minimum_compatibility_score	DECIMAL
rejection_message	TEXT

💰 Payments & Subscriptions 5 tables

payments

1 JSONBNot in schema doc▼

Payment requests and Stripe transactions for reservation deposits and final payments.

Column	Type	Info
id	UUID	PK
inquiry_id	UUID	FK → inquiries(id) CASCADE
type	VARCHAR(50)	reservation_deposit \| final_payment
amount	DECIMAL(10,2)	NOT NULL
currency	VARCHAR(3)	DEFAULT 'CHF'
status	VARCHAR(50)	pending \| processing \| completed \| failed \| refunded \| cancelled
stripe_payment_intent_id	VARCHAR(255)
stripe_checkout_session_id	VARCHAR(255)
payment_method	VARCHAR(50)	card \| bank_transfer \| twint
paid_at, refunded_at	TIMESTAMPTZ
metadata	JSONB

reservations

Not in schema doc▼

Puppy reservations with confirmation number, pickup details, and deposit link.

Column	Type	Info
id	UUID	PK
inquiry_id	UUID	FK → inquiries(id) CASCADE
deposit_payment_id	UUID	FK → payments(id)
expected_pickup_date, actual_pickup_date	DATE
pickup_location	TEXT
confirmation_number	TEXT
notify_other_breeders	BOOLEAN
notes	TEXT

subscriptions

Not in schema docRepo▼

Breeder subscriptions with Stripe integration. Tiers: free, premium.

Column	Type	Info
id	UUID	PK (inferred)
breeder_id	UUID	FK → users(id) UNIQUE
stripe_subscription_id	TEXT
stripe_customer_id	TEXT
tier	TEXT	free \| premium
status	TEXT	active \| past_due \| canceled
current_period_end	TIMESTAMPTZ

Repository

SubscriptionRepository — noted as "not yet in generated Supabase types"

stripe_connect_accounts

RLSNot in schema doc▼

Stripe Connect onboarding for breeder payouts. Linked from breeder_profiles.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id) UNIQUE
stripe_account_id	TEXT	UNIQUE NOT NULL
onboarding_complete	BOOLEAN
charges_enabled	BOOLEAN
payouts_enabled	BOOLEAN

Repository

StripeConnectRepository

webhook_events

Not in schema doc▼

Idempotent Stripe webhook event log for deduplication.

🌐 Breeder Sites 6 tables

breeder_sites

Not in schema docRepo▼

Premium AI-built breeder websites: tier (free/premium), custom domain, theme, publish status.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id)
tier	TEXT	CHECK: free \| premium
theme	TEXT
custom_domain	TEXT
is_published	BOOLEAN	DEFAULT false

Repository

BreederSiteRepository

breeder_site_pages

1 JSONBNot in schema doc▼

Page builder pages for breeder sites: templates, sections (JSONB), nav order.

Column	Type	Info
id	UUID	PK
site_id	UUID	FK → breeder_sites(id) CASCADE
page_type	TEXT
template_id	TEXT
nav_label	TEXT
nav_order	INTEGER
sections	JSONB	Section instances array
is_published, is_visible	BOOLEAN

Repository

BreederSitePageRepository

breeder_pages

RLS1 JSONBNot in schema doc▼

Legacy Puck page builder data for breeder websites.

Column	Type	Info
id	UUID	PK
breeder_id	TEXT	NOT NULL
slug	TEXT	UNIQUE per breeder
title	TEXT
puck_data	JSONB	Page builder data
is_published, is_homepage	BOOLEAN
sort_order	INTEGER

Repository

BreederPageRepository

breeder_media

Not in schema doc▼

Media library for breeder sites: images, videos, documents with storage paths.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id) CASCADE
file_type	TEXT	CHECK: image \| video \| document
storage_path, filename, mime_type	TEXT
size_bytes	BIGINT
thumbnail_path	TEXT
is_public	BOOLEAN

Repository

BreederMediaRepository

breeder_media_usage

Not in schema doc▼

Tracks where media is used across breeder site pages.

anonymous_leads

Not in schema doc▼

Lead capture from breeder site visitors before they sign up.

Repository

AnonymousLeadRepository

📋 Compliance & Regulations 12 tables

cantons

Not in schema doc▼

Swiss cantons master data with multilingual names (DE/FR/IT).

Column	Type	Info
id	UUID	PK
canton_code	VARCHAR(2)	UNIQUE NOT NULL
canton_name	VARCHAR(100)	NOT NULL
canton_name_de, _fr, _it	VARCHAR(100)	Multilingual

cantonal_legal_status_types

Not in schema doc▼

Legal status categories for breed regulations (severity 0-5).

cantonal_breed_regulations

Not in schema doc▼

Per-canton breed legal status: permits, blood percentage thresholds, effective dates.

Column	Type	Info
id	UUID	PK
canton_id	UUID	FK → cantons(id)
breed_id	UUID	FK → dog_breeds(id)
status_id	UUID	FK → cantonal_legal_status_types(id)
effective_date	DATE	UNIQUE(canton_id, breed_id, effective_date)
blood_percentage_threshold	DECIMAL(5,2)
requires_permit	BOOLEAN
legal_reference	TEXT

cantonal_dog_regulations

Not in schema doc▼

General dog regulations per canton: training, leashing, fines. Multilingual (DE/FR/IT).

cantonal_training_requirements

Not in schema doc▼

Canton-specific dog training requirements.

cantonal_breed_categories

Not in schema doc▼

Breed category definitions per canton (e.g., Rassetypenliste I/II in Zürich). Maps breeds to cantonal risk tiers.

Column	Type	Info
id	UUID	PK
canton_id	UUID	FK → cantons(id)
category_name	TEXT	e.g. "Rassetypenliste I"
risk_level	INTEGER	Category severity tier
description	TEXT
legal_reference	TEXT

cantonal_restricted_areas

Not in schema doc▼

Area types and restriction types per canton: banned/leash/muzzle zones, seasonal periods, time windows, fine amounts.

Column	Type	Info
id	UUID	PK
canton_id	UUID	FK → cantons(id)
area_type	TEXT	park \| playground \| nature_reserve \| public_transport \| etc.
restriction_type	TEXT	banned \| leash_required \| muzzle_required
seasonal_start, seasonal_end	DATE	Optional seasonal window
fine_amount	DECIMAL(10,2)	CHF fine if applicable
description	TEXT

breed_regulations

1 JSONBNot in schema doc▼

Club-level breeding rules: age limits, litter caps, mandatory tests, certifications.

Column	Type	Info
id	UUID	PK
breed_name	TEXT	NOT NULL
source_club	TEXT
min_dam_age_months, max_dam_age_months	INTEGER
min_sire_age_months, max_sire_age_months	INTEGER
max_litters_per_year, max_litters_lifetime	INTEGER
min_litter_interval_months	INTEGER
mandatory_training, required_certifications	TEXT[]
additional_rules	JSONB

Repository

BreedRegulationRepository

breed_guideline_documents

Not in schema doc▼

Downloadable guideline PDFs per breed from clubs.

breeder_compliance_steps

Not in schema doc▼

Per-breeder compliance checklist tracking (e.g. health tests done, registrations completed).

Repository

ComplianceRepository

consent_records

Not in schema doc▼

GDPR/data consent tracking for users.

breed_health_requirements

Not in schema doc▼

Required health screenings per breed (hip scores, eye tests, etc.).

⭐ Engagement & Tracking 14 tables

applications

3 JSONB22 cols▼

Buyer applications for puppies, litter waitlists, or rehoming — the full workflow from submission to decision.

Column	Type	Info
id	UUID	PK
applicant_id	UUID	FK → users(id) CASCADE
breeder_id	UUID	FK → users(id)
application_type	TEXT	CHECK: puppy \| litter_waitlist \| rehoming
puppy_id	UUID	FK → puppies(id)
litter_id	UUID	FK → litters(id)
rehoming_dog_id	UUID	FK → rehoming_dogs(id)
introduction_message	TEXT	NOT NULL
questionnaire_answers	JSONB
references	JSONB	[{name, relationship, contact}]
veterinarian_info	JSONB
status	TEXT	CHECK: pending \| under_review \| approved \| rejected \| withdrawn
reviewed_by	UUID	FK → users(id)

Repository

ApplicationRepository

waitlists

12 cols▼

Ordered user queue per litter with gender/color preferences and offer tracking.

Column	Type	Info
id	UUID	PK
litter_id	UUID	FK → litters(id) CASCADE
user_id	UUID	FK → users(id) CASCADE
position	INTEGER
gender_preference	TEXT	CHECK: male \| female \| no_preference
status	TEXT	CHECK: active \| offered \| accepted \| declined \| expired
offered_puppy_id	UUID	FK → puppies(id)
response_deadline	TIMESTAMPTZ

reviews

16 cols▼

Buyer reviews of breeders with multi-dimensional ratings and breeder response.

Column	Type	Info
id	UUID	PK
reviewer_id	UUID	FK → users(id)
breeder_id	UUID	FK → users(id)
puppy_id	UUID	FK → puppies(id)
overall_rating	INTEGER	CHECK 1–5
communication_rating, puppy_health_rating, accuracy_rating	INTEGER	CHECK 1–5
title, content	TEXT
breeder_response	TEXT
is_verified_purchase	BOOLEAN

Repository

ReviewRepository

favorite_puppies

Composite PK▼

Junction: user ↔ bookmarked puppies.

favorite_breeders

Composite PK▼

Junction: user ↔ bookmarked breeders.

favorite_litters

Not in schema doc▼

Junction: user ↔ bookmarked litters.

favorite_dogs

Not in schema doc▼

Junction: user ↔ bookmarked breeding dogs.

favorite_breeds

Not in schema doc▼

Junction: user ↔ bookmarked breeds.

breeder_blog_posts

16 cols▼

Breeder-authored content/blog posts with optional litter/dog links.

Repository

BreederBlogRepository

user_activities

1 JSONB8 cols▼

Activity/audit-style tracking with IP, user agent, and metadata.

Repository

UserActivityRepository

user_views

Not in schema doc▼

Tracks entity view counts per user.

user_interactions

Not in schema doc▼

Tracks user interaction events (clicks, shares, etc.).

breeder_waitlist_settings

Not in schema doc▼

Per-breeder waitlist configuration and preferences.

🏛️ Rasseklub / SKG 9 tables

breed_clubs

Not in schema doc▼

SKG breed club master data. 112 clubs seeded. Core reference for the club admin system.

Column	Type	Info
id	UUID	PK
name	TEXT	NOT NULL
abbreviation	TEXT
breed_id	UUID	FK → dog_breeds(id)
website_url	TEXT
contact_email	TEXT

Repository

BreedClubRepository

breed_club_events

Not in schema doc▼

Club events: shows, meetings, training days.

Column	Type	Info
id	UUID	PK
club_id	UUID	FK → breed_clubs(id)
event_name	TEXT	NOT NULL
event_date	DATE
location	TEXT

breeder_breeds

Not in schema doc▼

Junction: breeder ↔ breed. Which breeds a breeder works with.

Column	Type	Info
breeder_id	UUID	FK → users(id)
breed_id	UUID	FK → dog_breeds(id)

Repository

BreederBreedRepository

breeder_clubs

Not in schema doc▼

Junction: breeder ↔ club with membership number.

Column	Type	Info
breeder_id	UUID	FK → users(id)
club_id	UUID	FK → breed_clubs(id)
membership_number	TEXT

Repository

BreederClubRepository

club_users

RLSNot in schema doc▼

Independent club-tool user identity. Links to Supabase Auth. Separate from marketplace users.

Column	Type	Info
id	UUID	PK
auth_uid	UUID	Links to auth.users
email	TEXT	NOT NULL
first_name	TEXT
last_name	TEXT
phone	TEXT

RLS Policies

• User can read/update own record (auth_uid = auth.uid())
• Insert own record on signup

Repository

ClubUserRepository

club_memberships

RLSNot in schema doc▼

>Links club_user to breed_club with role (admin/zuchtwart/member) and status. One user can belong to multiple clubs.

Column	Type	Info
id	UUID	PK
club_user_id	UUID	FK → club_users(id)
club_id	UUID	FK → breed_clubs(id)
role	TEXT	CHECK: admin \| zuchtwart \| member
status	TEXT	CHECK: active \| inactive \| pending
membership_number	TEXT

RLS Policies

• Readable within own club
• Insert/update/delete restricted to club admins
• Bootstrap logic for first admin

club_breeders

RLSNot in schema doc▼

>Club-context breeder profile. Kennel name, breeding approval, facility inspection. Optional bridge to WelpenMatch via welpenmatch_profile_id.

Column	Type	Info
id	UUID	PK
club_membership_id	UUID	FK → club_memberships(id)
kennel_name	TEXT
breeding_approval_status	TEXT
facility_inspection_status	TEXT
welpenmatch_profile_id	UUID	FK → breeder_profiles(id) — bridge to marketplace

RLS Policies

• Access propagated through club membership and role checks
• Breeders can view/update own record

club_dogs

RLSNot in schema doc▼

>Club-registered breeding dogs. Registration number, microchip, breed details. Linked to club_breeders.

Column	Type	Info
id	UUID	PK
club_breeder_id	UUID	FK → club_breeders(id)
registration_number	TEXT
name	TEXT	NOT NULL
breed_id	UUID	FK → dog_breeds(id)
sex	TEXT	CHECK: male \| female
birth_date	DATE
microchip_number	TEXT
color	TEXT
markings	TEXT

RLS Policies

• Access propagated through club membership
• Breeders manage own dogs

health_tests

RLSNot in schema doc▼

>Per-dog health certificates and test results. Verified by Zuchtwart/admin. Core of the compliance pipeline.

Column	Type	Info
id	UUID	PK
club_dog_id	UUID	FK → club_dogs(id)
test_type	TEXT	e.g. HD, ED, eye test
result	TEXT
certificate_url	TEXT
verified_by	UUID	FK → club_users(id)
verified_at	TIMESTAMPTZ

RLS Policies

• Access propagated through club membership
• Breeders manage own dog tests
• Zuchtwarte can verify

📊 Admin & Operations 12 tables

audit_log

1 JSONB▼

Generic audit trail for operational/security logging via trigger.

Column	Type	Info
id	UUID	PK
table_name	TEXT	NOT NULL
operation	TEXT	NOT NULL
user_id	UUID	Not FK-enforced
changed_data	JSONB

platform_reports

RLSNot in schema doc▼

Off-platform solicitation reports. Buyers can anonymously report breeders bypassing the platform.

Column	Type	Info
id	UUID	PK
reporter_id	UUID	FK → users(id)
reported_breeder_id	UUID	FK → users(id)
inquiry_id	UUID	FK → inquiries(id)
reason	TEXT	off_platform_payment \| external_contact \| pressure_to_bypass
status	TEXT	pending \| valid \| invalid \| needs_info
reviewed_by	UUID	FK → users(id)

Repository

PlatformReportRepository

breeder_violations

RLSNot in schema doc▼

3-strikes moderation: warning → suspension → ban. Links to platform reports.

Column	Type	Info
id	UUID	PK
breeder_id	UUID	FK → users(id)
report_id	UUID
strike_number	INTEGER	CHECK 1–3
action	TEXT	warning \| suspension \| ban
revoked	BOOLEAN
suspension_until	TIMESTAMPTZ
issued_by	UUID	FK → users(id)

Repository

BreederViolationRepository

dog_ownership_records

RLSNot in schema doc▼

Chain of ownership for dogs purchased via the platform. Links buyer, breeder, puppy, and payment.

Column	Type	Info
id	UUID	PK
puppy_id	UUID	FK → puppies(id)
litter_id	UUID	FK → litters(id)
buyer_id	UUID	FK → users(id)
breeder_id	UUID	FK → users(id)
inquiry_id	UUID	FK → inquiries(id)
payment_id	UUID	FK → payments(id)
transfer_type	TEXT	sale \| rehoming \| return_to_breeder
transfer_date	TIMESTAMPTZ

Repository

DogOwnershipRepository

rehoming_requests

RLSNot in schema doc▼

Buyer-initiated rehoming workflow: return to breeder or find new home.

Column	Type	Info
id	UUID	PK
ownership_record_id	UUID	FK → dog_ownership_records(id)
requester_id	UUID	FK → users(id)
reason	TEXT	NOT NULL
preferred_outcome	TEXT	return_to_breeder \| find_new_home
status	TEXT	requested \| breeder_contacted \| breeder_accepted \| platform_handling \| resolved

Repository

RehomingRequestRepository

support_tickets

RLSNot in schema doc▼

User support tickets created via Telegram bot.

Column	Type	Info
id	UUID	PK
user_id	UUID	FK → users(id) CASCADE
chat_id	BIGINT	Telegram chat ID
category	TEXT	technical \| account \| payment \| other
description	TEXT
status	TEXT	open \| in_progress \| resolved \| closed
attachment_urls	TEXT[]

email_subscriptions

Not in schema doc▼

Email subscription preferences per user.

email_send_log

Not in schema doc▼

Transactional email delivery log.

telegram_link_tokens

Not in schema doc▼

Tokens for linking Telegram accounts to Welpenmatch users.

telegram_bot_sessions

Not in schema doc▼

Telegram bot conversation state persistence.

signup_attempts

Not in schema doc▼

Rate limiting for signup attempts.

import_records + related

Not in schema doc▼

Data import tracing: import_records, import_documents, import_timeline_events, import_vaccination_records.

Entity Relationship Diagram

Core tables and foreign-key relationships. Scroll/zoom to explore.

erDiagram
    users ||--o| breeder_profiles : "1:1 profile"
    users ||--o| buyer_profiles : "1:1 profile"
    users ||--o{ dogs : "owns"
    users ||--o{ litters : "creates"
    users ||--o{ puppies : "lists"
    users ||--o{ rehoming_dogs : "owns"
    users ||--o{ inquiries : "buyer"
    users ||--o{ inquiries : "breeder"
    users ||--o{ applications : "submits"
    users ||--o{ applications : "receives"
    users ||--o{ notifications : "receives"
    users ||--o{ reviews : "writes"
    users ||--o{ reviews : "reviewed"
    users ||--o{ waitlists : "joins"
    users ||--o{ breeder_blog_posts : "writes"
    users ||--o{ user_activities : "generates"
    users ||--o{ favorite_puppies : "saves"
    users ||--o{ favorite_breeders : "saves"
    users ||--o{ messages : "sends"
    users ||--o| subscriptions : "has"
    users ||--o| stripe_connect_accounts : "has"

    dog_breeds ||--o{ dogs : "classifies"
    dog_breeds ||--o{ litters : "classifies"
    dog_breeds ||--o{ puppies : "classifies"
    dog_breeds ||--o{ rehoming_dogs : "classifies"
    dog_breeds ||--o{ cantonal_breed_regulations : "regulated"
    dog_breeds ||--o{ breed_clubs : "associated"
    dog_breeds ||--o{ club_dogs : "classifies"

    dogs ||--o{ litters : "mother"
    dogs ||--o{ litters : "father"
    dogs ||--o{ expected_litters : "parent"

    litters ||--o{ puppies : "contains"
    litters ||--o{ waitlists : "has"
    litters ||--o{ applications : "target"
    litters ||--o{ breeder_blog_posts : "referenced"

    puppies ||--o{ applications : "target"
    puppies ||--o{ reviews : "about"
    puppies ||--o{ waitlists : "offered"
    puppies ||--o{ favorite_puppies : "favorited"
    puppies ||--o{ dog_ownership_records : "transferred"

    rehoming_dogs ||--o{ applications : "target"

    inquiries ||--o{ inquiry_messages : "has"
    inquiries ||--o{ inquiry_preferences : "has"
    inquiries ||--o{ inquiry_status_history : "tracks"
    inquiries ||--o{ payments : "paid_via"
    inquiries ||--o{ reservations : "reserved_via"
    inquiries ||--o{ platform_reports : "reported"
    inquiries ||--o{ dog_ownership_records : "resulted_in"

    payments ||--o{ reservations : "deposit"

    chat_rooms ||--o{ messages : "contains"

    breeder_sites ||--o{ breeder_site_pages : "has"

    cantons ||--o{ cantonal_breed_regulations : "has"
    cantons ||--o{ cantonal_dog_regulations : "has"
    cantons ||--o{ cantonal_breed_categories : "has"
    cantons ||--o{ cantonal_restricted_areas : "has"

    dog_ownership_records ||--o{ rehoming_requests : "has"

    breed_clubs ||--o{ breed_club_events : "has"
    breed_clubs ||--o{ breeder_clubs : "has"
    breed_clubs ||--o{ club_memberships : "has"

    club_users ||--o{ club_memberships : "has"
    club_memberships ||--o{ club_breeders : "has"
    club_breeders ||--o{ club_dogs : "has"
    club_dogs ||--o{ health_tests : "has"

    breeder_violations }o--|| users : "breeder"
    platform_reports }o--|| users : "reporter"

    users {
        uuid id PK
        uuid auth_uid UK
        text email UK
        text user_type
    }
    breeder_profiles {
        uuid id PK
        uuid user_id FK
        text breeder_name
        text slug UK
        text verification_status
    }
    buyer_profiles {
        uuid id PK
        uuid user_id FK
    }
    dogs {
        uuid id PK
        uuid breeder_id FK
        uuid breed_id FK
        text gender
        text breeding_status
    }
    litters {
        uuid id PK
        uuid breeder_id FK
        uuid mother_id FK
        uuid father_id FK
        text status
    }
    puppies {
        uuid id PK
        uuid breeder_id FK
        uuid litter_id FK
        uuid breed_id FK
        text status
        decimal price
    }
    inquiries {
        uuid id PK
        uuid buyer_id FK
        uuid breeder_id FK
        text status
        decimal compatibility_score
    }
    payments {
        uuid id PK
        uuid inquiry_id FK
        decimal amount
        text status
    }
    subscriptions {
        uuid id PK
        uuid breeder_id FK
        text tier
        text status
    }
    club_users {
        uuid id PK
        uuid auth_uid
        text email
    }
    club_memberships {
        uuid id PK
        uuid club_user_id FK
        uuid club_id FK
        text role
    }
    club_breeders {
        uuid id PK
        uuid club_membership_id FK
        text kennel_name
    }
    club_dogs {
        uuid id PK
        uuid club_breeder_id FK
        text registration_number
    }
    health_tests {
        uuid id PK
        uuid club_dog_id FK
        text test_type
        text result
    }

Key Data Flows

How data moves through the system for core business processes.

🐕 Buyer Inquiry Flow

Buyer browses puppies / litters → tracked in user_activities

↓

Sends inquiry → inquiries + inquiry_preferences created

↓

Chat begins → inquiry_messages >(with optional AI auto-response from breeder_faqs)

↓

Status changes tracked in inquiry_status_history

↓

If approved → payments → reservations → dog_ownership_records

🏠 Breeder Onboarding

↓

Complete profile → breeder_profiles >(9-step onboarding)

↓

Compliance checks → breeder_compliance_steps

↓

Subscription → subscriptions + stripe_connect_accounts

↓

Add dogs → dogs → Create litters → List puppies

💳 Payment Flow

Inquiry status → 'reserviert'

↓

>Deposit request created → payments >(type = 'reservation_deposit')

↓

Stripe checkout → webhook → webhook_events >(idempotent)

↓

Payment confirmed → reservations >created with confirmation number

↓

On pickup → dog_ownership_records + puppies.status → 'sold'

⚖️ Moderation Flow

>Buyer reports breeder → platform_reports

↓

Admin reviews → valid/invalid

↓

>If valid → strike issued → breeder_violations >(1=warning, 2=suspension, 3=ban)

↓

If suspended → breeder_profiles.suspended_until set