Files
venture 37dc36f1f2 V3 S5 : graphe documentaire + doublons + collections
Backend : resource_links + scan_session_id (000021), réconciliation
déterministe (same_scan outbox, doublons SHA-256 à l'upload + backfill,
same_issuer/référence via faits effectifs, verdicts préservés, élagage
auto), GET /files/:id/relations + verdict (owner-only),
GET /duplicates (groupes + gain récupérable). Tests service + handlers
+ migrations.

Mobile (Room v22) : links + collections, scanSessionId dans les creates
de scan, LinksRepository (sync + verdicts REST), « Voir aussi » dans la
fiche (verdict infirmer/re-confirmer), écran Doublons (garder le plus
récent), collections locales (sauver/rejouer/supprimer). FR/EN.

Contrat docs/api-v1.md (/relations, pas /links — pris par le partage),
V3.md (S5 livré, version_of non matérialisé).
2026-09-26 11:16:35 +02:00

356 lines
11 KiB
Go

package repository
import (
"database/sql"
)
// Link types — mirror of the resource_links CHECK (migration 000021).
// All types are symmetric : stored once with from_id < to_id (canonical
// order), read from either end. version_of is intentionally absent :
// versions live INSIDE a resource (resource_versions, 000018).
const (
LinkSameScan = "same_scan"
LinkDuplicateOf = "duplicate_of"
LinkSameIssuer = "same_issuer"
LinkSameReference = "same_reference"
)
// LinkRow is one edge viewed from the queried resource.
type LinkRow struct {
ID string
OtherID string
Type string
Confidence float64
CreatedBy string
OtherName string
OtherType string
OtherSize int64
}
// canonicalPair orders an unordered pair for symmetric storage.
func canonicalPair(a, b string) (string, string) {
if a < b {
return a, b
}
return b, a
}
// ListLinks returns the live edges touching resourceID (either direction),
// joined with the other end's identity. Trashed others are excluded.
// No ownership check — the caller scopes (owner-only in S5).
func (r *Resources) ListLinks(resourceID string) ([]LinkRow, error) {
rows, err := r.DB.Query(
`SELECT l.id,
CASE WHEN l.from_id = $1 THEN l.to_id ELSE l.from_id END AS other_id,
l.link_type, l.confidence, l.created_by,
o.name, o.type, COALESCE(o.size_bytes, 0)
FROM resource_links l
JOIN resources o ON o.resource_id = CASE WHEN l.from_id = $1 THEN l.to_id ELSE l.from_id END
WHERE (l.from_id = $1 OR l.to_id = $1)
AND o.deleted_at IS NULL
ORDER BY l.confidence DESC, o.name ASC`,
resourceID,
)
if err != nil {
return nil, err
}
defer rows.Close()
out := make([]LinkRow, 0)
for rows.Next() {
var row LinkRow
if err := rows.Scan(&row.ID, &row.OtherID, &row.Type, &row.Confidence, &row.CreatedBy, &row.OtherName, &row.OtherType, &row.OtherSize); err != nil {
return nil, err
}
out = append(out, row)
}
return out, rows.Err()
}
// ensureAutoLink inserts a symmetric auto edge (canonical order). Any
// existing row — auto or user verdict — blocks recreation (an invalidated
// edge never comes back by itself).
func (r *Resources) ensureAutoLink(from, to, linkType string, confidence float64) error {
a, b := canonicalPair(from, to)
id, err := NewIDErr()
if err != nil {
return err
}
_, err = r.DB.Exec(
`INSERT INTO resource_links (id, from_id, to_id, link_type, confidence, created_by)
VALUES ($1, $2, $3, $4, $5, 'auto')
ON CONFLICT (from_id, to_id, link_type) DO NOTHING`,
id, a, b, linkType, clamp01(confidence),
)
return err
}
// SetUserVerdict records an explicit verdict (created_by='user') : confirm →
// confidence 1.0, invalidate → 0.0. Upserts over any previous row (auto or
// older verdict) — the latest human word wins.
func (r *Resources) SetUserVerdict(one, other, linkType string, confirmed bool) error {
a, b := canonicalPair(one, other)
conf := 0.0
if confirmed {
conf = 1.0
}
id, err := NewIDErr()
if err != nil {
return err
}
_, err = r.DB.Exec(
`INSERT INTO resource_links (id, from_id, to_id, link_type, confidence, created_by)
VALUES ($1, $2, $3, $4, $5, 'user')
ON CONFLICT (from_id, to_id, link_type)
DO UPDATE SET confidence = EXCLUDED.confidence, created_by = 'user'`,
id, a, b, linkType, conf,
)
return err
}
// pruneAutoLinks drops auto edges of the symmetric types touching resourceID
// whose pair is no longer a candidate (facts/session edited). keep holds
// canonical "a\x00b\x00type" keys to preserve. User verdicts are never pruned.
func (r *Resources) pruneAutoLinks(resourceID string, keep map[string]bool) error {
rows, err := r.DB.Query(
`SELECT id, from_id, to_id, link_type FROM resource_links
WHERE (from_id = $1 OR to_id = $1) AND created_by = 'auto'`,
resourceID,
)
if err != nil {
return err
}
var stale []string
for rows.Next() {
var id, a, b, typ string
if err := rows.Scan(&id, &a, &b, &typ); err != nil {
rows.Close()
return err
}
if !keep[a+"\x00"+b+"\x00"+typ] {
stale = append(stale, id)
}
}
rows.Close()
if err := rows.Err(); err != nil {
return err
}
for _, id := range stale {
if _, err := r.DB.Exec(`DELETE FROM resource_links WHERE id = $1`, id); err != nil {
return err
}
}
return nil
}
// linkCandidate is one deterministic pair found by reconciliation.
type linkCandidate struct {
other string
typ string
confidence float64
}
// ReconcileLinks recomputes the deterministic auto edges of a live owned
// resource (same_scan, duplicate_of, same_issuer, same_reference), creates
// missing ones and prunes stale auto ones. User verdicts are preserved and
// block recreation. Idempotent — safe to call on every read (GET links).
func (r *Resources) ReconcileLinks(ownerID, resourceID string) error {
var session sql.NullString
var hash sql.NullString
var size int64
err := r.DB.QueryRow(
`SELECT COALESCE(scan_session_id, ''), COALESCE(content_hash, ''), COALESCE(size_bytes, 0)
FROM resources WHERE resource_id = $1 AND user_id = $2 AND deleted_at IS NULL`,
resourceID, ownerID,
).Scan(&session, &hash, &size)
if err == sql.ErrNoRows {
return ErrNotFound
}
if err != nil {
return err
}
var cands []linkCandidate
// same_scan : same opaque session, same user, live, not self.
if session.Valid && session.String != "" {
rows, err := r.DB.Query(
`SELECT resource_id FROM resources
WHERE user_id = $1 AND scan_session_id = $2 AND resource_id != $3 AND deleted_at IS NULL`,
ownerID, session.String, resourceID,
)
if err != nil {
return err
}
for rows.Next() {
var other string
if err := rows.Scan(&other); err != nil {
rows.Close()
return err
}
cands = append(cands, linkCandidate{other, LinkSameScan, 1.0})
}
rows.Close()
if err := rows.Err(); err != nil {
return err
}
}
// duplicate_of : same content hash (non-empty), size > 0, same user, live.
if hash.Valid && hash.String != "" && size > 0 {
rows, err := r.DB.Query(
`SELECT resource_id FROM resources
WHERE user_id = $1 AND content_hash = $2 AND size_bytes > 0
AND resource_id != $3 AND deleted_at IS NULL`,
ownerID, hash.String, resourceID,
)
if err != nil {
return err
}
for rows.Next() {
var other string
if err := rows.Scan(&other); err != nil {
rows.Close()
return err
}
cands = append(cands, linkCandidate{other, LinkDuplicateOf, 1.0})
}
rows.Close()
if err := rows.Err(); err != nil {
return err
}
}
// same_issuer / same_reference : shared fact values (case-insensitive
// for issuers, exact for reference codes), any origin (user corrections
// count — they ARE the household truth). Effective facts only : un fait
// auto est ignoré quand un fait user du même type existe sur la même
// ressource (miroir de `displayFacts` côté mobile — sinon une correction
// laisserait l'arête auto périmée).
for _, typ := range []string{"issuer", "reference"} {
linkTyp := LinkSameIssuer
if typ == "reference" {
linkTyp = LinkSameReference
}
rows, err := r.DB.Query(
`SELECT DISTINCT o.resource_id, LEAST(f.confidence, g.confidence)
FROM resource_facts f
JOIN resource_facts g ON g.fact_type = f.fact_type
AND g.resource_id != f.resource_id
AND CASE WHEN f.fact_type = 'issuer'
THEN LOWER(g.value_text) = LOWER(f.value_text)
ELSE g.value_text = f.value_text END
JOIN resources o ON o.resource_id = g.resource_id
WHERE f.resource_id = $1 AND f.fact_type = $2 AND f.value_text IS NOT NULL
AND (f.origin = 'user' OR NOT EXISTS (
SELECT 1 FROM resource_facts u WHERE u.resource_id = f.resource_id
AND u.fact_type = f.fact_type AND u.origin = 'user'))
AND (g.origin = 'user' OR NOT EXISTS (
SELECT 1 FROM resource_facts u WHERE u.resource_id = g.resource_id
AND u.fact_type = g.fact_type AND u.origin = 'user'))
AND o.user_id = $3 AND o.deleted_at IS NULL`,
resourceID, typ, ownerID,
)
if err != nil {
return err
}
for rows.Next() {
var other string
var conf float64
if err := rows.Scan(&other, &conf); err != nil {
rows.Close()
return err
}
cands = append(cands, linkCandidate{other, linkTyp, conf})
}
rows.Close()
if err := rows.Err(); err != nil {
return err
}
}
keep := make(map[string]bool, len(cands))
for _, c := range cands {
a, b := canonicalPair(resourceID, c.other)
keep[a+"\x00"+b+"\x00"+c.typ] = true
if err := r.ensureAutoLink(resourceID, c.other, c.typ, c.confidence); err != nil {
return err
}
}
return r.pruneAutoLinks(resourceID, keep)
}
// DuplicateGroup is one set of byte-identical live files (B2 screen).
type DuplicateGroup struct {
ContentHash string
Count int
TotalBytes int64
ReclaimableBytes int64 // total - biggest member (garder un seul)
Files []DuplicateFile
}
// DuplicateFile is one member of a duplicate group, newest first.
type DuplicateFile struct {
ID string
Name string
Size int64
UpdatedAt int64 // ms epoch
}
// ListDuplicateGroups returns the owner's byte-identical file sets
// (content_hash non-empty, size > 0, live only), biggest groups first.
func (r *Resources) ListDuplicateGroups(ownerID string) ([]DuplicateGroup, error) {
rows, err := r.DB.Query(
`SELECT content_hash, COUNT(*), COALESCE(SUM(size_bytes), 0)
FROM resources
WHERE user_id = $1 AND type = 'file' AND deleted_at IS NULL
AND content_hash IS NOT NULL AND content_hash <> '' AND size_bytes > 0
GROUP BY content_hash HAVING COUNT(*) > 1
ORDER BY SUM(size_bytes) DESC`,
ownerID,
)
if err != nil {
return nil, err
}
defer rows.Close()
out := []DuplicateGroup{}
for rows.Next() {
var g DuplicateGroup
if err := rows.Scan(&g.ContentHash, &g.Count, &g.TotalBytes); err != nil {
return nil, err
}
members, err := r.DB.Query(
`SELECT resource_id, name, COALESCE(size_bytes, 0),
(EXTRACT(EPOCH FROM updated_at) * 1000)::bigint
FROM resources
WHERE user_id = $1 AND type = 'file' AND deleted_at IS NULL AND content_hash = $2
ORDER BY updated_at DESC`,
ownerID, g.ContentHash,
)
if err != nil {
return nil, err
}
var biggest int64
for members.Next() {
var f DuplicateFile
if err := members.Scan(&f.ID, &f.Name, &f.Size, &f.UpdatedAt); err != nil {
members.Close()
return nil, err
}
if f.Size > biggest {
biggest = f.Size
}
g.Files = append(g.Files, f)
}
members.Close()
if err := members.Err(); err != nil {
return nil, err
}
g.ReclaimableBytes = g.TotalBytes - biggest
out = append(out, g)
}
if err := rows.Err(); err != nil {
return nil, err
}
return out, nil
}