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

systemprompt_files/repository/content/
mod.rs
