[PROPOSAL] Migration to Per Instance Architecture

[umutsevdi]

Migration to Per Instance Architecture

Moved here from StreamPets/twitch-bot

This document outlines the proposal for migrating the application to a
per-user-based monolithic architecture.

Overview

In this architecture, each application instance will manage the necessary
information for a single (or a few) streamers. Instances will be maintained
by an external orchestrator by providing streamer-specific information as
arguments.

The application manages Twitch Integration, SQL Database and the back-end
service.

Advantages of the Architecture

• Resiliency: If one instance crashes, the rest of the streamers will not be
  impacted.
• Scalability: Streamers with higher demands can be scaled individually.
• Performance: Instead of a central database, every service holds their local
  SQL Lite database.
• Self-Hosting Possibility: A single instance can be
  deployed outside the cluster due to its nature.

Implementation

• Twitch Bot Python server will be rewritten as a module under the back-end
  server.

• As a result, client-server calls will be refactored to function calls.

• The current database will be migrated to SQLite while
  adjusting schemes to the new architecture. (Such as
  removal of channel-related information)

• Command line options will be added for external configuration.

SQL Changes

Here are the proposed SQL changes.

-- Default items table
CREATE TABLE items (
    item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL,
    rarity TEXT NOT NULL,
-- rarity could be INTEGER
    image TEXT NOT NULL,
    prev_img TEXT NOT NULL,

    status INTEGER NOT NULL,
-- pending(0), approved(1), rejected(2)
);

-- Users in the channel
CREATE TABLE users (
    user_id TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    status INTEGER NOT NULL,
-- follower(0), subscriber(1), ...
    user_data TEXT,
-- For additional data specific to channel
);

-- old owned_items table
CREATE TABLE inventory (
    inventory_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    transaction_id TEXT NOT NULL UNIQUE,
    user_id TEXT,
    item_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

-- old selected_items table
CREATE TABLE user_active (
    user_id TEXT NOT NULL,
    slot INTEGER NOT NULL,
-- maybe for putting different items at the same time    
    item_id INTEGER NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id),
    CONSTRAINT unique_user_slot UNIQUE (user_id, slot)
);

CREATE TABLE schedules (
    schedule_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    day_of_week TEXT NOT NULL,
    item_id INTEGER NOT NULL,

    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

Code Changes

In the new architecture, redundant tables will be removed, API calls are
replaced with function calls.