# Authentication System Implexus Usage Analysis

## Executive Summary

This document provides a comprehensive analysis of the current authentication system's usage of the Implexus database and maps out the migration path to InvercargillSql. The authentication system uses Implexus as a document store with catalogue-based indexing for user and session management.

## 1. Implexus Usage Map

### 1.1 Files with Direct Implexus Dependencies

| File | Implexus Usage | Complexity |
|------|----------------|------------|
| [`UserService.vala`](../src/Authentication/UserService.vala) | Heavy - all CRUD operations | High |
| [`SessionService.vala`](../src/Authentication/SessionService.vala) | Heavy - all CRUD operations | High |
| [`AuthenticationMigration.vala`](../src/Authentication/AuthenticationMigration.vala) | Medium - schema setup | Medium |
| [`PermissionService.vala`](../src/Authentication/PermissionService.vala) | None - delegates to UserService | N/A |
| [`User.vala`](../src/Authentication/User.vala) | None - data model only | N/A |
| [`Session.vala`](../src/Authentication/Session.vala) | None - data model only | N/A |
| [`UserIdentityProvider.vala`](../src/Authentication/UserIdentityProvider.vala) | None - adapter only | N/A |
| `Components/*.vala` | None - use inject<> for services | N/A |

### 1.2 Implexus Import Patterns

```vala
// Standard imports in files using Implexus
using Implexus.Core;                    // Engine, EntityPath, Container, Document
using Invercargill;                     // Element, Properties
using Invercargill.DataStructures;      // Vector, Dictionary, Series
```

### 1.3 Storage Paths and Structure

```mermaid
graph TB
    subgraph Implexus Storage Structure
        Root["/spry"]
        Auth["/spry/authentication"]
        Users["/spry/authentication/users"]
        Sessions["/spry/authentication/sessions"]
        SessionsByUser["/spry/authentication/sessions_by_user"]
    end
    
    Root --> Auth
    Auth --> Users
    Auth --> Sessions
    Auth --> SessionsByUser
    
    subgraph User Document
        UserDoc["Document: {user_id}"]
        UserProps["Properties: username, email, password_hash, etc."]
    end
    
    subgraph Session Document
        SessionDoc["Document: {session_id}"]
        SessionProps["Properties: user_id, created_at, expires_at, etc."]
    end
    
    Users --> UserDoc
    UserDoc --> UserProps
    
    Sessions --> SessionDoc
    SessionDoc --> SessionProps
```

### 1.4 Catalogue Configuration

| Catalogue | Path | Indexed Property | Purpose |
|-----------|------|------------------|---------|
| `by_username` | `/spry/authentication/users` | `username` | Unique username lookup |
| `by_email` | `/spry/authentication/users` | `email` | Unique email lookup |

---

## 2. Data Models and Schemas

### 2.1 User Model

**File:** [`src/Authentication/User.vala`](../src/Authentication/User.vala)

```vala
public class Spry.Authentication.User : Object, Authorisation.Identity {
    public string id { get; set; }
    public string username { get; set; }
    public string email { get; set; }
    public string password_hash { get; set; }
    public Vector<string> permissions { get; set; }
    public Dictionary<string, string> app_data { get; set; }
    public bool is_active { get; set; }
    public DateTime created_at { get; set; }
    public DateTime? updated_at { get; set; }
    public DateTime? last_login_at { get; set; }
}
```

**Implexus Property Mapping:**

| Property | Implexus Storage | Type |
|----------|------------------|------|
| `id` | Document ID | `string` |
| `username` | Property | `string` |
| `email` | Property | `string` |
| `password_hash` | Property | `string` |
| `permissions` | Property (JSON array) | `Vector<string>` |
| `app_data` | Property (JSON object) | `Dictionary<string, string>` |
| `is_active` | Property | `bool` |
| `created_at` | Property (ISO 8601) | `DateTime` |
| `updated_at` | Property (ISO 8601) | `DateTime?` |
| `last_login_at` | Property (ISO 8601) | `DateTime?` |

### 2.2 Session Model

**File:** [`src/Authentication/Session.vala`](../src/Authentication/Session.vala)

