Data Model

All 31 tables in packages/backend/convex/schema.ts, grouped by domain. Every operational row carries orgId; tenancy is enforced by the orgQuery/orgMutation seam, never by ad-hoc filters. Money is always integer cents. Timestamps are epoch milliseconds unless noted.

Shared validators defined at the top of the schema: role, visitStatus, fulfillmentStatus, paymentStatus, productType, timeEntryType, careEventType, messageChannel, petStatus, issueType, issueSeverity, scheduleStyle — all sourced from @pawdentity/domain enums.

Key relationships — every operational table also carries orgId (enforced by the wrapper, drawn once here)

Identity & tenancy

Table	Purpose	Key fields	Indexes
`orgs`	A tenant business	`name`, `slug` (subdomain in prod, `/o/{slug}` in dev), `timezone`, `logoUrl?`, `accentColor?`, `checkInEarlyMinutes`, `checkInLateMinutes`, `checkOutLateMinutes`, `paymentProvider` (stripe/fluidpay/manual), `active`	`by_slug`
`users`	Global person/login	`email`, `firstName`, `lastName`, `phone?`, `avatarUrl?`, `passwordHash?`, `passwordSalt?` (local provider; Clerk replaces in prod)	`by_email`
`sessions`	Opaque bearer tokens	`userId`, `token`, `expiresAt` (14-day TTL)	`by_token`, `by_user`
`memberships`	User↔org join	`orgId`, `userId`, `role`, `pin?` (4-digit desk PIN), `payRateCents?`, `payType?` (hourly/salary), `title?`, `feedToken?` (calendar feed auth), `active`	`by_org`, `by_user`, `by_org_user`, `by_feed_token`

Facility

Table	Purpose	Key fields	Indexes
`locations`	Physical sites	`name`, `address?`, `phone?`, `color?`, `hours[]` (`{day, open, close, closed}`), `taxRateBps?` (925 = 9.25%), `active`	`by_org`
`rooms`	Bookable spaces	`locationId`, `name`, `kind` (run/suite/room/grooming_station), `capacity`, `active`	`by_org`, `by_location`

CRM

Table	Purpose	Key fields	Indexes
`clients`	Pet owners	`userId?` (portal login link), `firstName`, `lastName`, `email?`, `phone?`, `address?`, `contactMethod?`, `isVip` (policy-masked), `notes?`, `archived`, `searchText` (denormalized), `stripeCustomerId?`	`by_org`, `by_org_user`, `by_org_name` (alphabetical cursor pagination), `by_stripe_customer`; search index `search_name`
`pets`	Animals	`clientId`, `name`, `species` (dog/cat/other), `breed?`, `color?`, `sex?`, `fixed?`, `weightLbs?`, `birthDate?` (ISO), `photoUrl?/photoStorageId?`, `feedingNotes?/medicationNotes?/behaviorNotes?`, `feeding?` (structured: dryFood, dryPortion, wetFood, wetPortion, frequency, notes), `allergies?[]`, `status?` (petStatus), `vaccines[]` (`{name, expiresAt?}`), `issues[]` (legacy flags, superseded by `petIssues`), `microchip?`, `archived`, `searchText`	`by_org`, `by_client`, `by_org_name`; search index `search_name`
`petPhotos`	Gallery	`petId`, `storageId?`, `url?`, `caption?`, `takenBy?`	`by_org`, `by_pet`

Operations

Table	Purpose	Key fields	Indexes
`visits`	Bookings / stays	`petId`, `clientId`, `locationId`, `roomId?`, `orderId?`, `productId?`, `status` (state machine), `scheduleStyle`, `scheduledStart/End`, `checkInAt?/By?`, `checkOutAt?/By?`, `notes?`	`by_org`, `by_org_status`, `by_pet`, `by_client`, `by_org_start`
`careEvents`	Care log	`visitId`, `petId`, `type` (feeding/medication/potty/play/walk/grooming/incident/photo/note), `at`, `byUserId`, `note?`, `photoStorageId?`, `medicationNeedId?` (pairs a dose to a standing need)	`by_org`, `by_visit`, `by_pet`, `by_need`
`medicationNeeds`	Standing meds per pet (v1 parity)	`petId`, `name`, `dosage?`, `frequency?`, `timeOfDay?`, `withFood?`, `note?`, `active`	`by_org`, `by_pet`
`petIssues`	Issue lifecycle (v1 dog_issues parity)	`petId`, `visitId?`, `type`, `severity` (normal/serious), `title`, `note?`, `status` (open/resolved), `openedBy`, `resolvedBy?/At?`, `resolutionNote?`	`by_org`, `by_pet`, `by_org_status`
`documents`	Uploaded files	`petId?/visitId?/clientId?`, `storageId`, `name`, `mimeType` (allowlisted), `sizeBytes`, `kind` (vet_record/document), `uploadedBy`	`by_org`, `by_pet`, `by_visit`, `by_client`

Commerce

