query-orchestration/internal/database/queries/document.sql

-- name: GetDocumentSummary :one
SELECT id, clientId, hash FROM documents WHERE id = $1;

-- name: GetDocumentExternal :one
-- Query functionality has been removed. See remove_query_plan.md for details.
SELECT
  id,
  clientId,
  hash
FROM documents
WHERE id = @documentId;

-- name: ListDocumentsByClient :many
SELECT id, hash from documents where clientId = @clientId;

-- name: ListDocumentsByBatch :many
SELECT id, hash, filename from documents where batch_id = @batch_id;

-- name: CreateDocument :one
INSERT INTO documents (clientId, hash, batch_id, filename, folderId, originalPath, file_size_bytes) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id;

-- name: AddDocumentEntry :exec
INSERT INTO documentEntries (documentId, bucket, key) VALUES ($1, $2, $3);

-- name: GetDocumentEntry :one
SELECT documentId, bucket, key FROM documentEntries WHERE documentId = $1 ORDER BY id DESC LIMIT 1;

-- name: GetDocumentIDByHash :one
SELECT id FROM documents WHERE hash = $1 and clientId = $2;

-- name: ListDocumentIDsBatch :many
SELECT id, totalCount FROM listDocumentIDs(@clientId, @batchSize, @pageOffset);

-- name: AddDocumentUpload :exec
INSERT INTO documentUploads (id, clientId, bucket, key, part, createdAt, filename, batch_id, folder_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9);

-- name: GetDocumentUploadByKey :one
SELECT id, clientId, bucket, key, part, createdAt, filename, batch_id, folder_id FROM documentUploads WHERE bucket = $1 AND key = $2 LIMIT 1;

-- name: ListDocumentUploads :many
SELECT id, clientId, bucket, key, part, createdAt, filename, batch_id, folder_id FROM documentUploads WHERE clientId = $1 ORDER BY createdAt;

-- name: GetDocumentUploadCurrentPart :one
WITH client as (
    select clientId from clients where clientId = @clientId
),
parts as (
    SELECT docs.part
        FROM client as c
        JOIN documentUploads as docs on docs.clientId = c.clientId
        WHERE date(docs.createdAt) = date(@queryDate)
),
max_part as (
    SELECT COALESCE(max(part), 0) as max_part_num FROM parts
)
SELECT
    COALESCE(p.part, 0)::unsignedsmallint as part,
    COUNT(p.part) as count
FROM parts p
RIGHT JOIN max_part mp ON p.part = mp.max_part_num
GROUP BY p.part;

-- name: GetDocumentEnriched :one
-- Retrieves a document with extended metadata including folder and filename.
-- Milestone 2.3a adds custom_schema_id and a correlated EXISTS probe into
-- document_custom_metadata so GET /document/{id} can surface both fields
-- without a follow-up query.
SELECT
    d.id,
    d.clientId,
    d.hash,
    d.folderId,
    d.filename,
    d.originalPath,
    d.file_size_bytes,
    d.custom_schema_id,
    EXISTS(
        SELECT 1 FROM document_custom_metadata
        WHERE document_id = d.id
    ) AS has_custom_metadata
FROM documents d
WHERE d.id = $1;

-- name: GetDocumentEnrichedForClient :one
-- Chatbot-scoped read: same column set as GetDocumentEnriched, but adds a
-- clientId filter so cross-client lookups miss with pgx.ErrNoRows.
-- Plan §5 mandates a clientId filter on every chatbot document/folder
-- read so document scope cannot leak between clients. documents.clientId
-- is the unquoted (lowercased) varchar column from migration 5.
SELECT
    d.id,
    d.clientId,
    d.hash,
    d.folderId,
    d.filename,
    d.originalPath,
    d.file_size_bytes,
    d.custom_schema_id,
    EXISTS(
        SELECT 1 FROM document_custom_metadata
        WHERE document_id = d.id
    ) AS has_custom_metadata
FROM documents d
WHERE d.id = @id
  AND d.clientId = @client_id;

-- name: LockDocumentForLegacyExtractionWrite :one
-- Milestone 2.5 parent-row lock acquired by CreateFieldExtraction as the
-- FIRST DML statement inside its transaction. Serializes with AssignSchema
-- (which takes FOR UPDATE on the same row) so the mutual-exclusivity
-- invariant from plan Section 7.3 holds even if Trigger 2 is ever removed.
-- Returns the current custom_schema_id so the caller can short-circuit to
-- ErrMutualExclusivityViolation when the document is already bound.
SELECT id, custom_schema_id
FROM documents
WHERE id = @document_id
FOR UPDATE;

-- name: CollectDocumentS3Paths :many
-- @sqlc-vet-disable
-- Collect S3 bucket+key from documentEntries before deleting, for logging orphaned S3 objects
SELECT de.bucket, de.key FROM documentEntries de WHERE de.documentId = @document_id;

-- name: DeleteDocumentFieldExtractionArrayFields :exec
-- @sqlc-vet-disable
-- Delete array fields for all field extractions belonging to this document
DELETE FROM documentFieldExtractionArrayFields
WHERE fieldExtractionId IN (
    SELECT id FROM documentFieldExtractions WHERE documentId = @document_id
);

-- name: DeleteDocumentFieldExtractionVersions :exec
-- @sqlc-vet-disable
-- Delete version entries for all field extractions belonging to this document
DELETE FROM documentFieldExtractionVersions
WHERE documentId = @document_id;

-- name: DeleteDocumentFieldExtractions :exec
-- @sqlc-vet-disable
-- Delete field extraction records for this document
DELETE FROM documentFieldExtractions WHERE documentId = @document_id;

-- name: DeleteDocumentCleanEntries :exec
-- @sqlc-vet-disable
-- Delete clean entries for all cleans belonging to this document
DELETE FROM documentCleanEntries
WHERE cleanId IN (
    SELECT id FROM documentCleans WHERE documentId = @document_id
);

-- name: DeleteDocumentCleans :exec
-- @sqlc-vet-disable
-- Delete clean records for this document
DELETE FROM documentCleans WHERE documentId = @document_id;

-- name: DeleteDocumentEntries :exec
-- @sqlc-vet-disable
-- Delete S3 entry records for this document
DELETE FROM documentEntries WHERE documentId = @document_id;

-- name: HardDeleteDocument :execrows
-- @sqlc-vet-disable
-- Hard delete the document row itself. Returns the number of rows deleted (0 or 1).
-- documentLabels are removed automatically via ON DELETE CASCADE.
DELETE FROM documents WHERE id = @document_id;