Phase 1 Steps 1-2: Database Schema & Core DAOs for User Accounts #91

New issue

Closed

opened 2025-09-19 02:18:51 +00:00 by rwachters · 0 comments

rwachters commented 2025-09-19 02:18:51 +00:00 (Migrated from github.com)

• Copy link
• Copy Markdown

Overview

This issue covers the foundational database work for implementing user accounts and permissions in the chatbot application. This includes creating all necessary database tables using JetBrains Exposed ORM and implementing the core Data Access Objects (DAOs) required for user identity and basic ownership.

Phase 1 - Foundation: Authentication & Private User Space

Goal: Establish the core user system, allowing users to register, log in, and see only their own private chat sessions and groups.

Step 1: Database Schema Setup

Tasks

• Create comprehensive Exposed table definitions for all new user management tables
• Create association tables for ownership and access control
• Implement database migration scripts
• Create initial setup script for "All Users" group and admin user

New Tables to Implement

Core User Management Tables

• UsersTable - User accounts with credentials
• RolesTable - User roles (Admin, Standard User, etc.)
• PermissionsTable - Granular permissions
• RolePermissionsTable - Links roles to permissions
• UserRoleAssignmentsTable - Assigns roles to users
• UserSessionsTable - Active user sessions
• UserGroupsTable - User-defined groups (including special "All Users" group)
• UserGroupMembershipsTable - Links users to groups

Ownership Association Tables

• ChatSessionOwnersTable - Links chat sessions to owning users
• ChatGroupOwnersTable - Links chat groups to owning users
• LLMProviderOwnersTable - Links LLM providers to owning users
• LLMModelOwnersTable - Links LLM models to owning users
• ModelSettingsOwnersTable - Links model settings to owning users
• ApiSecretOwnersTable - Links API secrets to owning users

Access Association Tables

• LLMProviderAccessTable - Defines group access to providers
• LLMModelAccessTable - Defines group access to models
• ModelSettingsAccessTable - Defines group access to settings

Key Implementation Details

• Use JetBrains Exposed ORM for all table definitions
• Implement proper foreign key constraints with cascade options
• Create composite primary keys for association tables
• Add appropriate indexes for performance
• Store timestamps as epoch milliseconds (consistent with existing tables)
• Implement the special "All Users" group concept for public resources

Migration Requirements

• Create migration script to add all new tables
• Implement initial setup script that:
  1. Creates the special "All Users" group
  2. Creates the initial administrator user account
  3. Assigns Admin role to initial user
  4. Adds initial admin to "All Users" group

Step 2: Core Data Access Objects (DAOs)

Tasks

• Implement UserDao for user CRUD operations
• Implement UserSessionDao for session management
• Implement SessionOwnershipDao for chat session ownership
• Implement GroupOwnershipDao for chat group ownership

DAO Requirements

UserDao

• insertUser(username, passwordHash, email): User
• getUserById(id): User?
• getUserByUsername(username): User?
• updateUser(id, updates): Boolean
• deleteUser(id): Boolean

UserSessionDao

• createSession(userId, expiresAt): UserSession
• getSessionById(id): UserSession?
• updateLastAccessed(sessionId, timestamp): Boolean
• deleteSession(sessionId): Boolean
• deleteExpiredSessions(): Int

SessionOwnershipDao

• getAllSessionsForUser(userId): List<ChatSession>
• getSessionOwner(sessionId): User?
• setOwner(sessionId, userId): Boolean
• verifyOwnership(sessionId, userId): Boolean

GroupOwnershipDao

• getAllGroupsForUser(userId): List<ChatGroup>
• getGroupOwner(groupId): User?
• setOwner(groupId, userId): Boolean
• verifyOwnership(groupId, userId): Boolean

Technical Requirements

• Use Exposed DSL for all database operations
• Implement proper transaction handling
• Add comprehensive error handling
• Use JOIN operations for ownership queries
• Ensure type safety with Exposed's type-safe DSL
• Follow existing project patterns and conventions

File Locations

• Table definitions: server/src/main/kotlin/eu/torvian/chatbot/server/data/tables/
• DAO interfaces: server/src/main/kotlin/eu/torvian/chatbot/server/data/dao/
• DAO implementations: server/src/main/kotlin/eu/torvian/chatbot/server/data/dao/impl/
• Migration scripts: server/src/main/kotlin/eu/torvian/chatbot/server/data/migrations/

Acceptance Criteria

• All new Exposed table objects are properly defined with correct relationships
• Database migration successfully creates all new tables
• Initial setup script creates "All Users" group and admin user
• All core DAOs are implemented with full CRUD functionality
• Ownership DAOs properly filter data by user context
• All DAO operations use transactions appropriately
• Unit tests cover all DAO functionality
• No existing functionality is broken