```vala
public class Spry.Authentication.Session : Object {
    public string id { get; set; }
    public string user_id { get; set; }
    public DateTime created_at { get; set; }
    public DateTime expires_at { get; set; }
    public string? ip_address { get; set; }
    public string? user_agent { get; set; }
}
```

**Implexus Property Mapping:**

| Property | Implexus Storage | Type |
|----------|------------------|------|
| `id` | Document ID | `string` |
| `user_id` | Property | `string` |
| `created_at` | Property (ISO 8601) | `DateTime` |
| `expires_at` | Property (ISO 8601) | `DateTime` |
| `ip_address` | Property | `string?` |
| `user_agent` | Property | `string?` |

---

## 3. Implexus API Usage Patterns

### 3.1 UserService Implexus Operations

**File:** [`src/Authentication/UserService.vala`](../src/Authentication/UserService.vala)

#### Create User
```vala
// Current Implexus pattern
var user_path = new EntityPath("/spry/authentication/users");
var user_doc = yield engine.create_document_async(user_path);

// Set properties
yield engine.set_entity_property_async(user_doc.path, "username", new NativeElement<string>(username));
yield engine.set_entity_property_async(user_doc.path, "email", new NativeElement<string>(email));
yield engine.set_entity_property_async(user_doc.path, "password_hash", new NativeElement<string>(hash));
// ... more properties
```

#### Read User
```vala
// Current Implexus pattern
var user_path = new EntityPath("/spry/authentication/users/%s".printf(id));
var user_doc = yield engine.get_entity_or_null_async(user_path);
if (user_doc == null) return null;

var props = yield engine.get_properties_async(user_doc.path);
var user = new User();
user.id = user_doc.id;
user.username = props.get("username")?.as_string_or_null();
// ... more properties
```

#### Update User
```vala
// Current Implexus pattern - property by property updates
yield engine.set_entity_property_async(user_path, "email", new NativeElement<string>(email));
yield engine.set_entity_property_async(user_path, "updated_at", new NativeElement<string>(now));
```

#### Delete User
```vala
// Current Implexus pattern
var user_path = new EntityPath("/spry/authentication/users/%s".printf(id));
yield engine.delete_entity_async(user_path);
```

#### Query by Username (Catalogue)
```vala
// Current Implexus pattern
var catalogue = yield engine.get_catalogue_async("/spry/authentication/users/by_username");
var entry = catalogue.get(username);
if (entry != null) {
    var user_id = entry.value.as_string_or_null();
    // Then fetch user by ID
}
```

### 3.2 SessionService Implexus Operations

**File:** [`src/Authentication/SessionService.vala`](../src/Authentication/SessionService.vala)

#### Create Session
```vala
// Current Implexus pattern
var session_path = new EntityPath("/spry/authentication/sessions");
var session_doc = yield engine.create_document_async(session_path);

// Set properties
yield engine.set_entity_property_async(session_doc.path, "user_id", new NativeElement<string>(user_id));
yield engine.set_entity_property_async(session_doc.path, "created_at", new NativeElement<string>(created_at));
// ... more properties

// Also update sessions_by_user index
var index_path = new EntityPath("/spry/authentication/sessions_by_user/%s".printf(user_id));
// ... index management
```

#### Get Sessions by User
```vala
// Current Implexus pattern - uses secondary index
var index_path = new EntityPath("/spry/authentication/sessions_by_user/%s".printf(user_id));
var index_doc = yield engine.get_entity_or_null_async(index_path);
// Parse session IDs from index
```

### 3.3 Migration Implexus Operations

**File:** [`src/Authentication/AuthenticationMigration.vala`](../src/Authentication/AuthenticationMigration.vala)

```vala
public class Spry.Authentication.AuthenticationMigration : Implexus.Migrations.Migration {
    public override async void up_async(Engine engine) throws Error {
        // Create containers
        var users_path = new EntityPath("/spry/authentication/users");
        yield engine.create_container_async(users_path);
        
        var sessions_path = new EntityPath("/spry/authentication/sessions");
        yield engine.create_container_async(sessions_path);
        
        // Create catalogues for unique constraints
        yield engine.create_catalogue_async(users_path, "by_username", "username");
        yield engine.create_catalogue_async(users_path, "by_email", "email");
    }
}
```

