Jay Brown
60 lines
1.6 KiB
SQL
60 lines
1.6 KiB
SQL
-- name: ApplyLabel :one
|
|
INSERT INTO documentLabels (documentId, label, appliedBy)
|
|
VALUES ($1, $2, $3)
|
|
RETURNING *;
|
|
|
|
-- name: GetDocumentLabels :many
|
|
SELECT * FROM documentLabels
|
|
WHERE documentId = $1
|
|
ORDER BY appliedAt DESC;
|
|
|
|
-- name: GetDocumentLabelsForClient :many
|
|
-- Chatbot-scoped variant of GetDocumentLabels. Joins documents to enforce
|
|
-- the clientId filter so cross-client lookups return an empty slice
|
|
-- rather than another tenant's labels. Returns the same documentLabels
|
|
-- column shape as GetDocumentLabels (not the joined documents row).
|
|
-- Plan §5.
|
|
SELECT dl.*
|
|
FROM documentLabels dl
|
|
JOIN documents d ON d.id = dl.documentId
|
|
WHERE dl.documentId = @document_id
|
|
AND d.clientId = @client_id
|
|
ORDER BY dl.appliedAt DESC;
|
|
|
|
-- name: GetMostRecentLabel :one
|
|
SELECT * FROM documentLabels
|
|
WHERE documentId = $1 AND label = $2
|
|
ORDER BY appliedAt DESC
|
|
LIMIT 1;
|
|
|
|
-- name: GetDocumentsByLabel :many
|
|
SELECT DISTINCT d.*
|
|
FROM documents d
|
|
INNER JOIN documentLabels dl ON d.id = dl.documentId
|
|
WHERE d.clientId = $1 AND dl.label = $2
|
|
ORDER BY d.id;
|
|
|
|
-- name: GetDocumentsByLabelAndFolder :many
|
|
SELECT DISTINCT d.*
|
|
FROM documents d
|
|
INNER JOIN documentLabels dl ON d.id = dl.documentId
|
|
WHERE d.folderId = $1 AND dl.label = $2
|
|
ORDER BY d.id;
|
|
|
|
-- name: GetAllLabels :many
|
|
SELECT * FROM labels
|
|
ORDER BY label;
|
|
|
|
-- name: CreateLabel :one
|
|
INSERT INTO labels (label, description)
|
|
VALUES ($1, $2)
|
|
RETURNING *;
|
|
|
|
-- name: GetDocumentLabelHistory :many
|
|
SELECT dl.*, d.filename
|
|
FROM documentLabels dl
|
|
INNER JOIN documents d ON dl.documentId = d.id
|
|
WHERE d.clientId = $1
|
|
ORDER BY dl.appliedAt DESC
|
|
LIMIT $2 OFFSET $3;
|