Danny Kopping
58 lines
2.3 KiB
SQL
58 lines
2.3 KiB
SQL
-- name: InsertChatFile :one
|
|
INSERT INTO chat_files (owner_id, organization_id, name, mimetype, data, object_store_key)
|
|
VALUES (@owner_id::uuid, @organization_id::uuid, @name::text, @mimetype::text, @data::bytea, @object_store_key::text)
|
|
RETURNING id, owner_id, organization_id, created_at, name, mimetype;
|
|
|
|
-- name: GetChatFileByID :one
|
|
SELECT * FROM chat_files WHERE id = @id::uuid;
|
|
|
|
-- name: GetChatFilesByIDs :many
|
|
SELECT * FROM chat_files WHERE id = ANY(@ids::uuid[]);
|
|
|
|
-- name: GetChatFileMetadataByChatID :many
|
|
-- GetChatFileMetadataByChatID returns lightweight file metadata for
|
|
-- all files linked to a chat. The data column is excluded to avoid
|
|
-- loading file content.
|
|
SELECT cf.id, cf.owner_id, cf.organization_id, cf.name, cf.mimetype, cf.created_at
|
|
FROM chat_files cf
|
|
JOIN chat_file_links cfl ON cfl.file_id = cf.id
|
|
WHERE cfl.chat_id = @chat_id::uuid
|
|
ORDER BY cf.created_at ASC;
|
|
|
|
-- TODO(cian): Add indexes on chats(archived, updated_at) and
|
|
-- chat_files(created_at) for purge query performance.
|
|
-- See: https://github.com/coder/internal/issues/1438
|
|
-- name: DeleteOldChatFiles :many
|
|
-- Deletes chat files that are older than the given threshold and are
|
|
-- not referenced by any chat that is still active or was archived
|
|
-- within the same threshold window. This covers two cases:
|
|
-- 1. Orphaned files not linked to any chat.
|
|
-- 2. Files whose every referencing chat has been archived for longer
|
|
-- than the retention period.
|
|
-- Returns the deleted rows so callers can clean up associated object
|
|
-- store entries.
|
|
WITH kept_file_ids AS (
|
|
-- NOTE: This uses updated_at as a proxy for archive time
|
|
-- because there is no archived_at column. Correctness
|
|
-- requires that updated_at is never backdated on archived
|
|
-- chats. See ArchiveChatByID.
|
|
SELECT DISTINCT cfl.file_id
|
|
FROM chat_file_links cfl
|
|
JOIN chats c ON c.id = cfl.chat_id
|
|
WHERE c.archived = false
|
|
OR c.updated_at >= @before_time::timestamptz
|
|
),
|
|
deletable AS (
|
|
SELECT cf.id
|
|
FROM chat_files cf
|
|
LEFT JOIN kept_file_ids k ON cf.id = k.file_id
|
|
WHERE cf.created_at < @before_time::timestamptz
|
|
AND k.file_id IS NULL
|
|
ORDER BY cf.created_at ASC
|
|
LIMIT @limit_count
|
|
)
|
|
DELETE FROM chat_files
|
|
USING deletable
|
|
WHERE chat_files.id = deletable.id
|
|
RETURNING chat_files.id, chat_files.object_store_key;
|