Yuxu Ge
Article · 2024-01-01

Todo List Multi-User Application: From Single-User to Cross-Platform Architecture

Tech Stack

Core Upgrade Components

1. Project Structure Reorganization

The original flat structure was reorganized to prepare for multi-platform clients:

todo-list/
├── BLUE.md              # Cross-platform development blueprint
├── CLAUDE.md            # Development guide and code analysis
├── README.md            # Project documentation
└── web-client/          # B/S Web client
    ├── backend/
    │   ├── app.py           # Flask application main file
    │   ├── models.py        # Data models
    │   ├── requirements.txt # Python dependencies
    │   ├── migrate_db.py    # Database migration script
    │   └── todo.db         # SQLite database
    ├── static/
    │   ├── auth.css        # Authentication page styles
    │   ├── auth.js         # Authentication page scripts
    │   ├── script.js       # Main application script
    │   └── style.css       # Main application styles
    └── templates/
        ├── index.html      # Main application page
        ├── login.html      # Login page
        └── register.html   # Registration page

2. Database Schema Redesign

Original Schema

CREATE TABLE todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Multi-User Schema

-- Users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    email TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Todos table (updated for multi-user support)
CREATE TABLE todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

Technical note: SQLite sets updated_at automatically only on INSERT. For UPDATE operations to record the correct timestamp, the application must set it explicitly—or use a trigger:

CREATE TRIGGER update_todos_updated_at 
AFTER UPDATE ON todos 
WHEN old.updated_at <> current_timestamp 
BEGIN 
    UPDATE todos 
    SET updated_at = CURRENT_TIMESTAMP 
    WHERE id = OLD.id; 
END;

ON DELETE CASCADE ensures that deleting a user removes all their associated todos, maintaining referential integrity.

3. User Authentication System

Backend API

# User authentication endpoints
@app.route('/api/register', methods=['POST'])
def register():
    # User registration logic with password encryption
    
@app.route('/api/login', methods=['POST'])
def login():
    # User login verification, returns JWT Token
    
@app.route('/api/logout', methods=['POST'])
def logout():
    # User logout, clears session

# Protected API endpoints
@app.route('/api/todos', methods=['GET'])
@login_required
def get_todos():
    # Returns only current user's todos

Authentication Decorator

def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return jsonify({'error': 'Authentication required'}), 401
        return f(*args, **kwargs)
    return decorated_function

4. Frontend Authentication Interface

Login page

Registration page

5. Database Migration

A dedicated migration script, migrate_db.py, handles the schema upgrade:

def migrate_database(db_path):
    # 1. Automatically backup existing database
    backup_path = backup_database(db_path)
    
    # 2. Check table structure, determine if migration is needed
    todos_columns, users_exists = check_table_structure(db_path)
    
    # 3. Safely add user_id column and users table
    # 4. Preserve existing data, assign to default user
    # 5. Add foreign key constraints to ensure data consistency
    # 6. Automatic rollback on failure

Migration features:

Technical Challenges and Solutions

1. Schema Mismatch After Upgrade

Problem: sqlite3.OperationalError: no such column: user_id

Solution: A safe migration script upgrades the table structure incrementally, preserving all existing data.

2. API Path 404 Errors

Problem: Frontend was calling /api/5 instead of /api/todos/5

Fix:

// Before fix
url: `${API_BASE}/${id}`  // /api/5

// After fix  
url: `${API_BASE}/todos/${id}`  // /api/todos/5

3. User Data Isolation

Problem: Ensuring users can only access their own data

Solution: All API endpoints verify the authenticated user, and every database query includes a user_id filter. Session management is enforced throughout.

Performance and Security

Database connections

Frontend experience

Security measures

Project Results

Features delivered

Metrics (local environment)

Cross-Platform Roadmap

The server API established here follows the BLUE.md roadmap for future platform clients:

Phase 2: Apple Ecosystem

Phase 3: Android + Windows

Phase 4: Linux + Integration Testing

Tech stack: Python Flask, SQLite, HTML5, CSS3, JavaScript, JWT, bcrypt
Key features: Multi-user authentication, data isolation, responsive design, cross-platform server architecture
Development date: July 24, 2025
Status: Web client complete; multi-platform client development in progress

© 2026 Yuxu Ge ·