patrick
9a24ea29e1
Git-Repository für bestehenden archivdms-Code initialisiert, Branch-/Commit-Konvention (feature/<ticket>-<slug>-Branches, Ticket-Prefix in Commit-Nachricht) etabliert.
388 lines
15 KiB
Go
388 lines
15 KiB
Go
package storage
|
|
|
|
import (
|
|
"context"
|
|
"encoding/json"
|
|
"errors"
|
|
"fmt"
|
|
"time"
|
|
|
|
"github.com/jackc/pgx/v5/pgconn"
|
|
)
|
|
|
|
// ErrTaxonomyNotFound is returned when a tag/document_type/correspondent
|
|
// lookup, update or delete does not match any row owned by the caller's
|
|
// tenant.
|
|
var ErrTaxonomyNotFound = errors.New("storage: taxonomy entity not found or not owned by tenant")
|
|
|
|
// ErrDuplicateTaxonomyName is returned when a tenant already has an entity
|
|
// of the same kind with the same name (UNIQUE(tenant_id, name)).
|
|
var ErrDuplicateTaxonomyName = errors.New("storage: entity with this name already exists for tenant")
|
|
|
|
// TaxonomyEntity is the shared shape of tags, document_types and
|
|
// correspondents — structurally identical (see lazy-splashing-puppy plan),
|
|
// kept as one Go struct/table-set with a `kind` selector rather than three
|
|
// near-duplicate types.
|
|
type TaxonomyEntity struct {
|
|
ID int64 `json:"id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
Name string `json:"name"`
|
|
Color string `json:"color,omitempty"`
|
|
MatchAlgorithm string `json:"match_algorithm"`
|
|
MatchPattern string `json:"match_pattern,omitempty"`
|
|
CaseSensitive bool `json:"case_sensitive"`
|
|
BarcodeValue string `json:"barcode_value,omitempty"`
|
|
CreatedAt time.Time `json:"created_at"`
|
|
}
|
|
|
|
// TaxonomyEntityRequest holds create/update parameters for a taxonomy entity.
|
|
type TaxonomyEntityRequest struct {
|
|
Name string
|
|
Color string
|
|
MatchAlgorithm string
|
|
MatchPattern string
|
|
CaseSensitive bool
|
|
BarcodeValue string
|
|
}
|
|
|
|
// taxonomyTable maps the three supported "kinds" to their table name. Kept
|
|
// as an allowlist so a caller can never inject an arbitrary table name.
|
|
func taxonomyTable(kind string) (string, error) {
|
|
switch kind {
|
|
case "tags":
|
|
return "tags", nil
|
|
case "document_types":
|
|
return "document_types", nil
|
|
case "correspondents":
|
|
return "correspondents", nil
|
|
default:
|
|
return "", fmt.Errorf("storage: unknown taxonomy kind %q", kind)
|
|
}
|
|
}
|
|
|
|
// initTaxonomySchema creates the tags/document_types/correspondents/
|
|
// document_tags tables plus the documents-table ALTERs (doc_type_id,
|
|
// correspondent_id, barcode_values). Idempotent, called from
|
|
// (*Store).initSchema. Documented (not executed) in
|
|
// migrations/005_taxonomy.sql.
|
|
func (s *Store) initTaxonomySchema(ctx context.Context) error {
|
|
_, err := s.db.Exec(ctx, `
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
color TEXT,
|
|
match_algorithm TEXT NOT NULL DEFAULT 'none' CHECK (match_algorithm IN ('none','any','all','exact','regex','fuzzy')),
|
|
match_pattern TEXT,
|
|
case_sensitive BOOLEAN NOT NULL DEFAULT false,
|
|
barcode_value TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(tenant_id, name)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS document_types (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
color TEXT,
|
|
match_algorithm TEXT NOT NULL DEFAULT 'none' CHECK (match_algorithm IN ('none','any','all','exact','regex','fuzzy')),
|
|
match_pattern TEXT,
|
|
case_sensitive BOOLEAN NOT NULL DEFAULT false,
|
|
barcode_value TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(tenant_id, name)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS correspondents (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
color TEXT,
|
|
match_algorithm TEXT NOT NULL DEFAULT 'none' CHECK (match_algorithm IN ('none','any','all','exact','regex','fuzzy')),
|
|
match_pattern TEXT,
|
|
case_sensitive BOOLEAN NOT NULL DEFAULT false,
|
|
barcode_value TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(tenant_id, name)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS document_tags (
|
|
document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
|
|
tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (document_id, tag_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tags_tenant ON tags(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_document_types_tenant ON document_types(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_correspondents_tenant ON correspondents(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_document_tags_tag ON document_tags(tag_id);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_tags_tenant_barcode ON tags(tenant_id, barcode_value) WHERE barcode_value IS NOT NULL;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_document_types_tenant_barcode ON document_types(tenant_id, barcode_value) WHERE barcode_value IS NOT NULL;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_correspondents_tenant_barcode ON correspondents(tenant_id, barcode_value) WHERE barcode_value IS NOT NULL;
|
|
`)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: create taxonomy tables: %w", err)
|
|
}
|
|
|
|
// documents-table ALTERs: doc_type_id/correspondent_id/barcode_values.
|
|
// The old doc_type/correspondent free-text columns are NOT touched
|
|
// (Bestandsschutz) — they stay in place, deprecated in favor of these.
|
|
_, err = s.db.Exec(ctx, `
|
|
ALTER TABLE documents ADD COLUMN IF NOT EXISTS doc_type_id BIGINT REFERENCES document_types(id);
|
|
ALTER TABLE documents ADD COLUMN IF NOT EXISTS correspondent_id BIGINT REFERENCES correspondents(id);
|
|
ALTER TABLE documents ADD COLUMN IF NOT EXISTS barcode_values JSONB;
|
|
`)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: alter documents table for taxonomy: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func scanTaxonomyEntity(row interface {
|
|
Scan(dest ...any) error
|
|
}) (*TaxonomyEntity, error) {
|
|
var e TaxonomyEntity
|
|
if err := row.Scan(&e.ID, &e.TenantID, &e.Name, &e.Color, &e.MatchAlgorithm, &e.MatchPattern, &e.CaseSensitive, &e.BarcodeValue, &e.CreatedAt); err != nil {
|
|
return nil, err
|
|
}
|
|
return &e, nil
|
|
}
|
|
|
|
// CreateTaxonomyEntity inserts a new tag/document_type/correspondent row.
|
|
func (s *Store) CreateTaxonomyEntity(ctx context.Context, kind string, tenantID int64, req TaxonomyEntityRequest) (*TaxonomyEntity, error) {
|
|
table, err := taxonomyTable(kind)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
algo := req.MatchAlgorithm
|
|
if algo == "" {
|
|
algo = "none"
|
|
}
|
|
row := s.db.QueryRow(ctx, fmt.Sprintf(`
|
|
INSERT INTO %s (tenant_id, name, color, match_algorithm, match_pattern, case_sensitive, barcode_value)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7)
|
|
RETURNING id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
|
|
`, table), tenantID, req.Name, nullIfEmpty(req.Color), algo, nullIfEmpty(req.MatchPattern), req.CaseSensitive, nullIfEmpty(req.BarcodeValue))
|
|
e, err := scanTaxonomyEntity(row)
|
|
if err != nil {
|
|
var pgErr *pgconn.PgError
|
|
if errors.As(err, &pgErr) && pgErr.Code == "23505" {
|
|
return nil, ErrDuplicateTaxonomyName
|
|
}
|
|
return nil, fmt.Errorf("storage: create %s: %w", kind, err)
|
|
}
|
|
return e, nil
|
|
}
|
|
|
|
// ListTaxonomyEntities returns all entities of the given kind for a tenant.
|
|
func (s *Store) ListTaxonomyEntities(ctx context.Context, kind string, tenantID int64) ([]TaxonomyEntity, error) {
|
|
table, err := taxonomyTable(kind)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
rows, err := s.db.Query(ctx, fmt.Sprintf(`
|
|
SELECT id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
|
|
FROM %s WHERE tenant_id = $1 ORDER BY name ASC
|
|
`, table), tenantID)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("storage: list %s: %w", kind, err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
out := make([]TaxonomyEntity, 0)
|
|
for rows.Next() {
|
|
e, err := scanTaxonomyEntity(rows)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("storage: scan %s: %w", kind, err)
|
|
}
|
|
out = append(out, *e)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// ListActiveMatchers returns all entities of the given kind for a tenant
|
|
// whose match_algorithm is not 'none' — the candidate set the matching
|
|
// engine runs against on ingest.
|
|
func (s *Store) ListActiveMatchers(ctx context.Context, kind string, tenantID int64) ([]TaxonomyEntity, error) {
|
|
table, err := taxonomyTable(kind)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
rows, err := s.db.Query(ctx, fmt.Sprintf(`
|
|
SELECT id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
|
|
FROM %s WHERE tenant_id = $1 AND match_algorithm != 'none'
|
|
`, table), tenantID)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("storage: list active matchers %s: %w", kind, err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var out []TaxonomyEntity
|
|
for rows.Next() {
|
|
e, err := scanTaxonomyEntity(rows)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("storage: scan %s: %w", kind, err)
|
|
}
|
|
out = append(out, *e)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// GetTaxonomyEntityByBarcode looks up an entity by its barcode_value, scoped
|
|
// to tenant. Returns ErrTaxonomyNotFound if none match.
|
|
func (s *Store) GetTaxonomyEntityByBarcode(ctx context.Context, kind string, tenantID int64, barcodeValue string) (*TaxonomyEntity, error) {
|
|
table, err := taxonomyTable(kind)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
row := s.db.QueryRow(ctx, fmt.Sprintf(`
|
|
SELECT id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
|
|
FROM %s WHERE tenant_id = $1 AND barcode_value = $2
|
|
`, table), tenantID, barcodeValue)
|
|
e, err := scanTaxonomyEntity(row)
|
|
if err != nil {
|
|
return nil, ErrTaxonomyNotFound
|
|
}
|
|
return e, nil
|
|
}
|
|
|
|
// UpdateTaxonomyEntity updates a tag/document_type/correspondent, scoped to
|
|
// tenant ownership.
|
|
func (s *Store) UpdateTaxonomyEntity(ctx context.Context, kind string, id, tenantID int64, req TaxonomyEntityRequest) (*TaxonomyEntity, error) {
|
|
table, err := taxonomyTable(kind)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
algo := req.MatchAlgorithm
|
|
if algo == "" {
|
|
algo = "none"
|
|
}
|
|
row := s.db.QueryRow(ctx, fmt.Sprintf(`
|
|
UPDATE %s SET name = $1, color = $2, match_algorithm = $3, match_pattern = $4, case_sensitive = $5, barcode_value = $6
|
|
WHERE id = $7 AND tenant_id = $8
|
|
RETURNING id, tenant_id, name, COALESCE(color, ''), match_algorithm, COALESCE(match_pattern, ''), case_sensitive, COALESCE(barcode_value, ''), created_at
|
|
`, table), req.Name, nullIfEmpty(req.Color), algo, nullIfEmpty(req.MatchPattern), req.CaseSensitive, nullIfEmpty(req.BarcodeValue), id, tenantID)
|
|
e, err := scanTaxonomyEntity(row)
|
|
if err != nil {
|
|
var pgErr *pgconn.PgError
|
|
if errors.As(err, &pgErr) && pgErr.Code == "23505" {
|
|
return nil, ErrDuplicateTaxonomyName
|
|
}
|
|
return nil, ErrTaxonomyNotFound
|
|
}
|
|
return e, nil
|
|
}
|
|
|
|
// DeleteTaxonomyEntity deletes a tag/document_type/correspondent, scoped to
|
|
// tenant ownership.
|
|
func (s *Store) DeleteTaxonomyEntity(ctx context.Context, kind string, id, tenantID int64) error {
|
|
table, err := taxonomyTable(kind)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
tag, err := s.db.Exec(ctx, fmt.Sprintf(`DELETE FROM %s WHERE id = $1 AND tenant_id = $2`, table), id, tenantID)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: delete %s: %w", kind, err)
|
|
}
|
|
if tag.RowsAffected() == 0 {
|
|
return ErrTaxonomyNotFound
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// AttachTag adds a document_tags row (idempotent — repeated attaches are a
|
|
// no-op via ON CONFLICT). Ownership of both document and tag by tenantID
|
|
// must be verified by the caller beforehand.
|
|
func (s *Store) AttachTag(ctx context.Context, documentID, tagID int64) error {
|
|
_, err := s.db.Exec(ctx, `
|
|
INSERT INTO document_tags (document_id, tag_id) VALUES ($1, $2)
|
|
ON CONFLICT (document_id, tag_id) DO NOTHING
|
|
`, documentID, tagID)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: attach tag: %w", err)
|
|
}
|
|
// The document's tag set changed -> its tag_grants layer may differ.
|
|
if err := s.RecomputeVisibility(ctx, documentID); err != nil {
|
|
return fmt.Errorf("storage: recompute visibility after attach tag: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// DetachTag removes a document_tags row.
|
|
func (s *Store) DetachTag(ctx context.Context, documentID, tagID int64) error {
|
|
_, err := s.db.Exec(ctx, `DELETE FROM document_tags WHERE document_id = $1 AND tag_id = $2`, documentID, tagID)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: detach tag: %w", err)
|
|
}
|
|
if err := s.RecomputeVisibility(ctx, documentID); err != nil {
|
|
return fmt.Errorf("storage: recompute visibility after detach tag: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// ListDocumentTags returns the tags attached to a document.
|
|
func (s *Store) ListDocumentTags(ctx context.Context, documentID, tenantID int64) ([]TaxonomyEntity, error) {
|
|
rows, err := s.db.Query(ctx, `
|
|
SELECT t.id, t.tenant_id, t.name, COALESCE(t.color, ''), t.match_algorithm, COALESCE(t.match_pattern, ''), t.case_sensitive, COALESCE(t.barcode_value, ''), t.created_at
|
|
FROM tags t
|
|
JOIN document_tags dt ON dt.tag_id = t.id
|
|
WHERE dt.document_id = $1 AND t.tenant_id = $2
|
|
ORDER BY t.name ASC
|
|
`, documentID, tenantID)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("storage: list document tags: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
out := make([]TaxonomyEntity, 0)
|
|
for rows.Next() {
|
|
e, err := scanTaxonomyEntity(rows)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("storage: scan document tag: %w", err)
|
|
}
|
|
out = append(out, *e)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// SetDocumentBarcodeValues stores the raw barcode payloads detected during
|
|
// ingest on documents.barcode_values (JSONB array), regardless of whether
|
|
// they matched any taxonomy entity — kept for GoBD-Nachvollziehbarkeit.
|
|
func (s *Store) SetDocumentBarcodeValues(ctx context.Context, documentID, tenantID int64, values []string) error {
|
|
if len(values) == 0 {
|
|
return nil
|
|
}
|
|
b, err := json.Marshal(values)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: marshal barcode values: %w", err)
|
|
}
|
|
_, err = s.db.Exec(ctx, `UPDATE documents SET barcode_values = $1 WHERE id = $2 AND tenant_id = $3`, b, documentID, tenantID)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: set document barcode values: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// SetDocumentDocType sets documents.doc_type_id, scoped to tenant.
|
|
func (s *Store) SetDocumentDocType(ctx context.Context, documentID, tenantID, docTypeID int64) error {
|
|
_, err := s.db.Exec(ctx, `UPDATE documents SET doc_type_id = $1 WHERE id = $2 AND tenant_id = $3`, docTypeID, documentID, tenantID)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: set document doc_type_id: %w", err)
|
|
}
|
|
// The document's type changed -> its document_type_grants layer may differ.
|
|
if err := s.RecomputeVisibility(ctx, documentID); err != nil {
|
|
return fmt.Errorf("storage: recompute visibility after set doc_type: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// SetDocumentCorrespondent sets documents.correspondent_id, scoped to tenant.
|
|
func (s *Store) SetDocumentCorrespondent(ctx context.Context, documentID, tenantID, correspondentID int64) error {
|
|
_, err := s.db.Exec(ctx, `UPDATE documents SET correspondent_id = $1 WHERE id = $2 AND tenant_id = $3`, correspondentID, documentID, tenantID)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: set document correspondent_id: %w", err)
|
|
}
|
|
// correspondent is not part of the ACL, so no RecomputeVisibility runs
|
|
// here — sync the index directly. Best-effort.
|
|
s.SyncIndex(ctx, documentID)
|
|
return nil
|
|
}
|