You've already forked agentic-coding-workflow
36 lines
1.2 KiB
SQL
36 lines
1.2 KiB
SQL
-- name: InsertAttachment :one
|
|
INSERT INTO message_attachments (id, user_id, message_id, filename, mime_type, size_bytes, sha256, storage_path)
|
|
VALUES ($1, $2, NULL, $3, $4, $5, $6, $7)
|
|
RETURNING *;
|
|
|
|
-- name: GetAttachment :one
|
|
SELECT * FROM message_attachments WHERE id = $1;
|
|
|
|
-- name: ListAttachmentsForMessage :many
|
|
SELECT * FROM message_attachments WHERE message_id = $1 ORDER BY created_at ASC;
|
|
|
|
-- name: ListOrphanAttachmentsForUser :many
|
|
SELECT * FROM message_attachments
|
|
WHERE user_id = $1 AND message_id IS NULL AND id = ANY($2::uuid[]);
|
|
|
|
-- name: AttachToMessage :exec
|
|
UPDATE message_attachments SET message_id = $2 WHERE id = ANY($1::uuid[]);
|
|
|
|
-- name: DeleteAttachment :exec
|
|
DELETE FROM message_attachments WHERE id = $1;
|
|
|
|
-- name: ListExpiredOrphans :many
|
|
SELECT id, storage_path FROM message_attachments
|
|
WHERE message_id IS NULL AND created_at < NOW() - ($1::interval);
|
|
|
|
-- name: ListAttachmentsForSoftDeletedConversations :many
|
|
SELECT a.id, a.storage_path
|
|
FROM message_attachments a
|
|
JOIN messages m ON m.id = a.message_id
|
|
JOIN conversations c ON c.id = m.conversation_id
|
|
WHERE c.deleted_at IS NOT NULL
|
|
AND c.deleted_at < NOW() - ($1::interval);
|
|
|
|
-- name: DeleteAttachmentsByIDs :exec
|
|
DELETE FROM message_attachments WHERE id = ANY($1::uuid[]);
|