Table	Purpose	Key fields	Indexes
`products`	Catalog	`locationId?`, `roomId?` (default room), `type` (service/product/boarding), `name`, `sku`, `priceCents`, `salePriceCents?`, `depositCents?`, `durationMinutes?`, `scheduleStyle?`, `inventoryCount?` (retail on-hand, decremented on sale), `taxable`, `textColor?/bgColor?`, `parentId?` (variants), `active`	`by_org`, `by_org_type`
`orders`	Sales	`clientId`, `locationId`, `number` (per-org, starts 1000), `fulfillment`, `paymentStatus` (unpaid/authorized/deposit_due/deposit_met/paid/refund_due), `subtotalCents`, `taxCents`, `discountCents`, `totalCents`, `paidCents`, `depositCents?`, `placedBy?`, `notes?`	`by_org`, `by_org_number`, `by_client`, `by_org_fulfillment`
`orderItems`	Line items	`orderId`, `productId`, `petId?`, `visitId?`, `description`, `quantity`, `unitPriceCents`, `note?`	`by_org`, `by_order`
`orderEvents`	History feed	`orderId`, `at`, `byUserId?` (absent = system), `kind` (status_change/payment/refund/note/pickup), `summary`	`by_org`, `by_order`
`paymentMethods`	Vaulted cards (display data + provider ref only)	`clientId`, `provider` (stripe/fluidpay/dev), `providerRef`, `brand`, `last4`, `expMonth/Year`, `isDefault`, `active`	`by_org`, `by_client`
`payments`	Charge/refund ledger	`orderId`, `kind` (charge/refund), `provider` (stripe/fluidpay/manual/dev), `paymentMethodId?`, `methodType` (cash/card/bank/wallet), `amountCents`, `status` (succeeded/pending/failed), `last4?`, `byUserId`, `refundOfId?`, `idempotencyKey?`	`by_org`, `by_order`, `by_idempotency`

Staff operations

Table	Purpose	Key fields	Indexes
`shifts`	Scheduled shifts	`locationId`, `userId`, `start`, `end`, `note?`	`by_org`, `by_user`, `by_org_start`
`timeEntries`	Time-clock punches	`userId`, `type` (work/pto/sick/holiday/break/unpaid), `clockInAt`, `clockOutAt?`, `approvedBy?/At?`, `note?`, `editedBy?`	`by_org`, `by_user`, `by_org_open` (`[orgId, clockOutAt]`; open = `clockOutAt` undefined)

Communication

Table	Purpose	Key fields	Indexes
`notes`	Internal broadcast board	`title`, `body`, `pinned`, `requiresAck?`, `authorId`, `readBy[]`	`by_org`
`threads`	Client message threads	`clientId`, `petId?`, `subject`, `lastMessageAt`	`by_org`, `by_client`, `by_org_last`
`messages`	Thread messages	`threadId`, `direction` (outbound/inbound), `channel` (email/sms/in_app), `body`, `sentBy?`, `readAt?`, `attachmentIds?[]`	`by_org`, `by_thread`

Agreements

Table	Purpose	Key fields	Indexes
`agreements`	Versioned liability docs	`seriesKey`, `version` (append-only publishing), `title`, `body`, `requireForBooking`, `active`	`by_org`, `by_org_series`
`signatures`	Signed agreements	`agreementId`, `clientId`, `visitId?`, `signedAt`, `signedName`, `capturedBy?` (staff desk-capture)	`by_org`, `by_agreement`, `by_client`

Config

Table	Purpose	Key fields	Indexes
`foodBrands`	Curated food list (v1 parity)	`species` (dog/cat), `type` (dry/wet), `name`, `priority`, `active`	`by_org`
`rolePolicies`	Per-org permission overrides	`action` (PolicyAction), `minRole`	`by_org`
`reminderConfigs`	Automated reminder rules	`kind` (upcoming_visit/vaccine_expiry/agreement_missing), `offsetHours`, `channel`, `template`, `active`	`by_org`
`reminderLogs`	Sent-reminder dedupe	`configId`, `clientId`, `sentAt`, `summary`, `dedupeKey?`	`by_org`, `by_config`, `by_dedupe`

Platform (landlord)

Table	Purpose	Key fields	Indexes
`applications`	Tenant signups from `/apply`	`businessName`, `contactName`, `email`, `phone?`, `vertical`, `status` (submitted/under_review/approved/rejected), `orgId?`, plus business detail fields	`by_status`
`auditLogs`	Org activity trail	`actorId`, `action`, `entity`, `entityId`, `summary`, `at`	`by_org`, `by_org_at`

Modeling notes

Denormalized searchText on clients and pets ("first last email phone") powers the global search index alongside Convex search indexes.
State machines over booleans: visit status and payment status are explicit unions with transition rules in the domain package — never derived flags.
Append-only where history matters: agreements version instead of edit; orderEvents and payments are ledgers; auditLogs never update.
Legacy bridges: pets.issues[] (string flags) is superseded by the petIssues table but kept for v1 import compatibility.