From a32dc56d2e5447ef8988cb98b8686476cc94e70c Mon Sep 17 00:00:00 2001 From: soryu Date: Tue, 23 Dec 2025 02:14:58 +0000 Subject: Add Postgres for persistence and File cabinet Migrations are local only currently, and must be run manually by setting POSTGRES_CONNECTION_URI --- makima/src/db/mod.rs | 15 ++++++ makima/src/db/models.rs | 101 ++++++++++++++++++++++++++++++++++ makima/src/db/repository.rs | 128 ++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 244 insertions(+) create mode 100644 makima/src/db/mod.rs create mode 100644 makima/src/db/models.rs create mode 100644 makima/src/db/repository.rs (limited to 'makima/src/db') 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 { + 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, + #[sqlx(json)] + pub transcript: Vec, + pub location: Option, + pub created_at: DateTime, + pub updated_at: DateTime, +} + +/// 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, + /// Optional description + pub description: Option, + /// Transcript entries + pub transcript: Vec, + /// Storage location (e.g., s3://bucket/path) - not used yet + pub location: Option, +} + +/// Request payload for updating an existing file. +#[derive(Debug, Deserialize, ToSchema)] +#[serde(rename_all = "camelCase")] +pub struct UpdateFileRequest { + /// New name (optional) + pub name: Option, + /// New description (optional) + pub description: Option, + /// New transcript (optional) + pub transcript: Option>, +} + +/// Response for file list endpoint. +#[derive(Debug, Serialize, ToSchema)] +#[serde(rename_all = "camelCase")] +pub struct FileListResponse { + pub files: Vec, + 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, + pub transcript_count: usize, + /// Duration derived from last transcript end time + pub duration: Option, + pub created_at: DateTime, + pub updated_at: DateTime, +} + +impl From 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 { + 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, 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, 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, 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 { + 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 { + 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) +} -- cgit v1.2.3