Add Postgres for persistence and File cabinet

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

3 files changed, 244 insertions, 0 deletions

diff --git a/makima/src/db/mod.rs b/makima/src/db/mod.rs
new file mode 100644
index 0000000..dbfeeab
--- /dev/null
+++ b/makima/src/db/mod.rs
@@ -0,0 +1,15 @@
+//! Database module for PostgreSQL connectivity and models.
+
+pub mod models;
+pub mod repository;
+
+use sqlx::postgres::PgPoolOptions;
+use sqlx::PgPool;
+
+/// Create a database connection pool.
+pub async fn create_pool(database_url: &str) -> Result<PgPool, sqlx::Error> {
+    PgPoolOptions::new()
+        .max_connections(5)
+        .connect(database_url)
+        .await
+}
diff --git a/makima/src/db/models.rs b/makima/src/db/models.rs
new file mode 100644
index 0000000..45b0e53
--- /dev/null
+++ b/makima/src/db/models.rs
@@ -0,0 +1,101 @@
+//! Database models for the files table.
+
+use chrono::{DateTime, Utc};
+use serde::{Deserialize, Serialize};
+use sqlx::FromRow;
+use utoipa::ToSchema;
+use uuid::Uuid;
+
+/// TranscriptEntry stored in JSONB - matches frontend TranscriptEntry
+#[derive(Debug, Clone, Serialize, Deserialize, ToSchema)]
+#[serde(rename_all = "camelCase")]
+pub struct TranscriptEntry {
+    pub id: String,
+    pub speaker: String,
+    pub start: f32,
+    pub end: f32,
+    pub text: String,
+    pub is_final: bool,
+}
+
+/// File record from the database.
+#[derive(Debug, Clone, FromRow, Serialize, ToSchema)]
+#[serde(rename_all = "camelCase")]
+pub struct File {
+    pub id: Uuid,
+    pub owner_id: Uuid,
+    pub name: String,
+    pub description: Option<String>,
+    #[sqlx(json)]
+    pub transcript: Vec<TranscriptEntry>,
+    pub location: Option<String>,
+    pub created_at: DateTime<Utc>,
+    pub updated_at: DateTime<Utc>,
+}
+
+/// Request payload for creating a new file.
+#[derive(Debug, Deserialize, ToSchema)]
+#[serde(rename_all = "camelCase")]
+pub struct CreateFileRequest {
+    /// Name of the file (auto-generated if not provided)
+    pub name: Option<String>,
+    /// Optional description
+    pub description: Option<String>,
+    /// Transcript entries
+    pub transcript: Vec<TranscriptEntry>,
+    /// Storage location (e.g., s3://bucket/path) - not used yet
+    pub location: Option<String>,
+}
+
+/// Request payload for updating an existing file.
+#[derive(Debug, Deserialize, ToSchema)]
+#[serde(rename_all = "camelCase")]
+pub struct UpdateFileRequest {
+    /// New name (optional)
+    pub name: Option<String>,
+    /// New description (optional)
+    pub description: Option<String>,
+    /// New transcript (optional)
+    pub transcript: Option<Vec<TranscriptEntry>>,
+}
+
+/// Response for file list endpoint.
+#[derive(Debug, Serialize, ToSchema)]
+#[serde(rename_all = "camelCase")]
+pub struct FileListResponse {
+    pub files: Vec<FileSummary>,
+    pub total: i64,
+}
+
+/// Summary of a file for list views (excludes full transcript).
+#[derive(Debug, Clone, Serialize, ToSchema)]
+#[serde(rename_all = "camelCase")]
+pub struct FileSummary {
+    pub id: Uuid,
+    pub name: String,
+    pub description: Option<String>,
+    pub transcript_count: usize,
+    /// Duration derived from last transcript end time
+    pub duration: Option<f32>,
+    pub created_at: DateTime<Utc>,
+    pub updated_at: DateTime<Utc>,
+}
+
+impl From<File> for FileSummary {
+    fn from(file: File) -> Self {
+        let duration = file
+            .transcript
+            .iter()
+            .map(|t| t.end)
+            .fold(0.0_f32, f32::max);
+        Self {
+            id: file.id,
+            name: file.name,
+            description: file.description,
+            transcript_count: file.transcript.len(),
+            duration: if duration > 0.0 { Some(duration) } else { None },
+            created_at: file.created_at,
+            updated_at: file.updated_at,
+        }
+    }
+}
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)
+}