SPB Git

Repository Public

001_init.sql 3.6 KB 99 lines sql

1-- SP Mail — schéma initial : identité (passkeys), sessions par appareil, boîtes reliées à Stalwart, audit.2create extension if not exists pgcrypto;34create table users (5  id            uuid primary key default gen_random_uuid(),6  handle        text not null unique,7  display_name  text not null,8  role          text not null default 'member' check (role in ('admin','member')),9  status        text not null default 'active' check (status in ('active','disabled')),10  prefs         jsonb not null default '{}',11  created_at    timestamptz not null default now()12);1314create table passkeys (15  id            text primary key,16  user_id       uuid not null references users(id) on delete cascade,17  public_key    bytea not null,18  counter       bigint not null default 0,19  transports    text[] not null default '{}',20  device_type   text,21  backed_up     boolean not null default false,22  label         text not null default 'Passkey',23  created_at    timestamptz not null default now(),24  last_used_at  timestamptz25);26create index on passkeys(user_id);2728-- Une session par appareil : cookie (web) ou jeton porteur (apps natives). Seul le hachage du jeton est conservé.29create table sessions (30  id            uuid primary key default gen_random_uuid(),31  token_hash    bytea not null unique,32  user_id       uuid not null references users(id) on delete cascade,33  kind          text not null default 'web' check (kind in ('web','native')),34  device_name   text,35  platform      text,36  passkey_id    text,37  ip            text,38  user_agent    text,39  created_at    timestamptz not null default now(),40  last_seen_at  timestamptz not null default now(),41  expires_at    timestamptz not null,42  revoked_at    timestamptz43);44create index on sessions(user_id);4546create table challenges (47  id         uuid primary key default gen_random_uuid(),48  kind       text not null,49  challenge  text not null,50  user_id    uuid,51  data       jsonb not null default '{}',52  expires_at timestamptz not null53);5455-- Lien à usage unique : premier administrateur, récupération, ou ajout d'un utilisateur.56create table enrollment_tokens (57  token_hash bytea primary key,58  purpose    text not null check (purpose in ('bootstrap','recover','invite')),59  user_id    uuid references users(id) on delete cascade,60  expires_at timestamptz not null,61  used_at    timestamptz62);6364-- Une boîte = un compte Stalwart. Le secret (mot de passe du compte, généré par SP Mail) est chiffré AES-256-GCM.65create table mailboxes (66  id            uuid primary key default gen_random_uuid(),67  user_id       uuid not null references users(id) on delete cascade,68  address       text not null unique,69  display_name  text not null default '',70  stalwart_id   text not null,71  secret_enc    bytea not null,72  position      int not null default 0,73  created_at    timestamptz not null default now()74);75create index on mailboxes(user_id);7677-- Jetons APNs des apps natives (aucun contenu de message n'est poussé).78create table push_devices (79  id          uuid primary key default gen_random_uuid(),80  session_id  uuid not null references sessions(id) on delete cascade,81  user_id     uuid not null references users(id) on delete cascade,82  apns_token  text not null,83  environment text not null default 'production' check (environment in ('production','sandbox')),84  bundle_id   text not null,85  created_at  timestamptz not null default now(),86  unique (apns_token, bundle_id)87);8889create table audit_log (90  id      bigserial primary key,91  at      timestamptz not null default now(),92  actor   uuid,93  action  text not null,94  target  text,95  meta    jsonb not null default '{}',96  ip      text97);98create index on audit_log(at desc);99
Files main
Menu

Theme