Add Postgres for persistence and File cabinet

Migrations are local only currently, and must be run manually by setting POSTGRES_CONNECTION_URI

1 files changed, 128 insertions, 0 deletions

diff --git a/makima/src/db/repository.rs b/makima/src/db/repository.rs
new file mode 100644
index 0000000..90cb1b9
--- /dev/null
+++ b/makima/src/db/repository.rs
@@ -0,0 +1,128 @@
+//! Repository pattern for file database operations.
+
+use chrono::Utc;
+use sqlx::PgPool;
+use uuid::Uuid;
+
+use super::models::{CreateFileRequest, File, UpdateFileRequest};
+
+/// Default owner ID for anonymous users.
+pub const ANONYMOUS_OWNER_ID: Uuid = Uuid::from_u128(0x00000000_0000_0000_0000_000000000002);
+
+/// Generate a default name based on current timestamp.
+fn generate_default_name() -> String {
+    let now = Utc::now();
+    now.format("Recording - %b %d %Y %H:%M:%S").to_string()
+}
+
+/// Create a new file record.
+pub async fn create_file(pool: &PgPool, req: CreateFileRequest) -> Result<File, sqlx::Error> {
+    let name = req.name.unwrap_or_else(generate_default_name);
+    let transcript_json = serde_json::to_value(&req.transcript).unwrap_or_default();
+
+    sqlx::query_as::<_, File>(
+        r#"
+        INSERT INTO files (owner_id, name, description, transcript, location)
+        VALUES ($1, $2, $3, $4, $5)
+        RETURNING id, owner_id, name, description, transcript, location, created_at, updated_at
+        "#,
+    )
+    .bind(ANONYMOUS_OWNER_ID)
+    .bind(&name)
+    .bind(&req.description)
+    .bind(&transcript_json)
+    .bind(&req.location)
+    .fetch_one(pool)
+    .await
+}
+
+/// Get a file by ID.
+pub async fn get_file(pool: &PgPool, id: Uuid) -> Result<Option<File>, sqlx::Error> {
+    sqlx::query_as::<_, File>(
+        r#"
+        SELECT id, owner_id, name, description, transcript, location, created_at, updated_at
+        FROM files
+        WHERE id = $1 AND owner_id = $2
+        "#,
+    )
+    .bind(id)
+    .bind(ANONYMOUS_OWNER_ID)
+    .fetch_optional(pool)
+    .await
+}
+
+/// List all files for the owner, ordered by created_at DESC.
+pub async fn list_files(pool: &PgPool) -> Result<Vec<File>, sqlx::Error> {
+    sqlx::query_as::<_, File>(
+        r#"
+        SELECT id, owner_id, name, description, transcript, location, created_at, updated_at
+        FROM files
+        WHERE owner_id = $1
+        ORDER BY created_at DESC
+        "#,
+    )
+    .bind(ANONYMOUS_OWNER_ID)
+    .fetch_all(pool)
+    .await
+}
+
+/// Update a file by ID.
+pub async fn update_file(
+    pool: &PgPool,
+    id: Uuid,
+    req: UpdateFileRequest,
+) -> Result<Option<File>, sqlx::Error> {
+    // Get the existing file first
+    let existing = get_file(pool, id).await?;
+    let Some(existing) = existing else {
+        return Ok(None);
+    };
+
+    // Apply updates
+    let name = req.name.unwrap_or(existing.name);
+    let description = req.description.or(existing.description);
+    let transcript = req.transcript.unwrap_or(existing.transcript);
+    let transcript_json = serde_json::to_value(&transcript).unwrap_or_default();
+
+    sqlx::query_as::<_, File>(
+        r#"
+        UPDATE files
+        SET name = $3, description = $4, transcript = $5
+        WHERE id = $1 AND owner_id = $2
+        RETURNING id, owner_id, name, description, transcript, location, created_at, updated_at
+        "#,
+    )
+    .bind(id)
+    .bind(ANONYMOUS_OWNER_ID)
+    .bind(&name)
+    .bind(&description)
+    .bind(&transcript_json)
+    .fetch_optional(pool)
+    .await
+}
+
+/// Delete a file by ID.
+pub async fn delete_file(pool: &PgPool, id: Uuid) -> Result<bool, sqlx::Error> {
+    let result = sqlx::query(
+        r#"
+        DELETE FROM files
+        WHERE id = $1 AND owner_id = $2
+        "#,
+    )
+    .bind(id)
+    .bind(ANONYMOUS_OWNER_ID)
+    .execute(pool)
+    .await?;
+
+    Ok(result.rows_affected() > 0)
+}
+
+/// Count total files for owner.
+pub async fn count_files(pool: &PgPool) -> Result<i64, sqlx::Error> {
+    let result: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM files WHERE owner_id = $1")
+        .bind(ANONYMOUS_OWNER_ID)
+        .fetch_one(pool)
+        .await?;
+
+    Ok(result.0)
+}