DatabaseSaaSArchitectureBangkok

Database Design Patterns for Bangkok SaaS and Booking Platforms

4 May 2026 · by Yunmin Shin

What Database Decisions Define a SaaS Architecture?

Most of the SaaS-shaped applications we build in Bangkok aren't venture-funded startups — they're operational tools for real businesses: a multi-branch aesthetic clinic group that needs one booking system across Thonglor, Ekkamai, and Silom locations, a restaurant group managing LINE ordering across several outlets, a real estate brokerage tracking listings and leads across multiple agents. These have the same core database requirements as any SaaS product: multiple organizations (or branches) share the same infrastructure, but their data must be strictly isolated from each other, and features like audit logs, soft deletes, and role-aware access control are standard requirements from day one, not something to bolt on after a customer asks for them.

The database design you choose at the start will either support or constrain the product for years. These are the patterns worth getting right the first time.

How Do You Handle Multi-Tenancy?

There are three approaches, each with different trade-offs:

Shared database, shared schema (most common for early-stage SaaS): All tenants share the same tables. Every table has an organization_id foreign key — for a clinic group, this might represent each branch, or the group itself if branches share inventory and staff. Row-Level Security in PostgreSQL enforces isolation at the database level. This approach is simple to implement, cost-efficient, and sufficient for most products up to hundreds of tenants.

Shared database, separate schemas: Each tenant gets their own PostgreSQL schema (namespace) within the same database. Migrations must run across all schemas. This adds complexity but improves isolation and makes per-tenant data operations (backup, export) easier — worth considering if one clinic branch operator wants their own data export for a franchise agreement.

Separate databases per tenant: Maximum isolation, suitable for enterprise customers with strict compliance requirements. Operationally complex — connection pool management becomes non-trivial. Use only when a customer contract specifically requires it, which in practice is rare for the Bangkok SMB and multi-branch clientele Bluewich builds for.

Start with the shared schema approach and RLS. You can migrate to separate schemas later if a specific client genuinely needs it.

What Does a Clinic Booking Schema Actually Look Like?

Concretely, a multi-branch clinic booking platform needs at minimum:

CREATE TABLE branches (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL,
  name TEXT NOT NULL,          -- 'Thonglor', 'Ekkamai'
  line_oa_id TEXT,             -- LINE Official Account for this branch
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE patients (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL,
  line_user_id TEXT,           -- from LIFF login, not a password
  full_name TEXT NOT NULL,
  phone TEXT,
  preferred_language TEXT,     -- 'th', 'en', 'ko', 'zh'
  deleted_at TIMESTAMPTZ
);

CREATE TABLE appointments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL,
  branch_id UUID NOT NULL REFERENCES branches(id),
  patient_id UUID NOT NULL REFERENCES patients(id),
  treatment_id UUID NOT NULL,
  staff_id UUID,
  scheduled_at TIMESTAMPTZ NOT NULL,
  status TEXT NOT NULL,        -- 'booked', 'confirmed', 'completed', 'no_show'
  deleted_at TIMESTAMPTZ
);

Notice branch_id and organization_id both exist — RLS policies filter on organization_id for tenant isolation, while branch_id handles the more granular question of which physical location a staff member's dashboard should show. line_user_id on patients matters because most Bangkok clinic bookings originate from a LINE OA or LIFF app rather than a traditional email/password signup — the schema should treat LINE identity as a first-class login method, not an afterthought bolted on later.

What Is Soft Delete and Why Use It?

Hard deletes (DELETE FROM appointments WHERE id = ?) permanently remove records. Soft deletes mark a record as deleted with a deleted_at timestamp instead:

ALTER TABLE appointments ADD COLUMN deleted_at TIMESTAMPTZ;

All queries filter by WHERE deleted_at IS NULL. This matters more than usual for clinics: patient and treatment records often need to be retained for a legally defined period even after a patient asks to stop being a customer, and a staff member accidentally cancelling the wrong appointment should be recoverable, not gone. Soft-deleted records also preserve referential integrity — an old appointment shouldn't leave a dangling reference if the patient record it pointed to disappeared.

The tradeoff is that every query must include the deleted_at IS NULL filter. Use a Drizzle or Prisma middleware to apply this filter automatically so it can't be forgotten in a one-off query.

How Do You Build an Audit Log?

Regulations and multi-branch operators increasingly require a record of who changed what and when — particularly useful when a discount was applied to an invoice, or an appointment status was changed by a specific staff account. Implement an audit log table:

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL,
  actor_id UUID NOT NULL,
  action TEXT NOT NULL,      -- 'appointment.status_changed', 'patient.updated'
  resource_type TEXT NOT NULL,
  resource_id UUID NOT NULL,
  changes JSONB,              -- before/after state
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Write to this table inside a database transaction alongside the main operation, or use PostgreSQL triggers for automatic capture. Never delete audit log records — for a clinic, this table is often what settles a dispute about whether a patient was actually informed of a price change or a rescheduled slot.

How Do You Handle Subscription-Aware Access?

Store the tenant's plan tier and feature flags in the organizations table. Check these in your RLS policies and application middleware to restrict access to premium features — for example, a single-branch clinic might get basic LINE booking confirmations, while a multi-branch group pays for cross-branch reporting, automated SMS/LINE appointment reminders, or staff performance dashboards. Design your feature flag system to be additive — new features start disabled and are enabled per tier, rather than disabling features as organizations downgrade, which avoids ugly edge cases where existing data suddenly becomes inaccessible.

What About Thailand's PDPA?

Thailand's Personal Data Protection Act (PDPA) imposes GDPR-like obligations on any business storing personal data, and patient health-adjacent data from a clinic — even something as simple as "booked a filler consultation" — sits in a more sensitive category than a typical e-commerce order. Practically, this means your schema should support: recording consent (when and how a patient agreed to data processing, not just a boolean flag), and a defined path to export or delete a specific patient's data on request without breaking the referential integrity of other patients' records. Soft deletes and audit logs, designed in from the start as described above, make both of these far easier to implement correctly than retrofitting them into a schema that assumed data would live forever.