Database Schema: The 10 tables in detail

The Odys platform is built on a clean, relational PostgreSQL schema that powers both the professional and client experience. This document dives deep into the 10 core tables that define data flow, user interactions, and business logic — from scheduling and payments to messaging and reviews.

Overview

The schema consists of 10 tables, each serving a distinct domain concern. At the center sits the professionals table, which anchors all other entities. Relationships are enforced via foreign keys with ON DELETE CASCADE, ensuring data integrity when a professional or client is removed. Timestamps like createdAt and updatedAt are consistently applied, and UUIDs provide secure, scalable primary keys. The design supports real-time booking, recurring sessions, Stripe integration, and WhatsApp notifications — all while maintaining clarity and performance.

Core Tables & Their Roles

The professionals table (24 columns) stores all practitioner data: contact info, service details (sessionDuration, sessionPrice), payment preferences (paymentType, pixKey), and subscription state (plan, stripeSubscriptionId). Unique constraints on userId and slug ensure one account per user and a clean public profile URL.

Linked via professionalId, the availability table defines weekly time slots using dayOfWeek, startTime, and endTime. This enables precise calendar rendering. The clients table tracks client profiles, while clientNotes stores private, encrypted notes tied to both professional and client.

Recurring bookings are managed by recurringSchedules, supporting weekly, biweekly, or twice-weekly patterns. Appointments derive from these or one-off bookings, with status tracking in appointments — including payment state via stripePaymentIntentId and reminder flags.

Communication is handled by messages, where sender distinguishes between professional and client. The reviews table enforces one review per appointment via a unique constraint on appointmentId. Finally, follows tracks user interest in professionals, and notifications delivers timely alerts across the system.

Design decisions

The schema avoids joins where possible by denormalizing key fields (e.g., professionalId on appointments). This optimizes read performance for dashboard queries. Using text for time fields ("09:00") simplifies frontend handling, though it sacrifices native time operations. All foreign keys cascade-delete, reflecting the tenant-like ownership model: a professional’s data belongs to them. Defaults like autoConfirm: false and active: true ensure safe initial states.

Potential improvements

1. Add index on appointments.recurringScheduleId — This join is likely frequent for recurring session management. Add it in schema.ts around line 180 to speed up lookups.
2. Enforce dayOfWeek constraints at DB level — Add a check constraint to availability.dayOfWeek (0–6) to prevent invalid values. Currently, this is only validated in code.
3. Split paymentType logic into a dedicated table — The current string-based paymentType (upfront, percentage) could be normalized to support future methods. Consider a payment_methods table to avoid magic strings.

References

• src/lib/db/schema.ts: Lines 1–300 — Full schema definition with Drizzle ORM
• api/appointments/[id]/route.ts — Uses appointments and recurringSchedules
• api/stripe/webhook/route.ts — Updates professionals.plan and stripeSubscriptionId