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

systemprompt_files/repository/content/
mod.rs