---

## 4. Key Interfaces to Maintain

### 4.1 Public Service Interfaces

These interfaces must be preserved during migration:

#### UserService Public API
```vala
public async User? get_user_by_id_async(string id)
public async User? get_user_by_username_async(string username)
public async User? get_user_by_email_async(string email)
public async User create_user_async(string username, string email, string password)
public async User update_user_async(User user)
public async void delete_user_async(string id)
public async bool validate_credentials_async(string username, string password)
public async bool username_exists_async(string username)
public async bool email_exists_async(string email)
```

#### SessionService Public API
```vala
public async Session create_session_async(string user_id, string? ip_address, string? user_agent)
public async Session? get_session_async(string session_id)
public async void delete_session_async(string session_id)
public async void delete_user_sessions_async(string user_id)
public async Vector<Session> get_user_sessions_async(string user_id)
public async bool validate_session_async(string session_id)
public async Session? refresh_session_async(string session_id)
```

#### PermissionService Public API
```vala
public async void grant_permission_async(string user_id, string permission)
public async void revoke_permission_async(string user_id, string permission)
public async bool has_permission_async(string user_id, string permission)
public async Vector<string> get_user_permissions_async(string user_id)
```

### 4.2 Dependency Injection Contracts

```vala
// Inversion IoC registration pattern
container.register<UserService>().as_singleton();
container.register<SessionService>().as_singleton();
container.register<PermissionService>().as_singleton();
container.register<UserIdentityProvider>().as_singleton();
```

---

## 5. Implexus vs InvercargillSql Comparison

### 5.1 Architectural Differences

```mermaid
graph LR
    subgraph Implexus - Document Store
        I1[Engine]
        I2[Container]
        I3[Document]
        I4[Properties]
        I5[Catalogue]
    end
    
    subgraph InvercargillSql - SQL Database
        S1[Connection]
        S2[Command]
        S3[Table/Row]
        S4[Properties]
        S5[Index]
    end
    
    I1 --> I2
    I2 --> I3
    I3 --> I4
    I2 --> I5
    
    S1 --> S2
    S2 --> S3
    S3 --> S4
    S1 --> S5
```

### 5.2 Feature Comparison

| Feature | Implexus | InvercargillSql |
|---------|----------|-----------------|
| **Data Model** | Hierarchical document store | Relational tables |
| **Schema** | Schemaless properties | Fixed schema with migrations |
| **Indexing** | Catalogues (auto-maintained) | SQL indexes (manual) |
| **Querying** | Path-based + catalogues | SQL queries |
| **Transactions** | Not explicit | Full transaction support |
| **Async** | Native async API | Thread-based async |
| **Result Type** | `Properties` | `Enumerable<Properties>` |
| **Parameter Binding** | N/A | Fluent `with_parameter<T>()` |
| **Relationships** | Manual (via paths) | Foreign keys |

### 5.3 API Mapping

| Operation | Implexus | InvercargillSql |
|-----------|----------|-----------------|
| **Connect** | `Engine` construction | `ConnectionFactory.create()` + `open()` |
| **Create** | `create_document_async()` | `INSERT` via `execute_non_query()` |
| **Read** | `get_entity_or_null_async()` + `get_properties_async()` | `SELECT` via `execute_query()` |
| **Update** | `set_entity_property_async()` | `UPDATE` via `execute_non_query()` |
| **Delete** | `delete_entity_async()` | `DELETE` via `execute_non_query()` |
| **Query** | Catalogue lookup | `SELECT WHERE` via `execute_query()` |
| **Transaction** | N/A | `begin_transaction()` + `commit()`/`rollback()` |

### 5.4 Code Pattern Comparison

#### Create Operation

**Implexus (Current):**
```vala
var path = new EntityPath("/spry/authentication/users");
var doc = yield engine.create_document_async(path);
yield engine.set_entity_property_async(doc.path, "username", new NativeElement<string>(username));
yield engine.set_entity_property_async(doc.path, "email", new NativeElement<string>(email));
// ... more properties
return doc.id;
```