Dependencies

• Exposed 0.61.0 (already in project)
• Existing database tables remain unchanged
• Existing DAO patterns and conventions

Notes

• This is foundational work - no UI changes in this phase
• Existing tables (ChatSessionTable, LLMProviderTable, etc.) remain structurally unchanged
• The "All Users" group is a special, non-deletable group for public resource sharing
• Focus on data integrity and proper relationship modeling
• Performance considerations for JOIN operations on ownership tables

Related

• Epic: NF.E1 - User Accounts and Permissions
• Next Phase: Phase 1 Step 3-4 (Authentication Services & API Endpoints)
• Documentation: docs/plans/user accounts/User Accounts feature.md

## Overview This issue covers the foundational database work for implementing user accounts and permissions in the chatbot application. This includes creating all necessary database tables using JetBrains Exposed ORM and implementing the core Data Access Objects (DAOs) required for user identity and basic ownership. ## Phase 1 - Foundation: Authentication & Private User Space **Goal:** Establish the core user system, allowing users to register, log in, and see only their own private chat sessions and groups. ## Step 1: Database Schema Setup ### Tasks - [ ] Create comprehensive Exposed table definitions for all new user management tables - [ ] Create association tables for ownership and access control - [ ] Implement database migration scripts - [ ] Create initial setup script for "All Users" group and admin user ### New Tables to Implement #### Core User Management Tables - `UsersTable` - User accounts with credentials - `RolesTable` - User roles (Admin, Standard User, etc.) - `PermissionsTable` - Granular permissions - `RolePermissionsTable` - Links roles to permissions - `UserRoleAssignmentsTable` - Assigns roles to users - `UserSessionsTable` - Active user sessions - `UserGroupsTable` - User-defined groups (including special "All Users" group) - `UserGroupMembershipsTable` - Links users to groups #### Ownership Association Tables - `ChatSessionOwnersTable` - Links chat sessions to owning users - `ChatGroupOwnersTable` - Links chat groups to owning users - `LLMProviderOwnersTable` - Links LLM providers to owning users - `LLMModelOwnersTable` - Links LLM models to owning users - `ModelSettingsOwnersTable` - Links model settings to owning users - `ApiSecretOwnersTable` - Links API secrets to owning users #### Access Association Tables - `LLMProviderAccessTable` - Defines group access to providers - `LLMModelAccessTable` - Defines group access to models - `ModelSettingsAccessTable` - Defines group access to settings ### Key Implementation Details - Use JetBrains Exposed ORM for all table definitions - Implement proper foreign key constraints with cascade options - Create composite primary keys for association tables - Add appropriate indexes for performance - Store timestamps as epoch milliseconds (consistent with existing tables) - Implement the special "All Users" group concept for public resources ### Migration Requirements - Create migration script to add all new tables - Implement initial setup script that: 1. Creates the special "All Users" group 2. Creates the initial administrator user account 3. Assigns Admin role to initial user 4. Adds initial admin to "All Users" group ## Step 2: Core Data Access Objects (DAOs) ### Tasks - [ ] Implement `UserDao` for user CRUD operations - [ ] Implement `UserSessionDao` for session management - [ ] Implement `SessionOwnershipDao` for chat session ownership - [ ] Implement `GroupOwnershipDao` for chat group ownership ### DAO Requirements #### UserDao - `insertUser(username, passwordHash, email): User` - `getUserById(id): User?` - `getUserByUsername(username): User?` - `updateUser(id, updates): Boolean` - `deleteUser(id): Boolean` #### UserSessionDao - `createSession(userId, expiresAt): UserSession` - `getSessionById(id): UserSession?` - `updateLastAccessed(sessionId, timestamp): Boolean` - `deleteSession(sessionId): Boolean` - `deleteExpiredSessions(): Int` #### SessionOwnershipDao - `getAllSessionsForUser(userId): List<ChatSession>` - `getSessionOwner(sessionId): User?` - `setOwner(sessionId, userId): Boolean` - `verifyOwnership(sessionId, userId): Boolean` #### GroupOwnershipDao - `getAllGroupsForUser(userId): List<ChatGroup>` - `getGroupOwner(groupId): User?` - `setOwner(groupId, userId): Boolean` - `verifyOwnership(groupId, userId): Boolean` ### Technical Requirements - Use Exposed DSL for all database operations - Implement proper transaction handling - Add comprehensive error handling - Use JOIN operations for ownership queries - Ensure type safety with Exposed's type-safe DSL - Follow existing project patterns and conventions ### File Locations - Table definitions: `server/src/main/kotlin/eu/torvian/chatbot/server/data/tables/` - DAO interfaces: `server/src/main/kotlin/eu/torvian/chatbot/server/data/dao/` - DAO implementations: `server/src/main/kotlin/eu/torvian/chatbot/server/data/dao/impl/` - Migration scripts: `server/src/main/kotlin/eu/torvian/chatbot/server/data/migrations/` ## Acceptance Criteria - [ ] All new Exposed table objects are properly defined with correct relationships - [ ] Database migration successfully creates all new tables - [ ] Initial setup script creates "All Users" group and admin user - [ ] All core DAOs are implemented with full CRUD functionality - [ ] Ownership DAOs properly filter data by user context - [ ] All DAO operations use transactions appropriately - [ ] Unit tests cover all DAO functionality - [ ] No existing functionality is broken ## Dependencies - Exposed 0.61.0 (already in project) - Existing database tables remain unchanged - Existing DAO patterns and conventions ## Notes - This is foundational work - no UI changes in this phase - Existing tables (`ChatSessionTable`, `LLMProviderTable`, etc.) remain structurally unchanged - The "All Users" group is a special, non-deletable group for public resource sharing - Focus on data integrity and proper relationship modeling - Performance considerations for JOIN operations on ownership tables ## Related - Epic: NF.E1 - User Accounts and Permissions - Next Phase: Phase 1 Step 3-4 (Authentication Services & API Endpoints) - Documentation: `docs/plans/user accounts/User Accounts feature.md`

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

