Three architectures: dispatch, document intake and material ledger

-- PostgreSQL Exclusion Constraint stopping double-booking
create extension if not exists btree_gist;

create table assignments (
  id            bigint generated always as identity primary key,
  job_id        bigint not null references jobs(id),
  technician_id bigint not null references technicians(id),
  starts_at     timestamptz not null,
  ends_at       timestamptz not null,
  cancelled     boolean not null default false,
  check (ends_at > starts_at),
  exclude using gist (
    technician_id with =,
    tstzrange(starts_at, ends_at) with &&
  ) where (not cancelled)
);

-- Deduplication by cryptographic file hash and supplier invoice number
create table documents (
  id          bigint generated always as identity primary key,
  source      text not null check (source in ('email', 'upload', 'api')),
  file_sha256 text not null unique,     -- the exact file cannot be processed twice
  doc_type    text,
  status      text not null default 'received'
              check (status in ('received', 'extracted', 'validated',
                                'needs_review', 'posted', 'rejected')),
  received_at timestamptz not null default now()
);

-- the same supplier invoice number can't be posted twice, even under a different file
create unique index one_invoice_per_supplier
  on supplier_invoices (supplier_id, invoice_number);

-- Append-only inventory ledger: changes are logged, never overwritten
create table stock_movements (
  id           bigint generated always as identity primary key,
  item_id      bigint not null references items(id),
  location_id  bigint not null references locations(id),
  qty          numeric(14,3) not null check (qty <> 0),   -- positive: in, negative: out
  reason       text not null check (reason in
               ('receipt', 'issue_to_job', 'transfer', 'return', 'adjustment')),
  job_id       bigint references jobs(id),
  document_ref text,
  created_by   bigint not null references users(id),
  created_at   timestamptz not null default now()
);

-- the application can only append records, never alter past events
revoke update, delete on stock_movements from app_role;

-- live stock on hand is computed dynamically
create view stock_on_hand as
  select item_id, location_id, sum(qty) as on_hand
  from stock_movements
  group by item_id, location_id;