systemprompt_files/repository/content/
mod.rs

1use anyhow::{Context, Result};
2use chrono::Utc;
3use systemprompt_identifiers::{ContentId, ContextId, FileId, SessionId, TraceId, UserId};
4
5use super::file::FileRepository;
6use crate::models::{ContentFile, File, FileRole};
7
8impl FileRepository {
9    pub async fn link_to_content(
10        &self,
11        content_id: &ContentId,
12        file_id: &FileId,
13        role: FileRole,
14        display_order: i32,
15    ) -> Result<ContentFile> {
16        let file_id_uuid =
17            uuid::Uuid::parse_str(file_id.as_str()).context("Invalid UUID for file id")?;
18        let now = Utc::now();
19        let content_id_str = content_id.as_str();
20
21        sqlx::query_as!(
22            ContentFile,
23            r#"
24            INSERT INTO content_files (content_id, file_id, role, display_order, created_at)
25            VALUES ($1, $2, $3, $4, $5)
26            ON CONFLICT (content_id, file_id, role) DO UPDATE
27            SET display_order = $4
28            RETURNING id, content_id as "content_id: ContentId", file_id, role, display_order, created_at
29            "#,
30            content_id_str,
31            file_id_uuid,
32            role.as_str(),
33            display_order,
34            now
35        )
36        .fetch_one(self.pool.as_ref())
37        .await
38        .context(format!(
39            "Failed to link file {file_id} to content {content_id}"
40        ))
41    }
42
43    pub async fn unlink_from_content(
44        &self,
45        content_id: &ContentId,
46        file_id: &FileId,
47    ) -> Result<()> {
48        let file_id_uuid =
49            uuid::Uuid::parse_str(file_id.as_str()).context("Invalid UUID for file id")?;
50        let content_id_str = content_id.as_str();
51
52        sqlx::query!(
53            r#"
54            DELETE FROM content_files
55            WHERE content_id = $1 AND file_id = $2
56            "#,
57            content_id_str,
58            file_id_uuid
59        )
60        .execute(self.pool.as_ref())
61        .await
62        .context(format!(
63            "Failed to unlink file {file_id} from content {content_id}"
64        ))?;
65
66        Ok(())
67    }
68
69    pub async fn list_files_by_content(
70        &self,
71        content_id: &ContentId,
72    ) -> Result<Vec<(File, ContentFile)>> {
73        let content_id_str = content_id.as_str();
74        let rows = sqlx::query!(
75            r#"
76            SELECT
77                f.id, f.path, f.public_url, f.mime_type, f.size_bytes, f.ai_content,
78                f.metadata, f.user_id, f.session_id, f.trace_id, f.context_id, f.created_at, f.updated_at, f.deleted_at,
79                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
80            FROM files f
81            INNER JOIN content_files cf ON cf.file_id = f.id
82            WHERE cf.content_id = $1 AND f.deleted_at IS NULL
83            ORDER BY cf.display_order ASC, cf.created_at ASC
84            "#,
85            content_id_str
86        )
87        .fetch_all(self.pool.as_ref())
88        .await
89        .context(format!("Failed to list files for content: {content_id}"))?;
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) -> Result<Option<File>> {
126        let content_id_str = content_id.as_str();
127        let featured_role = FileRole::Featured.as_str();
128        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        .context(format!(
146            "Failed to find featured image for content: {content_id}"
147        ))
148    }
149
150    pub async fn set_featured(&self, file_id: &FileId, content_id: &ContentId) -> Result<()> {
151        let file_id_uuid =
152            uuid::Uuid::parse_str(file_id.as_str()).context("Invalid UUID for file id")?;
153        let content_id_str = content_id.as_str();
154        let featured_role = FileRole::Featured.as_str();
155        let attachment_role = FileRole::Attachment.as_str();
156
157        let mut tx = self.pool.begin().await?;
158
159        sqlx::query!(
160            r#"
161            UPDATE content_files
162            SET role = $1
163            WHERE content_id = $2 AND role = $3
164            "#,
165            attachment_role,
166            content_id_str,
167            featured_role
168        )
169        .execute(&mut *tx)
170        .await?;
171
172        let result = sqlx::query!(
173            r#"
174            UPDATE content_files
175            SET role = $1
176            WHERE file_id = $2 AND content_id = $3
177            "#,
178            featured_role,
179            file_id_uuid,
180            content_id_str
181        )
182        .execute(&mut *tx)
183        .await?;
184
185        if result.rows_affected() == 0 {
186            return Err(anyhow::anyhow!(
187                "File {} is not linked to content {}",
188                file_id,
189                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) -> Result<Vec<ContentFile>> {
198        let file_id_uuid =
199            uuid::Uuid::parse_str(file_id.as_str()).context("Invalid UUID for file id")?;
200
201        sqlx::query_as!(
202            ContentFile,
203            r#"
204            SELECT id, content_id as "content_id: ContentId", file_id, role, 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        .context(format!("Failed to list content for file: {file_id}"))
214    }
215}
systemprompt_files/repository/content/mod.rs

systemprompt_files/repository/content/
mod.rs
