patrick
9a24ea29e1
Git-Repository für bestehenden archivdms-Code initialisiert, Branch-/Commit-Konvention (feature/<ticket>-<slug>-Branches, Ticket-Prefix in Commit-Nachricht) etabliert.
321 lines
12 KiB
Go
321 lines
12 KiB
Go
package storage
|
|
|
|
import (
|
|
"context"
|
|
"encoding/json"
|
|
"errors"
|
|
"fmt"
|
|
"regexp"
|
|
"sort"
|
|
"strings"
|
|
"time"
|
|
|
|
"github.com/jackc/pgx/v5"
|
|
|
|
"archivdms/internal/matching"
|
|
)
|
|
|
|
// ErrSuggestionNotFound is returned when no metadata_suggestions row exists
|
|
// for a document (GetLatestSuggestion) or the id/tenant scope does not match
|
|
// (MarkSuggestionReviewed).
|
|
var ErrSuggestionNotFound = errors.New("storage: metadata suggestion not found")
|
|
|
|
// suggestionFloor is the minimum fuzzy score (0..1) at which a
|
|
// tag/document_type/correspondent is surfaced as a NON-binding suggestion.
|
|
// It sits deliberately BELOW matching.FuzzyThreshold (currently 0.85, the
|
|
// auto-assign confidence): candidates at/above FuzzyThreshold that were not
|
|
// already auto-assigned (e.g. a second document_type that also matched, or a
|
|
// name-only near-match on an entity whose configured algorithm isn't fuzzy)
|
|
// are strong suggestions; candidates in [suggestionFloor, FuzzyThreshold) are
|
|
// the near-misses this feature exists to expose for manual review. 0.55 keeps
|
|
// noise low while still catching typo-level OCR differences.
|
|
const suggestionFloor = 0.55
|
|
|
|
// maxSuggestionCandidates caps how many candidates are kept per category
|
|
// (tags / document_types / correspondents), sorted by score descending.
|
|
const maxSuggestionCandidates = 5
|
|
|
|
// autoGeneratedTitlePattern matches the timestamp placeholder title produced
|
|
// by titleFromOCRText ("Scan DD.MM.YYYY HH:MM") when no meaningful heading
|
|
// could be derived on ingest. A current title matching this is treated as
|
|
// "not a real title yet", so a re-derived title is suggested.
|
|
var autoGeneratedTitlePattern = regexp.MustCompile(`^Scan \d{2}\.\d{2}\.\d{4} \d{2}:\d{2}$`)
|
|
|
|
// SuggestionCandidate is one scored, non-binding metadata suggestion for a
|
|
// single taxonomy entity. Score is the fuzzy similarity in [0,1].
|
|
type SuggestionCandidate struct {
|
|
ID int64 `json:"id"`
|
|
Name string `json:"name"`
|
|
Score float64 `json:"score"`
|
|
Explanation []string `json:"explanation,omitempty"`
|
|
}
|
|
|
|
// DocumentDateCandidate is a non-binding belegdatum (invoice/document date)
|
|
// suggestion re-derived from the OCR text. Date is the ISO date (YYYY-MM-DD)
|
|
// the frontend can apply via PUT /api/documents/{id}/document-date; Score is a
|
|
// fixed heuristic confidence (the regex date scanner has no per-match score).
|
|
type DocumentDateCandidate struct {
|
|
Date string `json:"date"`
|
|
Score float64 `json:"score"`
|
|
}
|
|
|
|
// SuggestionPayload is the JSONB body persisted in metadata_suggestions.suggestion.
|
|
// A nil Title means no title suggestion was made (current title already looks
|
|
// human-authored). The candidate slices are always non-nil (possibly empty).
|
|
// DocumentDateCandidate is nil when the document already has a belegdatum set or
|
|
// no plausible date could be recognised in the OCR text.
|
|
type SuggestionPayload struct {
|
|
Title *string `json:"title,omitempty"`
|
|
DocTypeCandidates []SuggestionCandidate `json:"doc_type_candidates"`
|
|
CorrespondentCandidates []SuggestionCandidate `json:"correspondent_candidates"`
|
|
TagCandidates []SuggestionCandidate `json:"tag_candidates"`
|
|
DocumentDateCandidate *DocumentDateCandidate `json:"document_date_candidate,omitempty"`
|
|
}
|
|
|
|
// MetadataSuggestion is one persisted suggestion run for a document. It is a
|
|
// log/cache of what the heuristic provider proposed — applying an accepted
|
|
// field goes through the normal edit endpoints, NOT through this row.
|
|
type MetadataSuggestion struct {
|
|
ID int64 `json:"id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
DocumentID int64 `json:"document_id"`
|
|
Provider string `json:"provider"`
|
|
RequestedBy *int64 `json:"requested_by,omitempty"`
|
|
RequestedAt time.Time `json:"requested_at"`
|
|
Suggestion SuggestionPayload `json:"suggestion"`
|
|
Status string `json:"status"`
|
|
ReviewedAt *time.Time `json:"reviewed_at,omitempty"`
|
|
ReviewedBy *int64 `json:"reviewed_by,omitempty"`
|
|
}
|
|
|
|
// initMetadataSuggestionsSchema creates the metadata_suggestions table.
|
|
// Idempotent, called from (*Store).initSchema AFTER the documents/taxonomy
|
|
// schema exists. Documented (not executed) in
|
|
// migrations/012_metadata_suggestions.sql.
|
|
func (s *Store) initMetadataSuggestionsSchema(ctx context.Context) error {
|
|
_, err := s.db.Exec(ctx, `
|
|
CREATE TABLE IF NOT EXISTS metadata_suggestions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
document_id BIGINT NOT NULL,
|
|
provider TEXT NOT NULL DEFAULT 'heuristic',
|
|
requested_by BIGINT,
|
|
requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
suggestion JSONB NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','reviewed')),
|
|
reviewed_at TIMESTAMPTZ,
|
|
reviewed_by BIGINT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_metadata_suggestions_document ON metadata_suggestions(document_id);
|
|
`)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: create metadata suggestions table: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
const metadataSuggestionCols = `id, tenant_id, document_id, provider, requested_by, requested_at, suggestion, status, reviewed_at, reviewed_by`
|
|
|
|
func scanMetadataSuggestion(row interface {
|
|
Scan(dest ...any) error
|
|
}) (*MetadataSuggestion, error) {
|
|
var m MetadataSuggestion
|
|
var payload []byte
|
|
if err := row.Scan(&m.ID, &m.TenantID, &m.DocumentID, &m.Provider, &m.RequestedBy,
|
|
&m.RequestedAt, &payload, &m.Status, &m.ReviewedAt, &m.ReviewedBy); err != nil {
|
|
return nil, err
|
|
}
|
|
m.Suggestion = SuggestionPayload{
|
|
DocTypeCandidates: make([]SuggestionCandidate, 0),
|
|
CorrespondentCandidates: make([]SuggestionCandidate, 0),
|
|
TagCandidates: make([]SuggestionCandidate, 0),
|
|
}
|
|
if len(payload) > 0 {
|
|
if err := json.Unmarshal(payload, &m.Suggestion); err != nil {
|
|
return nil, fmt.Errorf("storage: unmarshal suggestion payload: %w", err)
|
|
}
|
|
}
|
|
return &m, nil
|
|
}
|
|
|
|
// GenerateHeuristicSuggestions builds a fresh, rule-based (no LLM) metadata
|
|
// suggestion for a document: it fuzzy-scores every taxonomy entity's name (and
|
|
// its configured match_pattern, if any) against the document's title+OCR text,
|
|
// surfaces the near-misses above suggestionFloor that are NOT already assigned,
|
|
// and — if the current title still looks auto-generated — proposes a
|
|
// re-derived title. The result is persisted as a metadata_suggestions row and
|
|
// returned. requestedBy may be nil for non-interactive callers.
|
|
func (s *Store) GenerateHeuristicSuggestions(ctx context.Context, documentID, tenantID int64, requestedBy *int64) (*MetadataSuggestion, error) {
|
|
doc, err := s.GetDocument(ctx, documentID, tenantID)
|
|
if err != nil {
|
|
return nil, err // ErrDocumentNotFound propagates
|
|
}
|
|
|
|
haystack := doc.Title
|
|
if doc.OCRText != "" {
|
|
haystack = doc.Title + "\n" + doc.OCRText
|
|
}
|
|
|
|
// Entities already assigned to the document — excluded from suggestions.
|
|
assignedTags := map[int64]bool{}
|
|
tags, err := s.ListDocumentTags(ctx, documentID, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
for _, t := range tags {
|
|
assignedTags[t.ID] = true
|
|
}
|
|
|
|
tagCands, err := s.scoreCandidates(ctx, "tags", tenantID, haystack, func(id int64) bool { return assignedTags[id] })
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
docTypeCands, err := s.scoreCandidates(ctx, "document_types", tenantID, haystack, func(id int64) bool {
|
|
return doc.DocTypeID != nil && *doc.DocTypeID == id
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
corrCands, err := s.scoreCandidates(ctx, "correspondents", tenantID, haystack, func(id int64) bool {
|
|
return doc.CorrespondentID != nil && *doc.CorrespondentID == id
|
|
})
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
payload := SuggestionPayload{
|
|
DocTypeCandidates: docTypeCands,
|
|
CorrespondentCandidates: corrCands,
|
|
TagCandidates: tagCands,
|
|
}
|
|
if autoGeneratedTitlePattern.MatchString(strings.TrimSpace(doc.Title)) {
|
|
if t := heuristicTitle(doc.OCRText); t != "" && t != doc.Title {
|
|
payload.Title = &t
|
|
}
|
|
}
|
|
// Belegdatum suggestion: only when the document has no document_date yet and
|
|
// a plausible date is recognisable in the OCR text. Surfaced as a chip the
|
|
// user can apply via PUT /api/documents/{id}/document-date. The confidence is
|
|
// now derived from keyword-proximity scoring (see documentDateFromTextWithScore)
|
|
// instead of a fixed value.
|
|
if doc.DocumentDate == nil {
|
|
if d, sc, ok := documentDateFromTextWithScore(doc.OCRText); ok {
|
|
payload.DocumentDateCandidate = &DocumentDateCandidate{
|
|
Date: d.Format("2006-01-02"),
|
|
Score: sc,
|
|
}
|
|
}
|
|
}
|
|
|
|
raw, err := json.Marshal(payload)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("storage: marshal suggestion payload: %w", err)
|
|
}
|
|
|
|
row := s.db.QueryRow(ctx, `
|
|
INSERT INTO metadata_suggestions (tenant_id, document_id, provider, requested_by, suggestion)
|
|
VALUES ($1, $2, 'heuristic', $3, $4)
|
|
RETURNING `+metadataSuggestionCols,
|
|
tenantID, documentID, requestedBy, raw)
|
|
m, err := scanMetadataSuggestion(row)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("storage: insert metadata suggestion: %w", err)
|
|
}
|
|
return m, nil
|
|
}
|
|
|
|
// scoreCandidates fuzzy-scores every entity of a kind for a tenant against the
|
|
// haystack, keeps those at/above suggestionFloor that are not excluded (already
|
|
// assigned), sorts by score descending and caps at maxSuggestionCandidates.
|
|
func (s *Store) scoreCandidates(ctx context.Context, kind string, tenantID int64, haystack string, excluded func(id int64) bool) ([]SuggestionCandidate, error) {
|
|
entities, err := s.ListTaxonomyEntities(ctx, kind, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
out := make([]SuggestionCandidate, 0)
|
|
for _, e := range entities {
|
|
if excluded(e.ID) {
|
|
continue
|
|
}
|
|
score := matching.FuzzyScore(e.Name, e.CaseSensitive, haystack)
|
|
if e.MatchPattern != "" {
|
|
if p := matching.FuzzyScore(e.MatchPattern, e.CaseSensitive, haystack); p > score {
|
|
score = p
|
|
}
|
|
}
|
|
if score < suggestionFloor {
|
|
continue
|
|
}
|
|
out = append(out, SuggestionCandidate{ID: e.ID, Name: e.Name, Score: score})
|
|
}
|
|
sort.SliceStable(out, func(i, j int) bool { return out[i].Score > out[j].Score })
|
|
if len(out) > maxSuggestionCandidates {
|
|
out = out[:maxSuggestionCandidates]
|
|
}
|
|
return out, nil
|
|
}
|
|
|
|
// heuristicTitle re-derives a candidate title from OCR text using a different
|
|
// heuristic than titleFromOCRText (which takes the first meaningful line): it
|
|
// picks the longest trimmed line, which is more likely to be a real heading /
|
|
// company line than a short letterhead fragment or page number. Returns "" if
|
|
// no line qualifies. Kept simple on purpose (no NLP).
|
|
func heuristicTitle(ocrText string) string {
|
|
if ocrText == "" {
|
|
return ""
|
|
}
|
|
best := ""
|
|
bestLen := 0
|
|
for _, line := range strings.Split(ocrText, "\n") {
|
|
line = strings.TrimSpace(line)
|
|
r := []rune(line)
|
|
if len(r) < 5 {
|
|
continue
|
|
}
|
|
if len(r) > bestLen {
|
|
bestLen = len(r)
|
|
if len(r) > 120 {
|
|
line = string(r[:120])
|
|
}
|
|
best = line
|
|
}
|
|
}
|
|
return best
|
|
}
|
|
|
|
// GetLatestSuggestion returns the most recent metadata_suggestions row for a
|
|
// document, scoped to tenant ownership, or ErrSuggestionNotFound if none exist.
|
|
func (s *Store) GetLatestSuggestion(ctx context.Context, documentID, tenantID int64) (*MetadataSuggestion, error) {
|
|
row := s.db.QueryRow(ctx, `SELECT `+metadataSuggestionCols+`
|
|
FROM metadata_suggestions
|
|
WHERE document_id = $1 AND tenant_id = $2
|
|
ORDER BY requested_at DESC, id DESC
|
|
LIMIT 1`, documentID, tenantID)
|
|
m, err := scanMetadataSuggestion(row)
|
|
if err != nil {
|
|
if errors.Is(err, pgx.ErrNoRows) {
|
|
return nil, ErrSuggestionNotFound
|
|
}
|
|
return nil, fmt.Errorf("storage: get latest metadata suggestion: %w", err)
|
|
}
|
|
return m, nil
|
|
}
|
|
|
|
// MarkSuggestionReviewed flags a suggestion row as reviewed (the user has acted
|
|
// on it in the UI, regardless of which fields they accepted — those went
|
|
// through the normal edit endpoints). Scoped to tenant ownership. Returns
|
|
// ErrSuggestionNotFound if the id/tenant scope does not match.
|
|
func (s *Store) MarkSuggestionReviewed(ctx context.Context, id, tenantID, reviewedBy int64) error {
|
|
tag, err := s.db.Exec(ctx, `
|
|
UPDATE metadata_suggestions
|
|
SET status = 'reviewed', reviewed_at = now(), reviewed_by = $1
|
|
WHERE id = $2 AND tenant_id = $3`, reviewedBy, id, tenantID)
|
|
if err != nil {
|
|
return fmt.Errorf("storage: mark metadata suggestion reviewed: %w", err)
|
|
}
|
|
if tag.RowsAffected() == 0 {
|
|
return ErrSuggestionNotFound
|
|
}
|
|
return nil
|
|
}
|