nilsandClaude
16d2a56a78
ci / docker (push) Successful in 13s
Rust (axum + sqlx) API and worker sharing a Postgres-backed job queue (SKIP LOCKED, heartbeat, reaper, typed statuses), S3 storage with derived keys and a fully private bucket, OIDC photographer login with per-request allowlist checks, client share links with argon2 passwords and lockout, cookie-based image authorization with sliding expiry, hand-rolled spec-compliant streaming ZIP downloads with exact Content-Length, React + Vite gallery frontend, single Docker image, Helm chart for external S3 + Postgres, and Gitea CI. Co-Authored-By: Claude <noreply@anthropic.com>
90 lines
2.9 KiB
SQL
90 lines
2.9 KiB
SQL
create extension if not exists pgcrypto;
|
|
|
|
create table users (
|
|
id uuid primary key default gen_random_uuid(),
|
|
oidc_subject text not null unique,
|
|
email text not null,
|
|
display_name text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table albums (
|
|
id uuid primary key default gen_random_uuid(),
|
|
name text not null,
|
|
description text not null default '',
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table photos (
|
|
id uuid primary key default gen_random_uuid(),
|
|
album_id uuid not null references albums(id) on delete cascade,
|
|
filename text not null,
|
|
content_type text not null,
|
|
size_bytes bigint not null default 0,
|
|
status text not null default 'uploaded', -- uploaded | processing | ready | error
|
|
error text,
|
|
width int,
|
|
height int,
|
|
taken_at timestamptz,
|
|
-- Bumped on every (re)process; cache-buster for preview/thumb URLs.
|
|
processed_at timestamptz,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index photos_album_idx on photos (album_id);
|
|
|
|
create table shares (
|
|
id uuid primary key default gen_random_uuid(),
|
|
album_id uuid not null references albums(id) on delete cascade,
|
|
token text not null unique,
|
|
label text not null default '',
|
|
password_hash text,
|
|
allow_download boolean not null default true,
|
|
expires_at timestamptz,
|
|
-- Brute-force lockout state for password-protected shares.
|
|
failed_attempts int not null default 0,
|
|
locked_until timestamptz,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index shares_album_idx on shares (album_id);
|
|
|
|
create table ratings (
|
|
share_id uuid not null references shares(id) on delete cascade,
|
|
photo_id uuid not null references photos(id) on delete cascade,
|
|
rating int not null check (rating between 1 and 5),
|
|
updated_at timestamptz not null default now(),
|
|
primary key (share_id, photo_id)
|
|
);
|
|
|
|
-- Cascaded photo deletes fire per-row FK triggers; without this each one
|
|
-- sequential-scans the table.
|
|
create index ratings_photo_idx on ratings (photo_id);
|
|
|
|
create table tags (
|
|
id uuid primary key default gen_random_uuid(),
|
|
share_id uuid not null references shares(id) on delete cascade,
|
|
photo_id uuid not null references photos(id) on delete cascade,
|
|
tag text not null,
|
|
created_at timestamptz not null default now(),
|
|
unique (share_id, photo_id, tag)
|
|
);
|
|
|
|
create index tags_photo_idx on tags (photo_id);
|
|
|
|
create table jobs (
|
|
id uuid primary key default gen_random_uuid(),
|
|
kind text not null,
|
|
payload jsonb not null default '{}',
|
|
status text not null default 'queued', -- queued | running | done | failed
|
|
attempts int not null default 0,
|
|
max_attempts int not null default 5,
|
|
run_at timestamptz not null default now(),
|
|
locked_by text,
|
|
locked_at timestamptz,
|
|
last_error text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index jobs_poll_idx on jobs (status, run_at);
|