patrick
9a24ea29e1
Git-Repository für bestehenden archivdms-Code initialisiert, Branch-/Commit-Konvention (feature/<ticket>-<slug>-Branches, Ticket-Prefix in Commit-Nachricht) etabliert.
61 lines
2.3 KiB
SQL
61 lines
2.3 KiB
SQL
-- Dokumentation, siehe README.md. Wird zur Laufzeit idempotent von
|
|
-- internal/tenantstore, internal/userstore, internal/storage und
|
|
-- internal/audit initSchema()-Funktionen erzeugt.
|
|
|
|
CREATE TABLE IF NOT EXISTS tenants (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
slug VARCHAR(100) UNIQUE NOT NULL,
|
|
domain VARCHAR(255),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_tenants_domain ON tenants (domain) WHERE domain IS NOT NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
username VARCHAR(100) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL DEFAULT '',
|
|
role VARCHAR(20) NOT NULL DEFAULT 'user',
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_login_at TIMESTAMPTZ,
|
|
tenant_id BIGINT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_users_tenant ON users (tenant_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS token_blacklist (
|
|
jti VARCHAR(255) PRIMARY KEY,
|
|
expires_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS documents (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
doc_type TEXT,
|
|
correspondent TEXT,
|
|
storage_path TEXT NOT NULL,
|
|
content_hash TEXT NOT NULL,
|
|
ocr_text TEXT,
|
|
retain_until DATE,
|
|
source TEXT, -- z.B. 'upload' | 'archivmail_import' (kein Importer implementiert)
|
|
source_ref TEXT, -- externe Referenz (z.B. archivmail Mail-ID via API-Pull)
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_documents_tenant ON documents(tenant_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
event_type VARCHAR(50) NOT NULL,
|
|
username VARCHAR(255) NOT NULL DEFAULT '',
|
|
ip_address VARCHAR(45) NOT NULL DEFAULT '',
|
|
document_id VARCHAR(64) NOT NULL DEFAULT '',
|
|
success BOOLEAN NOT NULL DEFAULT true,
|
|
detail TEXT NOT NULL DEFAULT '',
|
|
tenant_id BIGINT
|
|
);
|
|
-- append-only: BEFORE UPDATE OR DELETE trigger raises, see internal/audit/audit.go
|