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.map(ContextId::new),
109                    created_at: row.created_at,
110                    updated_at: row.updated_at,
111                    deleted_at: row.deleted_at,
112                };
113
114                let content_file = ContentFile {
115                    id: row.cf_id,
116                    content_id: ContentId::new(row.content_id),
117                    file_id: row.cf_file_id,
118                    role: row.role,
119                    display_order: row.display_order,
120                    created_at: row.cf_created_at,
121                };
122
123                (file, content_file)
124            })
125            .collect())
126    }
127
128    pub async fn find_featured_image(&self, content_id: &ContentId) -> FilesResult<Option<File>> {
129        let content_id_str = content_id.as_str();
130        let featured_role = FileRole::Featured.as_str();
131        let result = sqlx::query_as!(
132            File,
133            r#"
134            SELECT f.id, f.path, f.public_url, f.mime_type, f.size_bytes, f.ai_content,
135                   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
136            FROM files f
137            INNER JOIN content_files cf ON cf.file_id = f.id
138            WHERE cf.content_id = $1
139              AND cf.role = $2
140              AND f.deleted_at IS NULL
141            LIMIT 1
142            "#,
143            content_id_str,
144            featured_role
145        )
146        .fetch_optional(self.pool.as_ref())
147        .await?;
148
149        Ok(result)
150    }
151
152    pub async fn set_featured(&self, file_id: &FileId, content_id: &ContentId) -> FilesResult<()> {
153        let file_id_uuid = uuid::Uuid::parse_str(file_id.as_str())
154            .map_err(|e| FilesError::Validation(format!("Invalid UUID for file id: {e}")))?;
155        let content_id_str = content_id.as_str();
156        let featured_role = FileRole::Featured.as_str();
157        let attachment_role = FileRole::Attachment.as_str();
158
159        let mut tx = self.pool.begin().await?;
160
161        sqlx::query!(
162            r#"
163            UPDATE content_files
164            SET role = $1
165            WHERE content_id = $2 AND role = $3
166            "#,
167            attachment_role,
168            content_id_str,
169            featured_role
170        )
171        .execute(&mut *tx)
172        .await?;
173
174        let result = sqlx::query!(
175            r#"
176            UPDATE content_files
177            SET role = $1
178            WHERE file_id = $2 AND content_id = $3
179            "#,
180            featured_role,
181            file_id_uuid,
182            content_id_str
183        )
184        .execute(&mut *tx)
185        .await?;
186
187        if result.rows_affected() == 0 {
188            return Err(FilesError::NotFound(format!(
189                "File {file_id} is not linked to content {content_id}"
190            )));
191        }
192
193        tx.commit().await?;
194        Ok(())
195    }
196
197    pub async fn list_content_by_file(&self, file_id: &FileId) -> FilesResult<Vec<ContentFile>> {
198        let file_id_uuid = uuid::Uuid::parse_str(file_id.as_str())
199            .map_err(|e| FilesError::Validation(format!("Invalid UUID for file id: {e}")))?;
200
201        let result = sqlx::query_as!(
202            ContentFile,
203            r#"
204            SELECT id, content_id as "content_id: ContentId", file_id, role as "role: FileRole", display_order, created_at
205            FROM content_files
206            WHERE file_id = $1
207            ORDER BY created_at ASC
208            "#,
209            file_id_uuid
210        )
211        .fetch_all(self.pool.as_ref())
212        .await?;
213
214        Ok(result)
215    }
216}
systemprompt_files/repository/content/mod.rs

systemprompt_files/repository/content/
mod.rs
