Document Management System (DMS)

An enterprise-grade document management mock application that drove the development of SIMPLE_SQL's most advanced features: eager loading, cursor-based pagination, N+1 detection, and soft delete scopes.

Domain Overview

The DMS implements a realistic document management system with:

• Hierarchical Folders - Nested folder structure with path tracking
• Document Versioning - Version history with content diffs
• Comments - Threaded comments on documents
• Tags - Many-to-many tagging system
• Sharing Permissions - User access control per document
• Full-Text Search - FTS5 integration for content search
• Audit Trail - Automatic change logging via triggers
• Soft Deletes - Trash/restore functionality

Schema

-- Core tables
folders (id, name, parent_id, path, deleted_at)
documents (id, name, folder_id, content, version, created_by, deleted_at)
document_versions (id, document_id, version_number, content, created_at)

-- Relationships
comments (id, document_id, user_id, content, parent_id)
tags (id, name)
document_tags (document_id, tag_id)  -- Many-to-many
shares (id, document_id, user_id, permission)

-- Search & Audit
documents_fts (FTS5 virtual table)
audit_log (id, table_name, row_id, action, old_data, new_data, timestamp)

Friction Points Discovered

Building the DMS exposed several pain points that led to new SIMPLE_SQL features:

-- Before: 301 queries for 100 documents with 3 relations
across documents as doc loop
    folder := query_folder (doc.folder_id)    -- Query!
    author := query_user (doc.created_by)      -- Query!
    tags := query_tags_for (doc.id)            -- Query!
end

-- After: 4 queries total
result := db.eager_loader
    .from_table ("documents")
    .include ("folder", "folders", "folder_id", "id")
    .include ("author", "users", "created_by", "id")
    .include_many_to_many ("tags", "tags", "document_tags", "document_id", "tag_id")
    .execute

-- Before: Repetitive and error-prone
db.select_from ("documents").where ("folder_id = ? AND deleted_at IS NULL", id).execute
db.select_from ("folders").where ("parent_id = ? AND deleted_at IS NULL", id).execute
-- Easy to forget the filter!

-- After: Clear intent, can't forget
db.select_from ("documents").active_only.where ("folder_id = ?", id).execute
db.select_from ("folders").active_only.where ("parent_id = ?", id).execute

-- Before: OFFSET 10000 scans 10000 rows
db.select_from ("documents").limit (20).offset (10000).execute

-- After: Cursor jumps directly via index
page := db.paginator ("documents")
    .order_by ("id")
    .page_size (20)
    .after (cursor)  -- Instant seek, not scan

db.enable_query_monitor
load_documents_page
if attached db.query_monitor as mon and then mon.has_warnings then
    print (mon.report)  -- Shows repeated query patterns
end

Key Implementation Patterns

Document Service with Eager Loading

feature -- Document queries

    documents_in_folder (a_folder_id: INTEGER_64): SIMPLE_SQL_EAGER_RESULT
            -- Documents with tags and author, excludes deleted
        do
            Result := db.eager_loader
                .from_table ("documents")
                .columns ("id, name, version, created_by, created_at")
                .include ("author", "users", "created_by", "id")
                .include_many_to_many ("tags", "tags", "document_tags", "document_id", "tag_id")
                .where ("folder_id = ? AND deleted_at IS NULL")
                .order_by ("name")
                .execute
        end

Paginated Document List

feature -- Pagination

    recent_documents (a_cursor: detachable STRING_8): SIMPLE_SQL_PAGE
            -- Most recently modified documents, paginated
        local
            paginator: SIMPLE_SQL_PAGINATOR
        do
            paginator := db.paginator ("documents")
                .columns ("id, name, updated_at")
                .where ("deleted_at IS NULL")
                .order_by_desc ("updated_at")
                .page_size (25)

            if attached a_cursor then
                Result := paginator.after (a_cursor)
            else
                Result := paginator.first_page
            end
        end

Full-Text Search

feature -- Search

    search_documents (a_query: STRING_8): SIMPLE_SQL_RESULT
            -- FTS5 search with BM25 ranking
        do
            Result := db.query_with_args ("[
                SELECT d.id, d.name, snippet(documents_fts, 1, '', '', '...', 32) as excerpt,
                       bm25(documents_fts) as rank
                FROM documents_fts
                JOIN documents d ON d.id = documents_fts.rowid
                WHERE documents_fts MATCH ?
                  AND d.deleted_at IS NULL
                ORDER BY rank
                LIMIT 50
            ]", <<a_query>>)
        end

Test Coverage

The DMS includes 64 tests covering:

• CRUD operations on all entities
• Folder hierarchy and path calculations
• Document versioning
• Tag management (many-to-many)
• Full-text search with ranking
• Soft delete and restore
• Sharing permissions
• Audit trail verification
• N+1 detection stress tests
• Pagination performance tests

Source Code

Explore the DMS implementation:

• DMS_APP - Main application class
• DMS_DOCUMENT - Document entity
• DMS_FOLDER - Folder entity with hierarchy
• DMS_DOCUMENT_VERSION - Version tracking
• DMS_TAG - Tag entity
• DMS_AUDIT_LOG - Audit trail