SeaORM Migrations for DMS

This directory contains the SeaORM migration system for the dank macro stash.

Overview

The migration system uses SeaORM’s built-in migration framework to manage database schema changes in a version-controlled, reproducible way.

Structure

• migration/ - SeaORM migration crate
  • src/lib.rs - Main migration library with all registered migrations
  • src/main.rs - CLI binary for running migrations
  • src/m20241229_000001_create_users_table.rs - User authentication tables
  • src/m20241229_000002_create_uploads_table.rs - File upload and processing tables

Migrations Included

Migration 1: User Authentication

• users - User accounts with username/password authentication
• registration_tokens - Invite-only registration system with expiration
• user_sessions - Session-based authentication with token management

Migration 2: File Upload & Management

• uploads - Main file storage with comprehensive metadata
• tags - Namespaced tagging system (e.g., desc:dog, ocr:invoice)
• upload_tags - Many-to-many relationship with confidence scores
• ocr_results - Text extraction results
• ai_descriptions - AI-powered image descriptions
• similar_uploads - Image similarity relationships

Running Migrations

Prerequisites

1. Start the PostgreSQL database:

   docker-compose up -d postgres

Using the Migration CLI

1. Fresh setup (runs all pending migrations):

   cd migration
   cargo run -- fresh

2. Apply pending migrations:

   cd migration
   cargo run -- up

3. Rollback last migration:

   cd migration
   cargo run -- down

4. Check migration status:

   cd migration
   cargo run -- status

Database Connection

Set the DATABASE_URL environment variable:

export DATABASE_URL="postgres://dms_user:dms_password@localhost:5432/dms"

Or create a .env file in the migration directory:

DATABASE_URL=postgres://dms_user:dms_password@localhost:5432/dms

Key Features

Authentication System

• Invite-only registration - Users can only register with valid tokens
• Session management - Secure token-based sessions with expiration
• Password hashing - Ready for Argon2 or bcrypt integration

File Management

• Deduplication - SHA-256 hashing prevents duplicate uploads per user
• Multiple storage backends - Supports local, S3, etc.
• Processing pipeline - Status tracking for async processing jobs
• Comprehensive metadata - Original filenames, sizes, content types

Advanced Tagging

• Namespaced tags - Supports your desc:dog desc:labcoat requirement
• AI confidence scores - Track confidence for automatically generated tags
• Flexible search - Full-text search across all content

AI Processing Pipeline

• OCR integration - Multiple engine support (Tesseract, Google Vision, etc.)
• Image descriptions - AI-powered descriptions with model tracking
• Similarity detection - Find similar images with configurable algorithms
• Extensible - Easy to add new processing types

Performance Optimizations

• Comprehensive indexing - All foreign keys, search fields, and common queries
• Unique constraints - Prevent duplicates at database level
• Efficient queries - Designed for common access patterns
• Scalable schema - Ready for high-volume document processing

Next Steps

After running migrations, you can:

1. Generate SeaORM entities from the schema
2. Implement the business logic using the database models
3. Add additional migrations as features evolve