systemprompt_files/repository/content/
mod.rs

1//! [`FileRepository`] queries for file/content associations.
2//!
3//! Linking and unlinking files to content, listing the files for a piece of
4//! content (and vice versa), and managing the single featured-image role.
5//!
6//! Copyright (c) systemprompt.io — Business Source License 1.1.
7//! See <https://systemprompt.io> for licensing details.
8
9use chrono::Utc;
10use systemprompt_identifiers::{ContentId, ContextId, FileId, SessionId, TraceId, UserId};
11
12use super::file::FileRepository;
13use crate::error::{FilesError, FilesResult, parse_file_uuid};
14use crate::models::{ContentFile, ContentFileRow, File, FileMetadata, FileRole, FileRow};
15
16impl FileRepository {
17    pub async fn link_to_content(
18        &self,
19        content_id: &ContentId,
20        file_id: &FileId,
21        role: FileRole,
22        display_order: i32,
23    ) -> FilesResult<ContentFile> {
24        let file_id_uuid = parse_file_uuid(file_id)?;
25        let now = Utc::now();
26        let content_id_str = content_id.as_str();
27
28        let result = sqlx::query_as!(
29            ContentFileRow,
30            r#"
31            INSERT INTO content_files (content_id, file_id, role, display_order, created_at)
32            VALUES ($1, $2, $3, $4, $5)
33            ON CONFLICT (content_id, file_id, role) DO UPDATE
34            SET display_order = $4
35            RETURNING id, content_id as "content_id: ContentId", file_id, role as "role: FileRole", display_order, created_at
36            "#,
37            content_id_str,
38            file_id_uuid,
39            role.as_str(),
40            display_order,
41            now
42        )
43        .fetch_one(self.pool.as_ref())
44        .await?;
45
46        Ok(ContentFile::from(result))
47    }
48
49    pub async fn unlink_from_content(
50        &self,
51        content_id: &ContentId,
52        file_id: &FileId,
53    ) -> FilesResult<()> {
54        let file_id_uuid = parse_file_uuid(file_id)?;
55        let content_id_str = content_id.as_str();
56
57        sqlx::query!(
58            r#"
59            DELETE FROM content_files
60            WHERE content_id = $1 AND file_id = $2
61            "#,
62            content_id_str,
63            file_id_uuid
64        )
65        .execute(self.pool.as_ref())
66        .await?;
67
68        Ok(())
69    }
70
71    pub async fn list_files_by_content(
72        &self,
73        content_id: &ContentId,
74    ) -> FilesResult<Vec<(File, ContentFile)>> {
75        let content_id_str = content_id.as_str();
76        let rows = sqlx::query!(
77            r#"
78            SELECT
79                f.id, f.path, f.public_url, f.mime_type, f.size_bytes, f.ai_content,
80                f.metadata as "metadata: sqlx::types::Json<FileMetadata>", f.user_id, f.session_id, f.trace_id, f.context_id, f.created_at, f.updated_at, f.deleted_at,
81                cf.id as cf_id, cf.content_id, cf.file_id as cf_file_id, cf.role as "role: FileRole", cf.display_order, cf.created_at as cf_created_at
82            FROM files f
83            INNER JOIN content_files cf ON cf.file_id = f.id
84            WHERE cf.content_id = $1 AND f.deleted_at IS NULL
85            ORDER BY cf.display_order ASC, cf.created_at ASC
86            "#,
87            content_id_str
88        )
89        .fetch_all(self.pool.as_ref())
90        .await?;
91
92        Ok(rows
93            .into_iter()
94            .map(|row| {
95                let file = File {
96                    id: FileId::from_uuid(row.id),
97                    path: row.path,
98                    public_url: row.public_url,
99                    mime_type: row.mime_type,
100                    size_bytes: row.size_bytes,
101                    ai_content: row.ai_content,
102                    metadata: row.metadata,
103                    user_id: row.user_id.map(UserId::new),
104                    session_id: row.session_id.map(SessionId::new),
105                    trace_id: row.trace_id.map(TraceId::new),
106                    context_id: row.context_id.and_then(|c| match ContextId::try_new(c) {
107                        Ok(id) => Some(id),
108                        Err(e) => {
109                            tracing::warn!(error = %e, "stored context_id is malformed; dropping");
110                            None
111                        },
112                    }),
113                    created_at: row.created_at,
114                    updated_at: row.updated_at,
115                    deleted_at: row.deleted_at,
116                };
117
118                let content_file = ContentFile {
119                    id: row.cf_id,
120                    content_id: ContentId::new(row.content_id),
121                    file_id: FileId::from_uuid(row.cf_file_id),
122                    role: row.role,
123                    display_order: row.display_order,
124                    created_at: row.cf_created_at,
125                };
126
127                (file, content_file)
128            })
129            .collect())
130    }
131
132    pub async fn find_featured_image(&self, content_id: &ContentId) -> FilesResult<Option<File>> {
133        let content_id_str = content_id.as_str();
134        let featured_role = FileRole::Featured.as_str();
135        let result = sqlx::query_as!(
136            FileRow,
137            r#"
138            SELECT f.id, f.path, f.public_url, f.mime_type, f.size_bytes, f.ai_content,
139                   f.metadata as "metadata: sqlx::types::Json<FileMetadata>", f.user_id as "user_id: UserId", f.session_id as "session_id: SessionId", f.trace_id as "trace_id: TraceId", f.context_id as "context_id: ContextId", f.created_at, f.updated_at, f.deleted_at
140            FROM files f
141            INNER JOIN content_files cf ON cf.file_id = f.id
142            WHERE cf.content_id = $1
143              AND cf.role = $2
144              AND f.deleted_at IS NULL
145            LIMIT 1
146            "#,
147            content_id_str,
148            featured_role
149        )
150        .fetch_optional(self.pool.as_ref())
151        .await?;
152
153        Ok(result.map(File::from))
154    }
155
156    pub async fn set_featured(&self, file_id: &FileId, content_id: &ContentId) -> FilesResult<()> {
157        let file_id_uuid = parse_file_uuid(file_id)?;
158        let content_id_str = content_id.as_str();
159        let featured_role = FileRole::Featured.as_str();
160        let attachment_role = FileRole::Attachment.as_str();
161
162        let mut tx = self.pool.begin().await?;
163
164        sqlx::query!(
165            r#"
166            UPDATE content_files
167            SET role = $1
168            WHERE content_id = $2 AND role = $3
169            "#,
170            attachment_role,
171            content_id_str,
172            featured_role
173        )
174        .execute(&mut *tx)
175        .await?;
176
177        let result = sqlx::query!(
178            r#"
179            UPDATE content_files
180            SET role = $1
181            WHERE file_id = $2 AND content_id = $3
182            "#,
183            featured_role,
184            file_id_uuid,
185            content_id_str
186        )
187        .execute(&mut *tx)
188        .await?;
189
190        if result.rows_affected() == 0 {
191            return Err(FilesError::NotFound(format!(
192                "File {file_id} is not linked to content {content_id}"
193            )));
194        }
195
196        tx.commit().await?;
197        Ok(())
198    }
199
200    pub async fn list_content_by_file(&self, file_id: &FileId) -> FilesResult<Vec<ContentFile>> {
201        let file_id_uuid = parse_file_uuid(file_id)?;
202
203        let result = sqlx::query_as!(
204            ContentFileRow,
205            r#"
206            SELECT id, content_id as "content_id: ContentId", file_id, role as "role: FileRole", display_order, created_at
207            FROM content_files
208            WHERE file_id = $1
209            ORDER BY created_at ASC
210            "#,
211            file_id_uuid
212        )
213        .fetch_all(self.pool.as_ref())
214        .await?;
215
216        Ok(result.into_iter().map(ContentFile::from).collect())
217    }
218}
systemprompt_files/repository/content/mod.rs

systemprompt_files/repository/content/
mod.rs
