Spry/plans/invercargill-sql-migration-plan.md

InvercargillSql Migration Implementation Plan

Overview

This document provides a step-by-step implementation plan for migrating the Spry Authentication module from Implexus (document store) to InvercargillSql (relational database). The migration preserves all public APIs while replacing the storage backend.

Reference Documents

• Analysis: plans/authentication-implexus-analysis.md
• Current UserService: src/Authentication/UserService.vala
• Current SessionService: src/Authentication/SessionService.vala

---

1. Repository Interface Design

1.1 UserRepository Interface

File: src/Authentication/Repositories/UserRepository.vala

using Invercargill.DataStructures;

namespace Spry.Authentication {

    /**
     * Repository interface for User persistence operations.
     * Abstracts the storage mechanism from the service layer.
     */
    public interface UserRepository : GLib.Object {

        // =========================================================================
        // Retrieval Operations
        // =========================================================================

        /**
         * Gets a user by their unique ID.
         *
         * @param id The user's unique identifier
         * @return The User, or null if not found
         * @throws Error on storage failure
         */
        public abstract async User? get_by_id_async(string id) throws Error;

        /**
         * Gets a user by their username.
         *
         * @param username The username to look up
         * @return The User, or null if not found
         * @throws Error on storage failure
         */
        public abstract async User? get_by_username_async(string username) throws Error;

        /**
         * Gets a user by their email address.
         *
         * @param email The email address to look up
         * @return The User, or null if not found
         * @throws Error on storage failure
         */
        public abstract async User? get_by_email_async(string email) throws Error;

        // =========================================================================
        // Mutation Operations
        // =========================================================================

        /**
         * Creates a new user.
         *
         * @param user The user to create (id should be pre-generated)
         * @return The created User
         * @throws UserError.DUPLICATE_USERNAME if username exists
         * @throws UserError.DUPLICATE_EMAIL if email exists
         * @throws Error on storage failure
         */
        public abstract async User create_async(User user) throws Error;

        /**
         * Updates an existing user.
         *
         * @param user The user to update
         * @throws UserError.USER_NOT_FOUND if user doesn't exist
         * @throws UserError.DUPLICATE_USERNAME if new username conflicts
         * @throws UserError.DUPLICATE_EMAIL if new email conflicts
         * @throws Error on storage failure
         */
        public abstract async void update_async(User user) throws Error;

        /**
         * Deletes a user by their unique ID.
         *
         * @param id The user's unique identifier
         * @throws UserError.USER_NOT_FOUND if user doesn't exist
         * @throws Error on storage failure
         */
        public abstract async void delete_async(string id) throws Error;

        // =========================================================================
        // Query Operations
        // =========================================================================

        /**
         * Checks if a username already exists.
         *
         * @param username The username to check
         * @return true if the username exists
         * @throws Error on storage failure
         */
        public abstract async bool username_exists_async(string username) throws Error;

        /**
         * Checks if an email already exists.
         *
         * @param email The email to check
         * @return true if the email exists
         * @throws Error on storage failure
         */
        public abstract async bool email_exists_async(string email) throws Error;

        /**
         * Lists users with pagination support.
         *
         * @param offset The number of users to skip
         * @param limit The maximum number of users to return
         * @return A Vector of users
         * @throws Error on storage failure
         */
        public abstract async Vector<User> list_async(int offset = 0, int limit = 100) throws Error;

        /**
         * Gets the total count of users.
         *
         * @return The number of users
         * @throws Error on storage failure
         */
        public abstract async int count_async() throws Error;

        // =========================================================================
        // Permission Operations
        // =========================================================================

        /**
         * Gets all permissions for a user.
         *
         * @param user_id The user's unique identifier
         * @return A Vector of permission strings
         * @throws Error on storage failure
         */
        public abstract async Vector<string> get_permissions_async(string user_id) throws Error;

        /**
         * Adds a permission to a user.
         *
         * @param user_id The user's unique identifier
         * @param permission The permission to add
         * @throws Error on storage failure
         */
        public abstract async void add_permission_async(string user_id, string permission) throws Error;

        /**
         * Removes a permission from a user.
         *
         * @param user_id The user's unique identifier
         * @param permission The permission to remove
         * @throws Error on storage failure
         */
        public abstract async void remove_permission_async(string user_id, string permission) throws Error;