master

test/credential-validator-fixture-umask

feat/credential-profile-agent-roles

fix/credential-secret-tree-removal

feat/terminal-session-credential-attach

feat/worker-terminal-session

feat/worker-command-uid-isolation

v0.11.0

v0.10.0

v0.9.0

v0.8.0

v0.7.0

v0.6.0

v0.5.1

v0.5.0

v0.4.0

v0.3.0

v0.2.0

v0.1.0

Labels

Clear labels   

Epic 5

  

MCP

  

P1

  

PR-0

  

PR-1

  

api

  

api-layer

  

architecture

  

area:backend

  

area:frontend

  

arrow

  

authentication

  

backend

  

bug

Something isn't working

  

chat

  

client

  

clipboard

  

common-module

  

compose

  

cross-cutting

  

cross-cutting-requirement

  

data-layer

  

data-models

  

database

  

desktop-client

  

documentation

Improvements or additions to documentation

  

duplicate

This issue or pull request already exists

  

enhancement

New feature or request

  

epic

  

epic-1

  

epic-2

  

epic-3

  

epic-4

  

epic-5

  

epic-6

  

epic-7

  

epic-llm-config

  

error-handling

  

feature

  

frontend

  

good first issue

Good for newcomers

  

help wanted

Extra attention is needed

  

implementation

  

infrastructure

  

installer

  

invalid

This doesn't seem right

  

kotlin

  

ktor

  

llm

  

medium-priority

  

message

  

multiplatform

  

needs-review

  

needs-triage

  

packaging

  

performance

  

phase-1

  

phase-2

  

phase-3

  

phase-4

  

phase-5

  

phase3

  

phase:3

  

priority: high

  

question

Further information is requested

  

refactor

  

refactoring

  

resource-sharing

  

security

  

server

  

server-module

  

services

  

session

  

session-management

  

settings

  

setup

  

shutdown

  

state

  

storage

  

streaming

  

sub-issue

  

tests

  

tooling

  

type:task

  

ui

  

ui/ux

  

user-accounts

  

user-management

  

ux

  

viewmodel

  

websocket

  

windows

  

windows-integration

  

wontfix

This will not be worked on

No labels

Epic 5

MCP

P1

PR-0

PR-1

api

api-layer

architecture

area:backend

area:frontend

arrow

authentication

backend

bug

chat

client

clipboard

common-module

compose

cross-cutting

cross-cutting-requirement

data-layer

data-models

database

desktop-client

documentation

duplicate

enhancement

epic

epic-1

epic-2

epic-3

epic-4

epic-5

epic-6

epic-7

epic-llm-config

error-handling

feature

frontend

good first issue

help wanted

implementation

infrastructure

installer

invalid

kotlin

ktor

llm

medium-priority

message

multiplatform

needs-review

needs-triage

packaging

performance

phase-1

phase-2

phase-3

phase-4

phase-5

phase3

phase:3

priority: high

question

refactor

refactoring

resource-sharing

security

server

server-module

services

session

session-management

settings

setup

shutdown

state

storage

streaming

sub-issue

tests

tooling

type:task

ui

ui/ux

user-accounts

user-management

ux

viewmodel

websocket

windows

windows-integration

wontfix

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

1 participant

Notifications

Subscribe

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

Torvian.eu/chatbot#91

No description provided.