[E7.S4] Initialize SQLite Database & Exposed Schema #46

New issue

Closed

opened 2025-06-03 21:04:16 +00:00 by rwachters · 0 comments

rwachters commented

2025-06-03 21:04:16 +00:00

(Migrated from github.com)

Description

As a developer, I need the application to connect to the SQLite database file on startup and ensure all necessary tables, indexes, and foreign key constraints are created or updated using Exposed, so that data persistence works reliably, including support for message threading relationships.

Acceptance Criteria

Database connection logic using Exposed is implemented within the server module's data.exposed package.
The database file (chat.db) is created in a user-specific, platform-appropriate location if it doesn't exist.
Exposed Table objects (ChatSessions, ChatMessages, LLMModels, ModelSettings) are defined in the server module's data.models package, reflecting the schema requirements.
The ChatMessages Exposed Table definition includes a column for parentMessageId (Long, nullable, treated as a foreign key referencing ChatMessages.id) and a column for childrenMessageIds (likely stored as a serialized data type like JSON or comma-separated string).
Database creation or migration logic using SchemaUtils.create (or similar Exposed migration features) runs on startup to create tables if they are missing.
Foreign key constraints are defined using Exposed references: ChatMessage.sessionId referencing ChatSession.id with ReferenceOption.CASCADE for onDelete. Optionally, ChatMessage.parentMessageId referencing ChatMessage.id could be defined as a self-referencing foreign key with ReferenceOption.SET_NULL on delete for the orphan strategy, but for V1.1 the orphan logic is handled at the Service/DAO layer.
Indices are defined in Exposed Table objects for frequently queried columns: ChatMessage.sessionId, ChatMessage.parentMessageId, ModelSettings.modelId, ChatSession.groupId.
The database connection is established and ready for use by Exposed DAO implementations.
(Cross-cutting Requirement E7.S6): Database connection and schema operations are performed asynchronously using Coroutines if they could be blocking.

Implementation Details

This story involves setting up the SQLite database connection and defining the schema using Exposed ORM. Key considerations include:

Use proper Kotlin Coroutines for async database operations
Define appropriate Exposed table objects that match the shared DTO models
Implement proper threading relationship support in the schema
Set up appropriate indices for query optimization
Ensure database file is created in a user-appropriate location

Dependencies

PR #42 (Initial Multi-Module Project Setup) must be completed first
PR #44 (Common Module Data Models) should be completed to ensure schema alignment with shared DTOs

Parent Epic

Epic 7: Application Core Framework & Windows 11 Integration

E7.S5 - Implement Layered Architecture (this implements the data layer)
E7.S6 - Async Operations & Thread Management (cross-cutting requirement)

## Description As a developer, I need the application to connect to the SQLite database file on startup and ensure all necessary tables, indexes, and foreign key constraints are created or updated using Exposed, so that data persistence works reliably, including support for message threading relationships. ## Acceptance Criteria - [ ] Database connection logic using Exposed is implemented within the `server` module's `data.exposed` package. - [ ] The database file (`chat.db`) is created in a user-specific, platform-appropriate location if it doesn't exist. - [ ] Exposed Table objects (`ChatSessions`, `ChatMessages`, `LLMModels`, `ModelSettings`) are defined in the `server` module's `data.models` package, reflecting the schema requirements. - [ ] The `ChatMessages` Exposed Table definition includes a column for `parentMessageId` (Long, nullable, treated as a foreign key referencing `ChatMessages.id`) and a column for `childrenMessageIds` (likely stored as a serialized data type like JSON or comma-separated string). - [ ] Database creation or migration logic using `SchemaUtils.create` (or similar Exposed migration features) runs on startup to create tables if they are missing. - [ ] Foreign key constraints are defined using Exposed references: `ChatMessage.sessionId` referencing `ChatSession.id` with `ReferenceOption.CASCADE` for `onDelete`. Optionally, `ChatMessage.parentMessageId` referencing `ChatMessage.id` could be defined as a self-referencing foreign key with `ReferenceOption.SET_NULL` on delete for the orphan strategy, but for V1.1 the orphan logic is handled at the Service/DAO layer. - [ ] Indices are defined in Exposed Table objects for frequently queried columns: `ChatMessage.sessionId`, `ChatMessage.parentMessageId`, `ModelSettings.modelId`, `ChatSession.groupId`. - [ ] The database connection is established and ready for use by Exposed DAO implementations. - [ ] (Cross-cutting Requirement E7.S6): Database connection and schema operations are performed asynchronously using Coroutines if they could be blocking. ## Implementation Details This story involves setting up the SQLite database connection and defining the schema using Exposed ORM. Key considerations include: 1. Use proper Kotlin Coroutines for async database operations 2. Define appropriate Exposed table objects that match the shared DTO models 3. Implement proper threading relationship support in the schema 4. Set up appropriate indices for query optimization 5. Ensure database file is created in a user-appropriate location ## Dependencies - PR #42 (Initial Multi-Module Project Setup) must be completed first - PR #44 (Common Module Data Models) should be completed to ensure schema alignment with shared DTOs ## Parent Epic Epic 7: Application Core Framework & Windows 11 Integration ## Related Stories - E7.S5 - Implement Layered Architecture (this implements the data layer) - E7.S6 - Async Operations & Thread Management (cross-cutting requirement)