        /**
         * Clears all permissions from a user.
         *
         * @param user_id The user's unique identifier
         * @throws Error on storage failure
         */
        public abstract async void clear_permissions_async(string user_id) throws Error;

        // =========================================================================
        // App Data Operations
        // =========================================================================

        /**
         * Gets app data for a user.
         *
         * @param user_id The user's unique identifier
         * @return A Dictionary of app data key-value pairs
         * @throws Error on storage failure
         */
        public abstract async Dictionary<string, string> get_app_data_async(string user_id) throws Error;

        /**
         * Sets an app data value for a user.
         *
         * @param user_id The user's unique identifier
         * @param key The app data key
         * @param value The app data value
         * @throws Error on storage failure
         */
        public abstract async void set_app_data_value_async(string user_id, string key, string value) throws Error;

        /**
         * Removes an app data key from a user.
         *
         * @param user_id The user's unique identifier
         * @param key The app data key to remove
         * @throws Error on storage failure
         */
        public abstract async void remove_app_data_value_async(string user_id, string key) throws Error;
    }
}

1.2 SessionRepository Interface

File: src/Authentication/Repositories/SessionRepository.vala

using Invercargill.DataStructures;

namespace Spry.Authentication {

    /**
     * Repository interface for Session persistence operations.
     * Abstracts the storage mechanism from the service layer.
     */
    public interface SessionRepository : GLib.Object {

        // =========================================================================
        // Retrieval Operations
        // =========================================================================

        /**
         * Gets a session by its unique ID.
         *
         * @param id The session's unique identifier
         * @return The Session, or null if not found
         * @throws Error on storage failure
         */
        public abstract async Session? get_by_id_async(string id) throws Error;

        /**
         * Gets all sessions for a user.
         *
         * @param user_id The user's unique identifier
         * @return A Vector of sessions (excluding expired)
         * @throws Error on storage failure
         */
        public abstract async Vector<Session> get_by_user_async(string user_id) throws Error;

        // =========================================================================
        // Mutation Operations
        // =========================================================================

        /**
         * Creates a new session.
         *
         * @param session The session to create (id should be pre-generated)
         * @return The created Session
         * @throws Error on storage failure
         */
        public abstract async Session create_async(Session session) throws Error;

        /**
         * Updates an existing session.
         *
         * @param session The session to update
         * @throws SessionError.SESSION_NOT_FOUND if session doesn't exist
         * @throws Error on storage failure
         */
        public abstract async void update_async(Session session) throws Error;

        /**
         * Deletes a session by its unique ID.
         *
         * @param id The session's unique identifier
         * @throws Error on storage failure
         */
        public abstract async void delete_async(string id) throws Error;

        /**
         * Deletes all sessions for a user.
         *
         * @param user_id The user's unique identifier
         * @throws Error on storage failure
         */
        public abstract async void delete_by_user_async(string user_id) throws Error;

        // =========================================================================
        // Cleanup Operations
        // =========================================================================

        /**
         * Removes all expired sessions from storage.
         *
         * @throws Error on storage failure
         */
        public abstract async void cleanup_expired_async() throws Error;
    }
}

---

2. SQL Schema Design

2.1 Users Table

CREATE TABLE users (
    id TEXT PRIMARY KEY NOT NULL,
    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
);

-- Index for username lookups (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Index for email lookups (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);

2.2 User Permissions Table

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)
);

-- Index for permission lookups by user
CREATE INDEX idx_user_permissions_user_id ON user_permissions(user_id);

2.3 User App Data Table

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)
);

-- Index for app data lookups by user
CREATE INDEX idx_user_app_data_user_id ON user_app_data(user_id);

2.4 Sessions Table

CREATE TABLE sessions (
    id TEXT PRIMARY KEY NOT NULL,
    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
);

-- Index for session lookups by user
CREATE INDEX idx_sessions_user_id ON sessions(user_id);

-- Index for expired session cleanup
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);

2.5 Schema Diagram