**InvercargillSql (Target):**
```vala
var sql = """
    INSERT INTO users (id, username, email, password_hash, is_active, created_at)
    VALUES (:id, :username, :email, :password_hash, :is_active, :created_at)
""";
yield conn.create_command(sql)
    .with_parameter("id", generate_uuid())
    .with_parameter("username", username)
    .with_parameter("email", email)
    .with_parameter("password_hash", hash)
    .with_parameter("is_active", true)
    .with_parameter("created_at", new DateTime.now_utc().format_iso8601())
    .execute_non_query_async();
return conn.last_insert_rowid.to_string();
```

#### Read Operation

**Implexus (Current):**
```vala
var path = new EntityPath("/spry/authentication/users/%s".printf(id));
var doc = yield engine.get_entity_or_null_async(path);
if (doc == null) return null;
var props = yield engine.get_properties_async(doc.path);
return user_from_properties(props);
```

**InvercargillSql (Target):**
```vala
var sql = "SELECT * FROM users WHERE id = :id";
var results = yield conn.create_command(sql)
    .with_parameter("id", id)
    .execute_query_async();
var row = results.first_or_default();
if (row == null) return null;
return user_from_row(row);
```

#### Query by Index

**Implexus (Current):**
```vala
var catalogue = yield engine.get_catalogue_async("/spry/authentication/users/by_username");
var entry = catalogue.get(username);
if (entry == null) return null;
return yield get_user_by_id_async(entry.value.as_string_or_null());
```

**InvercargillSql (Target):**
```vala
var sql = "SELECT * FROM users WHERE username = :username";
var results = yield conn.create_command(sql)
    .with_parameter("username", username)
    .execute_query_async();
var row = results.first_or_default();
if (row == null) return null;
return user_from_row(row);
```

---

## 6. Migration Strategy Recommendations

### 6.1 Proposed SQL Schema

```sql
-- Users table
CREATE TABLE users (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT,
    last_login_at TEXT
);

-- User permissions (normalized)
CREATE TABLE user_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    permission TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(user_id, permission)
);

-- User app data (key-value store)
CREATE TABLE user_app_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    key TEXT NOT NULL,
    value TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(user_id, key)
);

-- Sessions table
CREATE TABLE sessions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    ip_address TEXT,
    user_agent TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Indexes for common queries
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
```

### 6.2 Migration Phases

```mermaid
flowchart TB
    subgraph Phase 1 - Infrastructure
        A1[Create SQL schema migration]
        A2[Create ConnectionProvider]
        A3[Update meson.build dependencies]
    end
    
    subgraph Phase 2 - Data Access Layer
        B1[Create UserRepository interface]
        B2[Implement SqlUserRepository]
        B3[Create SessionRepository interface]
        B4[Implement SqlSessionRepository]
    end
    
    subgraph Phase 3 - Service Migration
        C1[Refactor UserService to use repository]
        C2[Refactor SessionService to use repository]
        C3[Update PermissionService]
    end
    
    subgraph Phase 4 - Cleanup
        D1[Remove Implexus dependency]
        D2[Delete AuthenticationMigration]
        D3[Update documentation]
    end
    
    A1 --> A2 --> A3 --> B1
    B1 --> B2 --> B3 --> B4
    B4 --> C1 --> C2 --> C3
    C3 --> D1 --> D2 --> D3
```

### 6.3 Recommended Repository Pattern

To minimize impact on existing code and allow for future database changes, implement a repository pattern:

```vala
// Abstract repository interfaces
public interface UserRepository : Object {
    public abstract async User? get_by_id_async(string id);
    public abstract async User? get_by_username_async(string username);
    public abstract async User? get_by_email_async(string email);
    public abstract async User create_async(User user);
    public abstract async User update_async(User user);
    public abstract async void delete_async(string id);
    public abstract async bool username_exists_async(string username);
    public abstract async bool email_exists_async(string email);
}

public interface SessionRepository : Object {
    public abstract async Session create_async(Session session);
    public abstract async Session? get_by_id_async(string id);
    public abstract async void delete_async(string id);
    public abstract async void delete_by_user_async(string user_id);
    public abstract async Vector<Session> get_by_user_async(string user_id);
}
```

