RAPPORT-HOST/server/migrations/0001_init.sql

-- RAPPORT-HOST — Initiales Schema (HOST-eigene DB, getrennt von Kundendaten).

create extension if not exists "pgcrypto";

-- HOST-Kundenkonten (die zahlenden Büros, NICHT deren Endnutzer).
create table if not exists accounts (
  id            uuid primary key default gen_random_uuid(),
  email         text unique not null,
  password_hash text not null,
  created_at    timestamptz not null default now()
);

-- Abos. Ein Konto kann über die Zeit mehrere haben (Upgrade/Downgrade/Re-Sub).
create table if not exists subscriptions (
  id                     uuid primary key default gen_random_uuid(),
  account_id             uuid not null references accounts(id) on delete cascade,
  plan                   text not null,                       -- solo | studio | business
  status                 text not null,                       -- active | past_due | canceled | ...
  stripe_customer_id     text,
  stripe_subscription_id text unique,                         -- idempotenz-key fürs Fulfillment
  current_period_end     timestamptz,
  created_at             timestamptz not null default now()
);

-- Bereitgestellte Rapport-Instanzen (Modell A: ein Studio im geteilten Stack).
create table if not exists instances (
  id           uuid primary key default gen_random_uuid(),
  account_id   uuid not null references accounts(id) on delete cascade,
  studio_id    uuid,                                          -- studio_id im Rapport-Stack
  studio_slug  text not null,
  instance_url text not null,
  status       text not null default 'active',                -- active | suspended
  created_at   timestamptz not null default now()
);

create index if not exists idx_subscriptions_account on subscriptions(account_id);
create index if not exists idx_instances_account on instances(account_id);