Database Schema

All tables use a bigint auto-increment id, Laravel timestamps (created_at, updated_at), and InnoDB with utf8mb4 on MySQL (:memory: SQLite under phpunit). Migrations live in backend/database/migrations/.

The schema spans 40+ tables across nine functional clusters:

Cluster	Core tables
Identity & RBAC	system_admin, users, tenants, tenant_user, roles, permissions, role_permission, role_user
Audit	audit_logs
Auth helpers	personal_access_tokens, password_reset_tokens
Patients & medical	clients, pets, visits, prescriptions, prescription_items, lab_results, medical_attachments, pet_vaccinations, pet_documents, pet_health_logs
Caregivers & sharing	pet_shares, pet_caregivers
Scheduling	appointments
Billing	invoices, payments
Inventory	medicines, stock_movements, suppliers, purchase_orders, purchase_order_items
Notifications	reminders, reminder_templates, communication_opt_outs, service_settings
Marketplace & commerce	order_requests (uses tenants' provider columns)
Infra	cache, cache_locks, jobs, failed_jobs, job_batches

Entity-relationship diagram

---

Identity & RBAC

system_admin

Platform operators using the Filament admin panel. Separate auth guard, no tenant_id (operates above tenancy).

Column	Type	Notes
id	bigint PK
name, email (UK), phone, password	strings
email_verified_at, remember_token	nullable

users

Clinic staff and pet owners share this table.

Column	Type	Notes
id	bigint PK
name, email (UK), phone, password	strings
account_type	string	staff or owner
current_tenant_id	FK → tenants nullable	currently selected tenant for the staff user
email_verified_at, remember_token	nullable

tenants

One SaaS customer per row. Carries every operator-editable field plus the public-marketplace columns.

Column	Type	Notes
id	bigint PK
name, slug (UK), email (UK)	strings
phone, address	nullable
subscription_plan	enum	trial, starter, growth, premium, enterprise (default trial)
status	enum	active, suspended, cancelled
business_type	string(20)	clinic, pharmacy, retail, breeder, groomer, trainer
currency	string(3)	Default INR
timezone	string	Default Asia/Kolkata
latitude, longitude	decimal(10,7) nullable	Marketplace geo
service_radius_km	smallint nullable
headline, bio	text nullable	Public profile copy
services	json nullable	Array of strings
premium_features	json nullable	Slugs unlocked by the plan
is_public	boolean	Whether tenant appears in /providers

Migrations: 2026_04_15_175000_create_tenants_table.php, 2026_04_18_090000_add_business_type_…, 2026_04_19_210000_add_locale_fields_…, 2026_04_20_090200_add_provider_fields_….

tenant_user (pivot)

Column	Type	Notes
tenant_id, user_id	FK	cascade
role	string	clinic_admin, veterinarian, staff, pharmacy, retail_counter
status	string	active, invited, disabled
is_default	boolean

permissions

Catalog of capabilities; seeded by PermissionSeeder.

Column	Type	Notes
id	bigint PK
key	string UK	e.g. appointment.create, invoice.convert, payment.refund
description	string	Human-readable label

roles

Tenant-scoped roles; can be platform default (no tenant_id).

Column	Type	Notes
id	bigint PK
tenant_id	FK nullable	null = platform-default role
name	string
slug	string	unique within tenant
is_system	boolean	system roles can't be edited

role_permission, role_user

Standard pivot tables: (role_id, permission_id), (role_id, user_id, tenant_id).

audit_logs

Append-only audit trail of model changes and explicit events.

Column	Type	Notes
id	bigint PK
tenant_id	FK nullable	null = system-level (e.g. tenant create)
actor_user_id, actor_system_admin_id	FK nullable	one-of
auditable_type, auditable_id	morph	target row
action	string	create, update, delete, or custom (e.g. receipt.emailed)
before, after	json nullable	snapshots
ip, user_agent, request_id	strings nullable	from RecordAuditContext middleware

---

Auth helpers

personal_access_tokens

Standard Sanctum table. Used by both API + web SPAs (Bearer flow).

password_reset_tokens

Column	Type	Notes
id	bigint PK
user_id	FK	cascade
token_hash	string(64)	SHA-256 of token sent in email
expires_at, used_at	datetime nullable
ip_issued, ip_used	strings nullable	for audit

Active token uniqueness is enforced via a partial-unique behaviour at the model level; new requests invalidate older unused tokens.

---

Patients & medical

clients

Pet owners as seen by a clinic; may or may not have a paired users row (cash walk-in vs. owner-app user).

Column	Type	Notes
id	bigint PK
tenant_id	FK	cascade
owner_user_id	FK → users nullable	cascade-on-delete-set-null
full_name, email, phone, address	strings, mostly nullable
status	enum	active, inactive

pets

Column	Type	Notes
id	bigint PK
tenant_id, client_id	FK
name, species	required
breed, sex (male/female/unknown), date_of_birth, weight_kg, color_markings, allergies, medical_flags	nullable
status	enum	active, inactive, deceased
public_slug	string UK nullable	URL slug for the public collar-tag landing page
public_profile_enabled	boolean	feature flag
lost_mode_enabled_at	timestamp nullable	when lost-mode was toggled on
lost_mode_message	text nullable	owner's plea text

Migrations: base + 2026_04_22_100100_add_public_profile_to_pets_table.php.

visits

A consultation. Carries the SOAP narrative + vitals + status.

Column	Type	Notes
id	bigint PK
tenant_id, pet_id, client_id, veterinarian_user_id	FK
visit_at	datetime
chief_complaint, subjective, objective, assessment, plan	text nullable	SOAP fields
weight_kg, temperature_c, heart_rate_bpm, respiratory_rate_bpm	decimal/integer nullable	vitals
status	enum	draft, complete, cancelled
notes	text nullable

prescriptions

A clinical Rx envelope. One visit may have one prescription.

Column	Type	Notes
id	bigint PK
tenant_id, pet_id, client_id, prescribed_by_user_id	FK
visit_id	FK nullable	cascade-set-null when visit deleted
number	string	e.g. RX-2026-0001
issued_at	datetime
status	enum	draft, active, completed, cancelled
valid_until	date nullable
notes	text nullable

prescription_items

Each line of an Rx.

Column	Type	Notes
id	bigint PK
prescription_id	FK	cascade
medicine_id	FK nullable	nullable for free-text "external" Rx
medicine_name	string	snapshot
dosage, frequency, duration, route	strings/nullable	e.g. 5mg/kg, BID, 7d, oral
quantity	integer
refills_allowed, refills_remaining	integer
instructions	text nullable

lab_results

Column	Type	Notes
id	bigint PK
tenant_id, pet_id, visit_id (nullable), recorded_by_user_id	FK
panel	string	e.g. CBC, Biochem, Urinalysis
analyte	string
value, unit	strings
reference_range	string nullable
flag	enum	normal, low, high, critical (default normal)
recorded_at	datetime
notes	text nullable

medical_attachments

Polymorphic file attachments. Hangs off any clinical resource.

Column	Type	Notes
id	bigint PK
tenant_id, pet_id	FK
attachable_type, attachable_id	morph nullable	parent (visit / lab / prescription)
uploaded_by_user_id	FK
disk	string	always private (controller hard-codes)
path	string	storage key
original_name, mime_type	strings
size_bytes	integer
category	string	xray, ultrasound, lab_pdf, consent, other
description	text nullable

pet_vaccinations

Column	Type	Notes
id	bigint PK
pet_id	FK	cascade
tenant_id	FK nullable	null = owner-logged outside any clinic
recorded_by_user_id	FK
source	enum	clinic, owner (derived from URL prefix in controller)
name, manufacturer, batch	strings/nullable
administered_on	date
next_due_at	date nullable	drives the "Due Soon" feed
notes	text nullable

pet_documents

Owner-uploaded documents (insurance card, X-ray, lab PDF, ID).

Column	Type	Notes
id	bigint PK
pet_id	FK	cascade
uploaded_by_user_id	FK
disk	string	always local
path	string	storage key
original_name, mime_type	strings
size_bytes	integer
category	enum	lab_report, xray, ultrasound, insurance, id, other
issued_on	date nullable
notes	text nullable

pet_health_logs

Owner-written + clinic-noted vitals/symptom journal.

Column	Type	Notes
id	bigint PK
pet_id	FK
tenant_id	FK nullable	null = home log
logged_by_user_id	FK nullable
logged_at	datetime
weight_kg, temperature_c	decimal nullable
appetite	enum	low, normal, high (nullable)
symptoms	json nullable	array of strings
notes	text nullable
source	enum	owner, clinic

---

Caregivers & sharing

pet_shares

QR / link-based pet share for clinics.

Column	Type	Notes
id	bigint PK
pet_id	FK	cascade
owner_user_id	FK	always set server-side from authenticated user
code	string UK	random 8-char human-friendly code
expires_at	datetime nullable
revoked_at	datetime nullable
max_redemptions, redeemed_count	integer

pet_caregivers

Email-based ongoing co-owner / family invite.

Column	Type	Notes
id	bigint PK
pet_id	FK	cascade
invited_by_user_id	FK	the primary owner
invited_email, invited_name	strings
caregiver_user_id	FK nullable	bound on accept
invite_token	string(48) UK nullable	nulled out after accept (one-shot)
access_level	enum	view, full
accepted_at, revoked_at	timestamps nullable

---

Scheduling

appointments

Column	Type	Notes
id	bigint PK
tenant_id, pet_id, client_id	FK
veterinarian_user_id	FK nullable
appointment_type	string	consult, vaccination, surgery, …
status	enum	scheduled, confirmed, checked_in, with_vet, completed, cancelled, no_show
booking_source	enum	clinic_portal, owner_app, phone, walk_in
scheduled_start, scheduled_end	datetime
reason, notes	text nullable
rescheduled_from_start_at, rescheduled_at, rescheduled_by_user_id	nullable	reschedule audit
cancellation_reason, cancelled_at, cancelled_by_user_id	nullable	cancel audit
checked_in_at, with_vet_at, completed_at	timestamps nullable	workflow timestamps

Migrations: base + 2026_04_19_090000_add_reschedule_cancel_fields_… + 2026_04_24_090000_add_workflow_timestamps_….

---

Billing

invoices

Column	Type	Notes
id	bigint PK
tenant_id, client_id, pet_id (nullable)	FK
invoice_number	string UK	(per-tenant uniqueness recommended — see audit)
invoice_type	enum	estimate, invoice
status	enum	draft, pending, partial, paid, void
subtotal, tax_total, discount_total, grand_total, balance_due	decimal(12,2)
currency	string(3)
issued_at, due_at	dates
notes	text nullable
receipt_emailed_at, receipt_emailed_to, receipt_pdf_path	nullable	post-receipt audit

Migrations: base + 2026_04_19_150000_add_receipt_fields_to_invoices_table.php.

payments

Column	Type	Notes
id	bigint PK
tenant_id, invoice_id	FK
amount	decimal(12,2)
payment_method	enum	cash, card, upi, bank_transfer, online
status	enum	pending, captured, failed, refunded
reference_number	string nullable
paid_at	datetime nullable
gateway	string nullable	e.g. razorpay
gateway_order_id, gateway_payment_id, gateway_signature	strings nullable
raw_payload	json nullable	response body kept for forensics
refund_id, refunded_at, refund_reason	nullable	refund audit

Migration: base + 2026_04_21_090000_add_provider_columns_to_payments_table.php.

---

Inventory

See Inventory module for the operational story.

medicines

Tenant-scoped catalog. Used by clinic, pharmacy, and retail tenants.

Column	Type	Notes
id	bigint PK
tenant_id	FK
sku	string(64)	unique per tenant
name, generic_name, manufacturer	strings/nullable
category	string(60)	Antibiotic, NSAID, Food, …
kind	string(20)	medicine, vaccine, pet_food, supplement, accessory, grooming
form, strength, unit	nullable
stock_quantity, minimum_stock, maximum_stock, reorder_level	integers
unit_price, selling_price	decimal(12,2)
requires_prescription	boolean
batch_number, expiry_date, storage_instructions	nullable
indications, side_effects	json nullable
status, notes	nullable

stock_movements

Append-only ledger of every stock change.

Column	Type	Notes
id	bigint PK
tenant_id, medicine_id, performed_by_user_id (nullable), pet_id (nullable)	FK
type	enum	purchase, dispense, return, wastage, adjustment, transfer
quantity, previous_stock, new_stock	integers
unit_cost, total_value	decimal nullable
reference, vendor, invoice_number, prescription_number	strings nullable
batch_number, expiry_date	nullable
reason, notes	nullable
status	enum	completed, pending_approval, cancelled

suppliers, purchase_orders, purchase_order_items

Standard inventory shape with PO lifecycle: draft → ordered → partial → received → cancelled. Receive flow writes one stock_movements row per received item via PurchaseOrderController::receive.

---

Notifications

reminders

Column	Type	Notes
id	bigint PK
tenant_id	FK
client_id, pet_id	FK nullable
title	string
reminder_type	enum	vaccination, deworming, follow_up, payment_due, general
channel	enum	email, sms, push, in_app
due_at	datetime
status	enum	pending, sent, dismissed, failed
payload	json nullable
attempts, last_attempt_at	integer / timestamp	retry counter
sent_at, last_error, provider_message_id, to_address, template_slug	nullable	post-send audit

Migrations: base + 2026_04_19_120000_extend_reminders_and_create_comms_tables.php.

reminder_templates

Column	Type	Notes
id	bigint PK
tenant_id	FK nullable	null = platform default
slug	string	unique per tenant (or per platform)
channel	enum	email, sms, push, in_app
subject, body	strings/text	with {{variable}} interpolation
html_body	text nullable	for HTML email
from_name	string nullable	per-template From: override

Seeded slugs: general_reminder, appointment_reminder, vaccination_due, payment_due, receipt_email, caregiver_invite, order_request_received. See database/migrations/2026_04_23_090100_seed_transactional_email_templates.php.

communication_opt_outs

Column	Type	Notes
id	bigint PK
tenant_id	FK
channel	enum	email, sms, push
contact	string	normalised lower-cased trimmed
reason	string nullable
opted_out_at	timestamp

Unique index (tenant_id, channel, contact).

service_settings

Per-tenant key/value store for notification + integration config. Backs the Filament admin "Notification settings" page.

Column	Type	Notes
id	bigint PK
tenant_id	FK nullable	null = platform default
group	string	mail, sms, push, razorpay, …
key	string	scoped to group
value	text nullable	encrypted at rest for secrets

---

Marketplace & commerce

order_requests

Owner-initiated refill / reorder ping to a pharmacy or retail tenant.

Column	Type	Notes
id	bigint PK
target_tenant_id	FK	the seller
pet_id, requester_user_id	FK
source_prescription_id	FK nullable	when refill draft was used
kind	enum	refill, reorder, custom
status	enum	pending, accepted, fulfilled, rejected, cancelled
items	json	[{medicine_name, quantity, notes}, …]
delivery_preference	enum	pickup, delivery
delivery_address	text nullable
notes	text nullable
accepted_at, fulfilled_at, cancelled_at, rejected_at	timestamps nullable
reject_reason	string nullable

Tenants' provider columns (is_public, latitude, longitude, business_type) drive the marketplace listing this is paired with.

---

Foreign-key conventions

• All FKs use ->foreignId('xxx_id')->constrained()->cascadeOnDelete() unless the parent can vanish without invalidating the row, in which case ->nullOnDelete().
• Tenant scoping is always modelled with a non-nullable tenant_id column except for resources that legitimately exist outside any tenant (caregivers, public-share lookups, platform-default templates, pet-owner-only health logs).

Indexes worth knowing

• appointments(tenant_id, scheduled_start) — calendar.
• invoices(tenant_id, status) — dashboard aggregations.
• reminders(tenant_id, due_at, status) — dispatch scheduler.
• pets(public_slug) — lost-pet lookup.
• tenants(is_public, business_type, latitude, longitude) — marketplace shortlist.
• communication_opt_outs(tenant_id, channel, contact) — UNIQUE; the lookup happens on the same triple.
• pet_caregivers(invite_token) — token-redeem lookup.

Indexes to consider adding (audit)

See AUDIT.md for full context. Most relevant deferred items:

• Composite unique (tenant_id, invoice_number) rather than global unique (multi-tenant clash today).
• audit_logs(tenant_id, auditable_type, auditable_id) for entity history queries.
• order_requests(target_tenant_id, status, created_at) for the seller's inbox.