erDiagram
    users ||--o{ user_permissions : has
    users ||--o{ user_app_data : has
    users ||--o{ sessions : has

    users {
        TEXT id PK
        TEXT username UK
        TEXT email UK
        TEXT password_hash
        INTEGER is_active
        TEXT created_at
        TEXT updated_at
        TEXT last_login_at
    }

    user_permissions {
        INTEGER id PK
        TEXT user_id FK
        TEXT permission
    }

    user_app_data {
        INTEGER id PK
        TEXT user_id FK
        TEXT key UK
        TEXT value
    }

    sessions {
        TEXT id PK
        TEXT user_id FK
        TEXT created_at
        TEXT expires_at
        TEXT ip_address
        TEXT user_agent
    }

---

3. Implementation Details

3.1 SqlUserRepository Implementation

File: src/Authentication/Repositories/SqlUserRepository.vala

Constructor:

public class SqlUserRepository : GLib.Object, UserRepository {

    private InvercargillSql.Connection _connection;

    public SqlUserRepository(InvercargillSql.Connection connection) {
        _connection = connection;
    }
    
    // ... implementation
}

Key Implementation Patterns:

1. INSERT with Parameters:

   public async User create_async(User user) throws Error {
   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 _connection.create_command(sql)
       .with_parameter("id", user.id)
       .with_parameter("username", user.username)
       .with_parameter("email", user.email)
       .with_parameter("password_hash", user.password_hash)
       .with_parameter("is_active", 1)
       .with_parameter("created_at", user.created_at.format_iso8601())
       .execute_non_query_async();
       
   return user;
   }
   

2. SELECT with Properties Mapping:

   public async User? get_by_id_async(string id) throws Error {
   var sql = "SELECT * FROM users WHERE id = :id";
       
   var results = yield _connection.create_command(sql)
       .with_parameter("id", id)
       .execute_query_async();
       
   var row = results.first_or_default();
   if (row == null) return null;
       
   return user_from_properties(row);
   }
   
   private User user_from_properties(Properties props) {
   var user = new User();
   user.set_id(props.get("id").as<string>());
   user.set_username(props.get("username").as<string>());
   user.email = props.get("email").as<string>();
   user.password_hash = props.get("password_hash").as<string>());
       
   var created_str = props.get("created_at").as<string>();
   user.created_at = new DateTime.from_iso8601(created_str, new TimeZone.utc());
       
   // Handle nullable fields
   if (props.has("updated_at") && props.get("updated_at") != null) {
       var updated_str = props.get("updated_at").as<string>();
       user.updated_at = new DateTime.from_iso8601(updated_str, new TimeZone.utc());
   }
       
   return user;
   }
   

3. Permissions as Sub-query:

   public async User? get_by_id_async(string id) throws Error {
   // Get user with permissions in single query
   var sql = """
       SELECT u.*, GROUP_CONCAT(up.permission, ',') as permissions
       FROM users u
       LEFT JOIN user_permissions up ON u.id = up.user_id
       WHERE u.id = :id
       GROUP BY u.id
   """;
       
   // ... execute and parse
   }
   

3.2 SqlSessionRepository Implementation

File: src/Authentication/Repositories/SqlSessionRepository.vala

Constructor:

public class SqlSessionRepository : GLib.Object, SessionRepository {

    private InvercargillSql.Connection _connection;

    public SqlSessionRepository(InvercargillSql.Connection connection) {
        _connection = connection;
    }
    
    // ... implementation
}

Key Implementation Patterns:

1. Create Session:

   public async Session create_async(Session session) throws Error {
   var sql = """
       INSERT INTO sessions (id, user_id, created_at, expires_at, ip_address, user_agent)
       VALUES (:id, :user_id, :created_at, :expires_at, :ip_address, :user_agent)
   """;
       
   yield _connection.create_command(sql)
       .with_parameter("id", session.id)
       .with_parameter("user_id", session.user_id)
       .with_parameter("created_at", session.created_at.format_iso8601())
       .with_parameter("expires_at", session.expires_at.format_iso8601())
       .with_parameter("ip_address", session.ip_address ?? "")
       .with_parameter("user_agent", session.user_agent ?? "")
       .execute_non_query_async();
       
   return session;
   }
   

2. Get Sessions by User:

   public async Vector<Session> get_by_user_async(string user_id) throws Error {
   var sql = """
       SELECT * FROM sessions 
       WHERE user_id = :user_id AND expires_at > :now
       ORDER BY created_at DESC
   """;
       
   var now = new DateTime.now_utc().format_iso8601();
       
   var results = yield _connection.create_command(sql)
       .with_parameter("user_id", user_id)
       .with_parameter("now", now)
       .execute_query_async();
       
   var sessions = new Vector<Session>();
   foreach (var row in results) {
       sessions.add(session_from_properties(row));
   }
   return sessions;
   }
   

3. Cleanup Expired:

   public async void cleanup_expired_async() throws Error {
   var sql = "DELETE FROM sessions WHERE expires_at < :now";
   var now = new DateTime.now_utc().format_iso8601();
       
   yield _connection.create_command(sql)
       .with_parameter("now", now)
       .execute_non_query_async();
   }
   

3.3 Schema Migration Class

File: src/Authentication/Migrations/CreateAuthTables.vala

namespace Spry.Authentication.Migrations {

    /**
     * Creates the authentication database schema.
     * Run once during application initialization.
     */
    public class CreateAuthTables : GLib.Object {

        private InvercargillSql.Connection _connection;

        public CreateAuthTables(InvercargillSql.Connection connection) {
            _connection = connection;
        }

        /**
         * Creates all authentication tables if they don't exist.
         */
        public async void migrate_async() throws Error {
            // Users table
            yield _connection.create_command("""
                CREATE TABLE IF NOT EXISTS users (
                    id TEXT PRIMARY KEY NOT NULL,
                    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
                )
            """).execute_non_query_async();

            // User permissions table
            yield _connection.create_command("""
                CREATE TABLE IF NOT EXISTS 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)
                )
            """).execute_non_query_async();

            // User app data table
            yield _connection.create_command("""
                CREATE TABLE IF NOT EXISTS 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)
                )
            """).execute_non_query_async();

            // Sessions table
            yield _connection.create_command("""
                CREATE TABLE IF NOT EXISTS sessions (
                    id TEXT PRIMARY KEY NOT NULL,
                    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
                )
            """).execute_non_query_async();

            // Create indexes
            yield create_index_async("idx_users_username", "users(username)");
            yield create_index_async("idx_users_email", "users(email)");
            yield create_index_async("idx_user_permissions_user_id", "user_permissions(user_id)");
            yield create_index_async("idx_user_app_data_user_id", "user_app_data(user_id)");
            yield create_index_async("idx_sessions_user_id", "sessions(user_id)");
            yield create_index_async("idx_sessions_expires_at", "sessions(expires_at)");
        }

        private async void create_index_async(string name, string definition) throws Error {
            yield _connection.create_command(
                "CREATE INDEX IF NOT EXISTS %s ON %s".printf(name, definition)
            ).execute_non_query_async();
        }
    }
}

---

4. Code Migration Strategy

4.1 UserService Changes

Current Dependencies (to remove):

using Implexus.Core;
private Engine _engine = inject<Engine>();

New Dependencies (to add):

private UserRepository _user_repository = inject<UserRepository>();

Methods to Modify:

Method	Current Implementation	New Implementation
create_user_async()	Uses create_document_async(), set_entity_property_async()	Call _user_repository.create_async()
get_user_async()	Uses get_entity_or_null_async(), get_properties_async()	Call _user_repository.get_by_id_async()
get_user_by_username_async()	Uses catalogue path lookup	Call _user_repository.get_by_username_async()
get_user_by_email_async()	Uses catalogue path lookup	Call _user_repository.get_by_email_async()
update_user_async()	Uses set_entity_property_async()	Call _user_repository.update_async()
delete_user_async()	Uses delete_async() on entity	Call _user_repository.delete_async()
list_users_async()	Iterates container children	Call _user_repository.list_async()
username_exists_async()	Uses entity_exists_async() on catalogue path	Call _user_repository.username_exists_async()
email_exists_async()	Uses entity_exists_async() on catalogue path	Call _user_repository.email_exists_async()
user_count_async()	Counts document children	Call _user_repository.count_async()

Methods to Remove (Private Helpers):

• get_users_container_async() - No longer needed
• store_user_in_document_async() - No longer needed
• load_user_from_document_async() - No longer needed
• json_to_element() - No longer needed
• properties_to_json() - No longer needed
• element_to_json() - No longer needed

Refactored UserService (simplified):

using Invercargill.DataStructures;
using Inversion;

namespace Spry.Authentication {

    public class UserService : GLib.Object {

        private UserRepository _user_repository = inject<UserRepository>();
        private CryptographyProvider _crypto = inject<CryptographyProvider>();

        public async User create_user_async(string username, string email, string password) throws Error {
            // Validate username uniqueness
            if (yield _user_repository.username_exists_async(username)) {
                throw new UserError.DUPLICATE_USERNAME("Username already exists");
            }

            // Validate email uniqueness
            if (yield _user_repository.email_exists_async(email)) {
                throw new UserError.DUPLICATE_EMAIL("Email already exists");
            }

            // Hash password
            var password_hash = hash_password(password);
            if (password_hash == null) {
                throw new UserError.STORAGE_ERROR("Failed to hash password");
            }

            // Create user object
            var user = new User();
            user.set_id(generate_uuid());
            user.set_username(username);
            user.email = email;
            user.password_hash = (!)password_hash;
            user.created_at = new DateTime.now_utc();

            // Persist via repository
            return yield _user_repository.create_async(user);
        }

        public async User? get_user_async(string user_id) throws Error {
            return yield _user_repository.get_by_id_async(user_id);
        }

        public async User? get_user_by_username_async(string username) throws Error {
            return yield _user_repository.get_by_username_async(username);
        }

        public async User? get_user_by_email_async(string email) throws Error {
            return yield _user_repository.get_by_email_async(email);
        }

        public async void update_user_async(User user) throws Error {
            // Check for username/email conflicts before update
            var existing = yield _user_repository.get_by_id_async(user.id);
            if (existing == null) {
                throw new UserError.USER_NOT_FOUND("User not found");
            }

            if (existing.username != user.username) {
                if (yield _user_repository.username_exists_async(user.username)) {
                    throw new UserError.DUPLICATE_USERNAME("Username already exists");
                }
            }

            if (existing.email != user.email) {
                if (yield _user_repository.email_exists_async(user.email)) {
                    throw new UserError.DUPLICATE_EMAIL("Email already exists");
                }
            }

            user.updated_at = new DateTime.now_utc();
            yield _user_repository.update_async(user);
        }

        public async void delete_user_async(string user_id) throws Error {
            yield _user_repository.delete_async(user_id);
        }

        public async Vector<User> list_users_async(int offset = 0, int limit = 100) throws Error {
            return yield _user_repository.list_async(offset, limit);
        }

        public async bool username_exists_async(string username) throws Error {
            return yield _user_repository.username_exists_async(username);
        }

        public async bool email_exists_async(string email) throws Error {
            return yield _user_repository.email_exists_async(email);
        }

        public async int user_count_async() throws Error {
            return yield _user_repository.count_async();
        }

        // Password methods remain unchanged
        public string? hash_password(string password) {
            return Sodium.PasswordHashing.hash(password);
        }

        public bool verify_password(User user, string password) {
            return Sodium.PasswordHashing.check(user.password_hash, password);
        }

        public async void set_password_async(User user, string new_password) throws Error {
            var password_hash = hash_password(new_password);
            if (password_hash == null) {
                throw new UserError.STORAGE_ERROR("Failed to hash password");
            }
            user.password_hash = (!)password_hash;
            user.updated_at = new DateTime.now_utc();
            yield update_user_async(user);
        }

        public async User? authenticate_async(string username_or_email, string password) throws Error {
            User? user = yield get_user_by_username_async(username_or_email);
            if (user == null) {
                user = yield get_user_by_email_async(username_or_email);
            }
            if (user == null) return null;

            if (!verify_password(user, password)) return null;
            return user;
        }

        private string generate_uuid() {
            uint8[] bytes = new uint8[16];
            Sodium.Random.random_bytes(bytes);
            bytes[6] = (bytes[6] & 0x0f) | 0x40;
            bytes[8] = (bytes[8] & 0x3f) | 0x80;
            return "%02x%02x%02x%02x-%02x%02x-%02x%02x-%02x%02x-%02x%02x%02x%02x%02x%02x".printf(
                bytes[0], bytes[1], bytes[2], bytes[3],
                bytes[4], bytes[5], bytes[6], bytes[7],
                bytes[8], bytes[9], bytes[10], bytes[11],
                bytes[12], bytes[13], bytes[14], bytes[15]
            );
        }
    }
}

4.2 SessionService Changes

Current Dependencies (to remove):

using Implexus.Core;
private Engine _engine = inject<Engine>();

New Dependencies (to add):

private SessionRepository _session_repository = inject<SessionRepository>();

Methods to Modify:

Method	Current Implementation	New Implementation
create_session_async()	Creates document + updates user index	Call _session_repository.create_async()
get_session_async()	Uses get_entity_or_null_async()	Call _session_repository.get_by_id_async()
get_sessions_for_user_async()	Uses secondary index	Call _session_repository.get_by_user_async()
delete_session_async()	Deletes document + updates index	Call _session_repository.delete_async()
delete_all_sessions_for_user_async()	Iterates index + deletes	Call _session_repository.delete_by_user_async()
cleanup_expired_sessions_async()	Iterates all sessions	Call _session_repository.cleanup_expired_async()

Methods to Remove (Private Helpers):

• get_sessions_container_async() - No longer needed
• get_sessions_by_user_container_async() - No longer needed
• store_session_in_document_async() - No longer needed
• load_session_from_document_async() - No longer needed
• json_to_element() - No longer needed
• properties_to_json() - No longer needed
• element_to_json() - No longer needed
• add_session_to_user_index_async() - No longer needed
• remove_session_from_user_index_async() - No longer needed
• clear_user_sessions_index_async() - No longer needed
• get_session_ids_for_user_async() - No longer needed
• load_session_ids_from_document_async() - No longer needed
• store_session_ids_in_document_async() - No longer needed

4.3 PermissionService Changes

PermissionService already delegates to UserService, so minimal changes are needed. The update_user_async() call in PermissionService will work transparently with the repository-based UserService.

---

5. Build System Changes

5.1 Root meson.build Changes

File: meson.build

Add InvercargillSql dependency:

# Add after existing dependencies (around line 17)
invercargill_sql_dep = dependency('invercargill-sql-1')

Note: The implexus_dep can be removed from the root meson.build if no other modules use it. For this migration, keep it temporarily until full removal is confirmed.

5.2 src/meson.build Changes

File: src/meson.build

No changes needed - the main Spry library doesn't directly use Implexus.

5.3 src/Authentication/meson.build Changes

File: src/Authentication/meson.build

Current content:

authentication_sources = files(
    'User.vala',
    'Session.vala',
    'UserService.vala',
    'SessionService.vala',
    'PermissionService.vala',
    'UserIdentityProvider.vala',
    'Components/LoginFormComponent.vala',
    'Components/UserManagementComponent.vala',
    'Components/UserDetailsComponent.vala',
    'Components/NewUserComponent.vala',
    'AuthenticationMigration.vala'
)

libspry_authentication = static_library('spry-authentication',
    authentication_sources,
    dependencies: [spry_dep, spry_authorisation_dep, implexus_dep, sodium_deps, invercargill_dep, astralis_dep],
    include_directories: include_directories('..')
)

spry_authentication_inc = include_directories('.')
spry_authentication_dep = declare_dependency(
    link_with: libspry_authentication,
    include_directories: spry_authentication_inc,
    dependencies: [spry_dep, spry_authorisation_dep, implexus_dep]
)

New content:

authentication_sources = files(
    'User.vala',
    'Session.vala',
    'UserService.vala',
    'SessionService.vala',
    'PermissionService.vala',
    'UserIdentityProvider.vala',
    'Components/LoginFormComponent.vala',
    'Components/UserManagementComponent.vala',
    'Components/UserDetailsComponent.vala',
    'Components/NewUserComponent.vala',
    'Repositories/UserRepository.vala',
    'Repositories/SqlUserRepository.vala',
    'Repositories/SessionRepository.vala',
    'Repositories/SqlSessionRepository.vala',
    'Migrations/CreateAuthTables.vala'
)

libspry_authentication = static_library('spry-authentication',
    authentication_sources,
    dependencies: [spry_dep, spry_authorisation_dep, invercargill_sql_dep, sodium_deps, invercargill_dep, astralis_dep],
    include_directories: include_directories('..')
)

spry_authentication_inc = include_directories('.')
spry_authentication_dep = declare_dependency(
    link_with: libspry_authentication,
    include_directories: spry_authentication_inc,
    dependencies: [spry_dep, spry_authorisation_dep, invercargill_sql_dep]
)

Summary of changes:

1. Remove 'AuthenticationMigration.vala' from sources
2. Add new repository files to sources
3. Add new migration file to sources
4. Replace implexus_dep with invercargill_sql_dep in dependencies

---

6. Migration Path

6.1 Implementation Order

flowchart TB
    subgraph Phase 1 - Infrastructure
        A1[Add invercargill_sql_dep to meson.build]
        A2[Create Repositories directory]
        A3[Create Migrations directory]
    end
    
    subgraph Phase 2 - Repository Layer
        B1[Create UserRepository interface]
        B2[Create SessionRepository interface]
        B3[Implement SqlUserRepository]
        B4[Implement SqlSessionRepository]
        B5[Create CreateAuthTables migration]
    end
    
    subgraph Phase 3 - Service Migration
        C1[Update UserService to use UserRepository]
        C2[Update SessionService to use SessionRepository]
        C3[Remove Implexus imports and helpers]
    end
    
    subgraph Phase 4 - Build Updates
        D1[Update src/Authentication/meson.build]
        D2[Delete AuthenticationMigration.vala]
        D3[Remove implexus_dep from root meson.build]
    end
    
    subgraph Phase 5 - IoC Registration
        E1[Register Connection in IoC container]
        E2[Register UserRepository to SqlUserRepository]
        E3[Register SessionRepository to SqlSessionRepository]
        E4[Run migration at startup]
    end
    
    A1 --> A2 --> A3 --> B1
    B1 --> B2 --> B3 --> B4 --> B5
    B5 --> C1 --> C2 --> C3
    C3 --> D1 --> D2 --> D3
    D3 --> E1 --> E2 --> E3 --> E4

6.2 Step-by-Step Implementation Checklist

Phase 1: Infrastructure Setup

• Add invercargill_sql_dep = dependency('invercargill-sql-1') to root meson.build
• Create directory src/Authentication/Repositories/
• Create directory src/Authentication/Migrations/

Phase 2: Repository Interfaces

• Create src/Authentication/Repositories/UserRepository.vala with interface definition
• Create src/Authentication/Repositories/SessionRepository.vala with interface definition

Phase 3: Repository Implementations

• Create src/Authentication/Repositories/SqlUserRepository.vala
  • Implement get_by_id_async()
  • Implement get_by_username_async()
  • Implement get_by_email_async()
  • Implement create_async()
  • Implement update_async()
  • Implement delete_async()
  • Implement username_exists_async()
  • Implement email_exists_async()
  • Implement list_async()
  • Implement count_async()
  • Implement permission methods
  • Implement app data methods
• Create src/Authentication/Repositories/SqlSessionRepository.vala
  • Implement get_by_id_async()
  • Implement get_by_user_async()
  • Implement create_async()
  • Implement update_async()
  • Implement delete_async()
  • Implement delete_by_user_async()
  • Implement cleanup_expired_async()

Phase 4: Schema Migration

• Create src/Authentication/Migrations/CreateAuthTables.vala

Phase 5: Service Refactoring

• Update src/Authentication/UserService.vala:
  • Remove using Implexus.Core;
  • Replace Engine _engine with UserRepository _user_repository
  • Refactor all methods to use repository
  • Remove private helper methods for Implexus
• Update src/Authentication/SessionService.vala:
  • Remove using Implexus.Core;
  • Replace Engine _engine with SessionRepository _session_repository
  • Refactor all methods to use repository
  • Remove private helper methods for Implexus
  • Remove user index management methods

Phase 6: Build System Updates

• Update src/Authentication/meson.build:
  • Add new repository files to sources
  • Add new migration file to sources
  • Remove AuthenticationMigration.vala from sources
  • Replace implexus_dep with invercargill_sql_dep
• Delete src/Authentication/AuthenticationMigration.vala
• Optionally remove implexus_dep from root meson.build if no other modules use it

Phase 7: IoC Registration Updates

• Update application startup code to:
  • Create/open SQLite database connection
  • Register InvercargillSql.Connection as singleton in IoC container
  • Register UserRepository → SqlUserRepository in IoC container
  • Register SessionRepository → SqlSessionRepository in IoC container
  • Run CreateAuthTables.migrate_async() on first startup

6.3 IoC Registration Example

In application startup code (e.g., main application entry point):

using Inversion;
using InvercargillSql;

// Create and open database connection
var db_path = Path.build_filename(Environment.get_user_data_dir(), "spry", "authentication.db");
var connection = new Connection(db_path);
yield connection.open_async();

// Register in IoC container
var container = new Container();
container.register<Connection>(() => connection).as_singleton();
container.register<UserRepository, SqlUserRepository>().as_singleton();
container.register<SessionRepository, SqlSessionRepository>().as_singleton();
container.register<UserService>().as_singleton();
container.register<SessionService>().as_singleton();
container.register<PermissionService>().as_singleton();

// Run schema migration
var migration = new Migrations.CreateAuthTables(connection);
yield migration.migrate_async();

6.4 Data Migration Considerations

If there is existing production data in Implexus:

1. Create a data migration script that:

   • Reads all users from Implexus
   • Reads all sessions from Implexus
   • Inserts them into the SQL tables
   • Verifies data integrity

2. Migration script outline:

   public async void migrate_data_from_implexus_async(
   Engine implexus_engine,
   InvercargillSql.Connection sql_connection
   ) throws Error {
   // Migrate users
   var users_path = new EntityPath("/spry/authentication/users");
   var users_container = yield implexus_engine.get_entity_or_null_async(users_path);
       
   if (users_container != null) {
       var children = yield users_container.get_children_async();
       foreach (var child in children) {
           if (child.entity_type == EntityType.DOCUMENT) {
               var props = yield child.get_properties_async();
               // Convert to User object and insert into SQL
           }
       }
   }
       
   // Migrate sessions similarly
   }
   

6.5 Testing Strategy

1. Unit Tests for Repositories:

   • Test each repository method in isolation
   • Use in-memory SQLite database for fast tests
   • Test edge cases (null values, empty results, duplicates)

2. Integration Tests for Services:

   • Test UserService with SqlUserRepository
   • Test SessionService with SqlSessionRepository
   • Verify public API remains unchanged

3. Migration Tests:

   • Test schema creation on fresh database
   • Test idempotency (running migration twice)
   • Test data migration from Implexus (if applicable)

4. Performance Tests:

   • Benchmark user lookup by username
   • Benchmark session listing by user
   • Compare with Implexus performance

---

7. Files Summary

7.1 New Files to Create

File	Purpose
src/Authentication/Repositories/UserRepository.vala	User repository interface
src/Authentication/Repositories/SessionRepository.vala	Session repository interface
src/Authentication/Repositories/SqlUserRepository.vala	SQLite user repository implementation
src/Authentication/Repositories/SqlSessionRepository.vala	SQLite session repository implementation
src/Authentication/Migrations/CreateAuthTables.vala	SQL schema migration

7.2 Files to Modify

File	Changes
meson.build	Add invercargill_sql_dep dependency
src/Authentication/meson.build	Update sources and dependencies
src/Authentication/UserService.vala	Replace Implexus with repository
src/Authentication/SessionService.vala	Replace Implexus with repository

7.3 Files to Delete

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

---

8. Risk Mitigation

Risk	Mitigation
Breaking existing API	Repository pattern isolates changes; public service APIs unchanged
Data loss during migration	Create backup before migration; implement data migration script
Performance regression	SQL indexes replicate catalogue performance; benchmark critical paths
Async behavior differences	Both use async/await; InvercargillSql uses thread-based async (transparent)
Missing features in InvercargillSql	Verify API coverage before starting; implement workarounds if needed

---

9. Verification Checklist

After implementation, verify:

• All existing tests pass
• User CRUD operations work correctly
• Session CRUD operations work correctly
• Username/email uniqueness is enforced
• Permissions are correctly stored and retrieved
• App data is correctly stored and retrieved
• Expired sessions are cleaned up
• Foreign key constraints work (deleting user deletes sessions)
• Build completes without errors
• No Implexus references remain in Authentication module