smb-online/backoffice/db/init.sql

-- smb-crm — sole source-of-truth schema (Postgres).

CREATE TABLE IF NOT EXISTS clients (
    client_id        text PRIMARY KEY,
    business_name    text,
    owner_name       text,
    email            text,
    phone            text,
    niche            text,
    tier             text,
    status           text,
    domain           text,
    stack_notes      text,
    vault_ref        text,
    services         text,
    billing_cycle    text,
    monthly_fee_eur  numeric,
    start_date       date,
    renewal_date     date,
    created_at       timestamptz,
    notes            text,
    notify_channel   text,
    updated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS leads (
    lead_id          text PRIMARY KEY,
    received_at      timestamptz,
    client_id        text,
    source           text,
    name             text,
    contact          text,
    service_interest text,
    message          text,
    status           text,
    notified         boolean,
    updated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS projects (
    project_id    text PRIMARY KEY,
    client_id     text,
    deliverable   text,
    tier          text,
    checklist     text,
    go_live_date  date,
    status        text,
    updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS activity_log (
    id         bigserial PRIMARY KEY,
    ts         timestamptz,
    workflow   text,
    client_id  text,
    action     text,
    detail     text,
    result     text
);

CREATE TABLE IF NOT EXISTS bookings (
    booking_id        text PRIMARY KEY,
    created_at        timestamptz,
    client_id         text,
    customer_name     text,
    customer_contact  text,
    service           text,
    start_time        timestamptz,
    end_time          timestamptz,
    source            text,
    status            text,
    updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS invoices (
    invoice_id   text PRIMARY KEY,
    client_id    text,
    issued_date  date,
    due_date     date,
    amount_eur   numeric,
    period       text,
    status       text,
    paid_date    date,
    updated_at   timestamptz NOT NULL DEFAULT now()
);

-- Per-client login credentials (e.g. the auto-generated Easy!Appointments
-- provider login). Gated by X-CRM-Token even to read, so secrets never leave
-- Postgres.
CREATE TABLE IF NOT EXISTS credentials (
    cred_id      text PRIMARY KEY,
    client_id    text,
    label        text,
    username     text,
    secret       text,
    notes        text,
    created_at   timestamptz,
    updated_at   timestamptz NOT NULL DEFAULT now()
);

-- Booking module (#15): resources, services, and the users / password-reset
-- tables the owner-login tickets build on. All access goes through
-- app/booking_db.py — see that module for the tenancy-safe data-access layer.
--
-- Locations (Filialen) group resources for display/selection purposes only --
-- the actual bookable/concurrency-safe unit stays `resources` (a client with
-- several staff at one location needs each staff member independently
-- bookable at the same time, so hours/notice/buffer/the no-overlap
-- constraint all stay keyed on resource_id, not location_id). No FK to
-- clients or from resources.location_id to here, matching this whole
-- schema's convention: no real FKs anywhere, tenant/existence integrity is
-- enforced in app/booking_db.py via get_location()/get_resource()-style
-- checks before every write.
CREATE TABLE IF NOT EXISTS locations (
    location_id  text PRIMARY KEY,
    client_id    text NOT NULL,
    name         text NOT NULL,
    active       boolean NOT NULL DEFAULT true,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS resources (
    resource_id         text PRIMARY KEY,
    client_id           text NOT NULL,
    name                text NOT NULL,
    active              boolean NOT NULL DEFAULT true,
    min_notice_minutes  integer NOT NULL DEFAULT 60,
    max_advance_days    integer NOT NULL DEFAULT 30,
    buffer_minutes      integer NOT NULL DEFAULT 0,
    created_at          timestamptz NOT NULL DEFAULT now(),
    updated_at          timestamptz NOT NULL DEFAULT now()
);

-- Per-resource weekly opening hours (#16): one open/close interval per
-- weekday (0=Monday .. 6=Sunday, matching Python's date.weekday()); no row
-- for a weekday means the resource is closed that day. Per-resource, not
-- per-client, since multi-resource clients may have staff with different
-- hours (#14).
CREATE TABLE IF NOT EXISTS resource_hours (
    resource_id  text NOT NULL,
    weekday      smallint NOT NULL CHECK (weekday BETWEEN 0 AND 6),
    opens_at     time NOT NULL,
    closes_at    time NOT NULL,
    PRIMARY KEY (resource_id, weekday)
);

CREATE TABLE IF NOT EXISTS services (
    service_id        text PRIMARY KEY,
    client_id         text NOT NULL,
    name              text NOT NULL,
    duration_minutes  integer NOT NULL,
    price             numeric,
    active            boolean NOT NULL DEFAULT true,
    created_at        timestamptz NOT NULL DEFAULT now(),
    updated_at        timestamptz NOT NULL DEFAULT now()
);

-- Owner login. email is globally unique (not per-client) per the spec (#14).
CREATE TABLE IF NOT EXISTS users (
    user_id        text PRIMARY KEY,
    client_id      text NOT NULL,
    email          text NOT NULL UNIQUE,
    password_hash  text NOT NULL,
    created_at     timestamptz NOT NULL DEFAULT now(),
    updated_at     timestamptz NOT NULL DEFAULT now()
);

-- Single-use reset tokens; consuming one (setting used_at) invalidates it.
-- No client_id column: the token itself is the auth boundary, resolved
-- straight to its user_id, same as a signed cancel/reschedule link.
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    token       text PRIMARY KEY,
    user_id     text NOT NULL,
    expires_at  timestamptz NOT NULL,
    used_at     timestamptz
);

-- Double-booking protection lives in Postgres, not app code: one resource
-- can't hold two overlapping bookings, enforced on INSERT and UPDATE alike.
-- btree_gist lets the GiST exclusion constraint use plain "=" on resource_id.
CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings ADD COLUMN IF NOT EXISTS resource_id text;
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS during tstzrange
    GENERATED ALWAYS AS (tstzrange(start_time, end_time, '[)')) STORED;

-- ALTER TABLE ... ADD CONSTRAINT has no IF NOT EXISTS form, so drop-then-add
-- unconditionally to keep this file safe to re-run on every deploy like
-- everything above it. The WHERE clause is load-bearing: without it, a
-- cancelled booking's old time range stays "occupied" forever, permanently
-- blocking that resource+slot from ever being booked again even though the
-- booking itself is dead -- found 2026-09-12 when a cancelled test booking
-- blocked a real reschedule into the same slot.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'bookings_no_overlap'
  ) THEN
    ALTER TABLE bookings DROP CONSTRAINT bookings_no_overlap;
  END IF;
  ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
    EXCLUDE USING gist (resource_id WITH =, during WITH &&)
    WHERE (status <> 'cancelled');
END $$;

ALTER TABLE clients ADD COLUMN IF NOT EXISTS slug         text UNIQUE;
ALTER TABLE clients ADD COLUMN IF NOT EXISTS timezone     text NOT NULL DEFAULT 'Europe/Berlin';
ALTER TABLE clients ADD COLUMN IF NOT EXISTS auto_confirm boolean NOT NULL DEFAULT true;
ALTER TABLE clients ADD COLUMN IF NOT EXISTS ics_token    text;

ALTER TABLE resources ADD COLUMN IF NOT EXISTS location_id text;

-- Generic, permanent backfill (not a one-off for any single client): any
-- resource stuck in the old flat shape (location_id IS NULL) gets a brand
-- new location auto-created with its own name and is attached to it. Keeps
-- protecting against a future resource ending up without a location -- a
-- provisioning bug, a manual INSERT, a restored backup -- since a resource
-- with location_id already set is never touched again by this block.
DO $$
DECLARE r RECORD; new_loc_id text;
BEGIN
  FOR r IN SELECT resource_id, client_id, name FROM resources WHERE location_id IS NULL LOOP
    new_loc_id := 'LOC-' || floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint
                   || '-' || substr(md5(random()::text), 1, 6);
    INSERT INTO locations (location_id, client_id, name) VALUES (new_loc_id, r.client_id, r.name);
    UPDATE resources SET location_id = new_loc_id WHERE resource_id = r.resource_id;
  END LOOP;
END $$;

-- keep updated_at fresh on row changes
CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

-- CREATE OR REPLACE TRIGGER, not plain CREATE TRIGGER: this whole DO block is
-- one statement, so on a redeploy where e.g. clients_touch already exists, a
-- plain CREATE would raise and roll back the entire block -- including the
-- resources/services/users triggers this migration is adding -- before ever
-- reaching them, since they're later in the array.
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['clients','leads','projects','bookings','invoices',
                           'credentials','locations','resources','services','users'] LOOP
    EXECUTE format(
      'CREATE OR REPLACE TRIGGER %I_touch BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION touch_updated_at()',
      t, t);
  END LOOP;
END $$;

CREATE INDEX IF NOT EXISTS leads_received_idx   ON leads (received_at DESC);
CREATE INDEX IF NOT EXISTS clients_status_idx   ON clients (status);
CREATE INDEX IF NOT EXISTS activity_ts_idx      ON activity_log (ts DESC);
CREATE INDEX IF NOT EXISTS credentials_client_idx ON credentials (client_id);
CREATE INDEX IF NOT EXISTS locations_client_idx ON locations (client_id);
CREATE INDEX IF NOT EXISTS resources_client_idx ON resources (client_id);
CREATE INDEX IF NOT EXISTS resources_location_idx ON resources (location_id);
CREATE INDEX IF NOT EXISTS services_client_idx  ON services (client_id);
CREATE INDEX IF NOT EXISTS bookings_client_idx  ON bookings (client_id);
CREATE INDEX IF NOT EXISTS users_client_idx     ON users (client_id);
CREATE INDEX IF NOT EXISTS password_reset_tokens_user_idx ON password_reset_tokens (user_id);

-- Partial (NULL-excluding) so many clients can share ics_token IS NULL before
-- their first ensure_ics_token() call lazily backfills a real value (#22).
CREATE UNIQUE INDEX IF NOT EXISTS clients_ics_token_idx ON clients (ics_token)
    WHERE ics_token IS NOT NULL;