### 6.4 Key Migration Considerations

1. **Data Type Mapping:**
   - `DateTime` → ISO 8601 string storage
   - `Vector<string>` (permissions) → Separate table with foreign key
   - `Dictionary<string, string>` (app_data) → Separate key-value table

2. **Catalogue Replacement:**
   - `by_username` catalogue → `UNIQUE` constraint + index
   - `by_email` catalogue → `UNIQUE` constraint + index
   - `sessions_by_user` index → Foreign key + index on `user_id`

3. **Transaction Support:**
   - InvercargillSql provides explicit transactions
   - Use transactions for multi-step operations (e.g., create user + permissions)

4. **Async Patterns:**
   - Both use async/await
   - InvercargillSql uses thread-based async (transparent to caller)

5. **Connection Management:**
   - Use singleton Connection injected via IoC
   - Consider connection pooling for future scalability

---

## 7. Files Requiring Modification

### 7.1 Files to Modify

| File | Changes Required |
|------|------------------|
| `src/Authentication/UserService.vala` | Replace Implexus with repository |
| `src/Authentication/SessionService.vala` | Replace Implexus with repository |
| `src/Authentication/PermissionService.vala` | May need updates if directly accessing user permissions |
| `src/Authentication/meson.build` | Replace `implexus_dep` with `invercargill_sql_dep` |
| `meson.build` (root) | Update dependency declaration |

### 7.2 Files to Create

| File | Purpose |
|------|---------|
| `src/Authentication/Repositories/UserRepository.vala` | Abstract user repository interface |
| `src/Authentication/Repositories/SqlUserRepository.vala` | SQLite implementation |
| `src/Authentication/Repositories/SessionRepository.vala` | Abstract session repository interface |
| `src/Authentication/Repositories/SqlSessionRepository.vala` | SQLite implementation |
| `src/Authentication/Migrations/CreateAuthTables.vala` | SQL schema migration |

### 7.3 Files to Delete

| File | Reason |
|------|--------|
| `src/Authentication/AuthenticationMigration.vala` | Replaced by SQL migration |

---

## 8. Dependency Changes

### 8.1 Current Dependencies (meson.build)

```meson
# src/Authentication/meson.build
dependencies: [spry_dep, spry_authorisation_dep, implexus_dep, sodium_deps, invercargill_dep, astralis_dep]
```

### 8.2 Proposed Dependencies

```meson
# src/Authentication/meson.build
dependencies: [spry_dep, spry_authorisation_dep, invercargill_sql_dep, sodium_deps, invercargill_dep, astralis_dep]
```

### 8.3 Root meson.build Changes

```meson
# Current
implexus_dep = dependency('implexus-0.1')

# Proposed
invercargill_sql_dep = dependency('invercargill-sql-1')
```

---

## 9. Risk Assessment

| Risk | Impact | Mitigation |
|------|--------|------------|
| Data loss during migration | High | Implement data migration script, backup strategy |
| Breaking existing API | High | Maintain service interfaces, use repository pattern |
| Performance regression | Medium | Benchmark critical paths, optimize queries |
| Async behavior differences | Low | Both use async/await patterns consistently |
| Transaction semantics | Low | InvercargillSql provides more robust transactions |

---

## 10. Summary

The authentication system's Implexus usage is concentrated in two main service classes: `UserService` and `SessionService`. The migration to InvercargillSql is straightforward from an API perspective since both libraries:

1. Use the `Properties` interface for data representation
2. Support async operations
3. Are part of the Invercargill ecosystem

The main differences are:

1. **Query paradigm**: Path-based document lookup → SQL queries
2. **Indexing**: Catalogues → SQL indexes with UNIQUE constraints
3. **Transactions**: Implicit → Explicit with commit/rollback
4. **Schema**: Schemaless → Fixed schema with migrations

Implementing a repository pattern will isolate the database implementation details and allow the services to remain largely unchanged in their public API.