Shared Database Service

Overview

The App Hub provides a shared SQLite database layer for applications that need persistence.

Usage

Database Location

All shared databases are stored in: ~/projects/app-hub/shared_databases/

Getting a Database Connection

import sqlite3
from pathlib import Path
DB_DIR = Path.home() / "projects" / "app-hub" / "shared_databases"
DB_DIR.mkdir(exist_ok=True)
Use app-specific database name
db_path = DB_DIR / "your_app_name.db"
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row  # Access columns by name

Schema Guidelines

1. Table Names: Prefix with your app name (e.g., myapp_users) 2. Indexes: Create indexes on frequently queried columns 3. Timestamps: Use ISO 8601 format or SQLite datetime 4. Foreign Keys: Enable if you need referential integrity

Enable foreign keys
conn.execute("PRAGMA foreign_keys = ON")

Example Schema

def init_db():
    conn.execute("""
        CREATE TABLE IF NOT EXISTS myapp_users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_users_email ON myapp_users(email)")
    conn.commit()

Best Practices

• Always use context managers for connections
• Handle exceptions gracefully
• Use parameterized queries to prevent SQL injection
• Close connections when done
• Document your schema in a README

Migration Support

When changing schemas, version your database:

def get_db_version(conn):
    row = conn.execute("SELECT version FROM schema_version").fetchone()
    return row["version"] if row else 0
def migrate_db(conn, from_version):
    if from_version < 1:
        conn.execute("ALTER TABLE myapp_users ADD COLUMN profile TEXT")
    conn.commit()

Shared Authentication Service

Overview

The App Hub provides a simple shared authentication layer for applications that need user management.

Design Philosophy

Minimal and Local: The auth system is intentionally simple, file-based, and local. No external services required.

Storage Location

Auth data is stored in: ~/projects/app-hub/shared_databases/auth.db

Usage

Basic Authentication

import sqlite3
import hashlib
from pathlib import Path
AUTH_DB = Path.home() / "projects" / "app-hub" / "shared_databases" / "auth.db"
def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()
def create_user(username, password):
    conn = sqlite3.connect(AUTH_DB)
    try:
        conn.execute("""
            INSERT INTO users (username, password_hash, created_at)
            VALUES (?, ?, ?)
        """, (username, hash_password(password), datetime.now().isoformat()))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False  # User already exists
    finally:
        conn.close()
def verify_user(username, password):
    conn = sqlite3.connect(AUTH_DB)
    conn.row_factory = sqlite3.Row
    user = conn.execute(
        "SELECT * FROM users WHERE username = ?",
        (username,)
    ).fetchone()
    conn.close()
    if user and user["password_hash"] == hash_password(password):
        return dict(user)
    return None

Session Management

For web apps, use Flask sessions:

from flask import session
def login_required(f):
    def decorated_function(args, *kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        return f(args, *kwargs)
    return decorated_function
In your login route
user = verify_user(username, password)
if user:
    session['user_id'] = user['id']
    session['username'] = user['username']

API Key Authentication

For API-based apps:

import secrets
def generate_api_key(user_id):
    api_key = secrets.token_urlsafe(32)
    conn = sqlite3.connect(AUTH_DB)
    conn.execute("""
        INSERT INTO api_keys (user_id, key, created_at)
        VALUES (?, ?, ?)
    """, (user_id, api_key, datetime.now().isoformat()))
    conn.commit()
    conn.close()
    return api_key
def verify_api_key(api_key):
    conn = sqlite3.connect(AUTH_DB)
    conn.row_factory = sqlite3.Row
    key = conn.execute(
        "SELECT * FROM api_keys WHERE key = ? AND active = 1",
        (api_key,)
    ).fetchone()
    conn.close()
    return dict(key) if key else None

Registration in Manifest

Add to your app's manifest.yaml:

shared_services:
  - auth

Security Notes

• Passwords are SHA-256 hashed (basic, suitable for local apps)
• For production, consider using bcrypt or Argon2
• Always use HTTPS in production deployments
• Implement rate limiting for authentication endpoints
• Never log passwords or API keys

User Roles (Future Enhancement)

The schema supports role-based access:

Add roles to a user
conn.execute("UPDATE users SET role = ? WHERE id = ?", ("admin", user_id))
Check permissions
if user.get('role') == 'admin':
    # Allow admin actions