Booking Reservations Schema — Lab

Diagram Views

erDiagram resources ||--o{ availability_slots : exposes customers ||--o{ reservations : makes availability_slots ||--o{ reservations : books reservations ||--|| reservation_payments : paid_by resources { bigint id PK text name text kind } customers { bigint id PK text email } availability_slots { bigint id PK bigint resource_id FK timestamptz starts_at timestamptz ends_at int capacity } reservations { bigint id PK bigint slot_id FK bigint customer_id FK int quantity text status } reservation_payments { bigint id PK bigint reservation_id FK int amount_cents text status }

SQL Snippets

schema.sql

create table resources (
  id bigint primary key generated always as identity,
  name text not null,
  kind text not null check (kind in ('room','seat','vehicle','service')),
  timezone text not null,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table customers (
  id bigint primary key generated always as identity,
  email text not null unique,
  full_name text not null,
  phone text,
  created_at timestamptz not null default now()
);

create table availability_slots (
  id bigint primary key generated always as identity,
  resource_id bigint not null references resources(id) on delete cascade,
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  capacity integer not null default 1 check (capacity > 0),
  check (ends_at > starts_at),
  unique (resource_id, starts_at, ends_at)
);

create table reservations (
  id bigint primary key generated always as identity,
  slot_id bigint not null references availability_slots(id) on delete cascade,
  customer_id bigint not null references customers(id),
  quantity integer not null default 1 check (quantity > 0),
  status text not null check (status in ('pending','confirmed','cancelled','no_show')),
  created_at timestamptz not null default now()
);

create table reservation_payments (
  id bigint primary key generated always as identity,
  reservation_id bigint not null unique references reservations(id) on delete cascade,
  amount_cents integer not null check (amount_cents >= 0),
  status text not null check (status in ('authorized','captured','failed','refunded')),
  provider text not null,
  created_at timestamptz not null default now()
);

create index idx_slots_resource_start on availability_slots(resource_id, starts_at);
create index idx_reservations_customer_created on reservations(customer_id, created_at desc);

seed.sql

insert into resources (name, kind, timezone)
values
  ('Room A', 'room', 'UTC'),
  ('Room B', 'room', 'UTC');

insert into customers (email, full_name, phone)
values
  ('elena@book.dev', 'Elena Ruiz', '+57-301-000-1000'),
  ('marco@book.dev', 'Marco Silva', '+57-301-000-1001');

insert into availability_slots (resource_id, starts_at, ends_at, capacity)
values
  (1, now() + interval '1 day', now() + interval '1 day 1 hour', 4),
  (1, now() + interval '1 day 2 hours', now() + interval '1 day 3 hours', 4),
  (2, now() + interval '1 day', now() + interval '1 day 1 hour', 2);

insert into reservations (slot_id, customer_id, quantity, status)
values
  (1, 1, 2, 'confirmed'),
  (3, 2, 1, 'pending');

insert into reservation_payments (reservation_id, amount_cents, status, provider)
values
  (1, 5000, 'captured', 'stripe');

queries.sql

-- 1) Slot occupancy
select
  s.id as slot_id,
  r.name as resource_name,
  s.capacity,
  coalesce(sum(case when rv.status in ('pending', 'confirmed') then rv.quantity else 0 end), 0) as reserved_qty,
  s.capacity - coalesce(sum(case when rv.status in ('pending', 'confirmed') then rv.quantity else 0 end), 0) as available_qty
from availability_slots s
join resources r on r.id = s.resource_id
left join reservations rv on rv.slot_id = s.id
group by s.id, r.name
order by s.starts_at;

-- 2) Customer reservation history
select
  rv.id,
  rv.status,
  s.starts_at,
  s.ends_at,
  r.name as resource_name
from reservations rv
join availability_slots s on s.id = rv.slot_id
join resources r on r.id = s.resource_id
where rv.customer_id = 1
order by rv.created_at desc;

-- 3) Revenue captured by day
select
  date_trunc('day', rp.created_at) as day,
  sum(rp.amount_cents) / 100.0 as captured_usd
from reservation_payments rp
where rp.status = 'captured'
group by day
order by day desc;