I have updated the Implementation Plan to reflect the recent changes in the ticket.

Previous Plan

Observations

The codebase currently uses classic-level (LevelDB) as a key-value store with JSON encoding. Database operations are simple: get, put, del, and one iterator for range queries. The data model uses prefixed keys (user:, project:, session:, etc.) to organize different entity types. The database is initialized in file:src/index.js and passed to all route handlers via app.locals.db. All operations are async and use try-catch for error handling.

Approach

The migration will replace LevelDB with SQLite using the better-sqlite3 library (synchronous, performant) or sqlite3 (async). We'll create a normalized relational schema with separate tables for users, projects, sessions, and metadata. A thin database abstraction layer will maintain the existing get/put/del API surface to minimize changes to business logic. The migration preserves all current functionality while enabling future SQL-based queries and better data integrity through proper schemas and indexes.

Implementation Steps

1. Database Schema Design

Create a new file file:src/database/schema.sql defining the SQLite schema:

Tables to create:

users table: id (INTEGER PRIMARY KEY), username (TEXT UNIQUE), username_lower (TEXT UNIQUE), salt (TEXT), hash (TEXT), bio (TEXT), joined (INTEGER), featured_project_id (TEXT), avatar_file (TEXT)

projects table: id (TEXT PRIMARY KEY), author (TEXT), name (TEXT), description (TEXT), lang_id (TEXT), metadata (TEXT/JSON), thumbnail (INTEGER/BOOLEAN), created_at (INTEGER), flagged (INTEGER/BOOLEAN), FOREIGN KEY to users

sessions table: token (TEXT PRIMARY KEY), username (TEXT), expires (INTEGER), FOREIGN KEY to users

metadata table: key (TEXT PRIMARY KEY), value (TEXT) - for storing counters and arrays like project_counter and projects:recent

Indexes to create:

Index on users.username_lower for case-insensitive lookups

Index on projects.author for user project queries

Index on projects.created_at for time-based sorting

Index on sessions.expires for cleanup queries

2. Install SQLite Dependency

Update file:package.json:

Add better-sqlite3 to dependencies (recommended for Node.js, synchronous API, better performance)

Alternative: sqlite3 if async API is preferred

Remove classic-level from dependencies after migration is complete

3. Create Database Abstraction Layer

Create file:src/database/db.js that exports a database wrapper:

Core methods to implement:

open() - Initialize SQLite connection, create tables if not exist, run schema.sql

get(key) - Parse key prefix, query appropriate table, return JSON-compatible object

put(key, value) - Parse key prefix, insert or update in appropriate table

del(key) - Parse key prefix, delete from appropriate table

iterator(options) - Implement range query support for moderation panel (query projects table with WHERE clause)

close() - Close database connection gracefully

Key parsing logic:

Keys starting with user: → query/update users table

Keys starting with project: → query/update projects table

Keys starting with session: → query/update sessions table

Keys like project_counter, projects:recent → query/update metadata table

Keys like projects_by_time: → can be ignored (redundant with created_at column)

Error handling:

Throw errors matching LevelDB behavior (e.g., throw on get() when key not found)

Wrap SQLite errors to maintain consistent error interface

4. Update Database Initialization

Modify file:src/index.js:

Lines 8, 49-62 changes:

Replace const { ClassicLevel } = require('classic-level') with const Database = require('./database/db')

Replace const db = new ClassicLevel(dbPath, { valueEncoding: 'json' }) with const db = new Database(path.join(__dirname, 'telewarp.db'))

Update the async initialization block to call db.open() instead of db.open()

Keep app.locals.db = db unchanged

Lines 99, 103 changes:

No changes needed - the abstraction layer handles the key-based API

5. Update API Files

No changes required to the following files if the abstraction layer properly mimics LevelDB API:

file:src/api/user-api.js - All db.get/put operations work through abstraction

file:src/api/project.js - All db.get operations work through abstraction

file:src/api/upload.js - All db.get/put operations work through abstraction

file:src/api/moderation.js - All db.get/put/del operations work through abstraction

file:src/api/sb3.js - All db.get operations work through abstraction

Potential optimization opportunities (optional, not required):

Replace key-based lookups with direct SQL queries for better performance

Add pagination support using SQL LIMIT/OFFSET

Implement full-text search on project names/descriptions

6. Update View Server Files

No changes required to the following files if the abstraction layer properly mimics LevelDB API:

file:src/views/index/page.server.js - db.get operations work through abstraction

file:src/views/projects/[id]/page.server.js - db.get operations work through abstraction

file:src/views/user/[name]/page.server.js - db.get operations work through abstraction

Update required for file:src/views/moderation/page.server.js:

Lines 4-6: Replace iterator usage with direct SQL query through abstraction layer

The abstraction layer's iterator() method should handle the range query { gt: "project:", lt: "project;" } by querying all rows from projects table

7. Data Migration Script

Create file:src/database/migrate.js for one-time migration:

Migration steps:

Open existing LevelDB database (read-only)

Open new SQLite database

Iterate through all LevelDB entries

Parse keys and insert into appropriate SQLite tables

Handle key prefixes: user:, project:, session:, metadata keys

Validate data integrity after migration

Create backup of LevelDB directory before migration

Usage:

Run once: node src/database/migrate.js

Keep LevelDB data as backup until migration is verified

8. Testing Considerations

Manual testing checklist:

User signup/signin/session management

Project upload and retrieval

Avatar upload and display

Recent projects list on homepage

User profile pages with project listings

Moderation panel project listing

Project deletion and flagging

Data integrity checks:

Verify all users migrated correctly

Verify all projects migrated correctly

Verify all sessions migrated correctly

Check foreign key relationships are valid

9. Cleanup

After successful migration and testing:

Remove classic-level from file:package.json

Remove or archive the leveldb directory

Update documentation/README if it mentions LevelDB

Consider adding database backup/restore scripts

Architecture Diagram
sequenceDiagram
    participant App as Express App
    participant DB as Database Abstraction
    participant SQLite as SQLite Database
    
    App->>DB: db.get("user:john")
    DB->>DB: Parse key prefix
    DB->>SQLite: SELECT * FROM users WHERE username_lower = 'john'
    SQLite-->>DB: User row
    DB-->>App: JSON object
    
    App->>DB: db.put("project:123", projectData)
    DB->>DB: Parse key prefix
    DB->>SQLite: INSERT OR REPLACE INTO projects (...)
    SQLite-->>DB: Success
    DB-->>App: Success
    
    App->>DB: db.iterator({gt: "project:", lt: "project;"})
    DB->>SQLite: SELECT * FROM projects ORDER BY created_at DESC
    SQLite-->>DB: Project rows
    DB-->>App: Iterator with rows
Loading
Data Model Comparison

LevelDB Key Pattern SQLite Table Notes

user:{username} users Indexed on username_lower

project:{id} projects Foreign key to users.username

session:{token} sessions Foreign key to users.username

project_counter metadata Single row with key='project_counter'

projects:recent metadata Single row with JSON array

projects_by_time:{ts}:{id} N/A Redundant, use projects.created_at

Import In IDE
🤖 Prompt for AI Agents
## Observations

The codebase currently uses `classic-level` (LevelDB) as a key-value store with JSON encoding. Database operations are simple: get, put, del, and one iterator for range queries. The data model uses prefixed keys (`user:`, `project:`, `session:`, etc.) to organize different entity types. The database is initialized in `file:src/index.js` and passed to all route handlers via `app.locals.db`. All operations are async and use try-catch for error handling.

## Approach

The migration will replace LevelDB with SQLite using the `better-sqlite3` library (synchronous, performant) or `sqlite3` (async). We'll create a normalized relational schema with separate tables for users, projects, sessions, and metadata. A thin database abstraction layer will maintain the existing get/put/del API surface to minimize changes to business logic. The migration preserves all current functionality while enabling future SQL-based queries and better data integrity through proper schemas and indexes.

## Implementation Steps

### 1. Database Schema Design

Create a new file `file:src/database/schema.sql` defining the SQLite schema:

**Tables to create:**
- `users` table: id (INTEGER PRIMARY KEY), username (TEXT UNIQUE), username_lower (TEXT UNIQUE), salt (TEXT), hash (TEXT), bio (TEXT), joined (INTEGER), featured_project_id (TEXT), avatar_file (TEXT)
- `projects` table: id (TEXT PRIMARY KEY), author (TEXT), name (TEXT), description (TEXT), lang_id (TEXT), metadata (TEXT/JSON), thumbnail (INTEGER/BOOLEAN), created_at (INTEGER), flagged (INTEGER/BOOLEAN), FOREIGN KEY to users
- `sessions` table: token (TEXT PRIMARY KEY), username (TEXT), expires (INTEGER), FOREIGN KEY to users
- `metadata` table: key (TEXT PRIMARY KEY), value (TEXT) - for storing counters and arrays like project_counter and projects:recent

**Indexes to create:**
- Index on `users.username_lower` for case-insensitive lookups
- Index on `projects.author` for user project queries
- Index on `projects.created_at` for time-based sorting
- Index on `sessions.expires` for cleanup queries

### 2. Install SQLite Dependency

Update `file:package.json`:
- Add `better-sqlite3` to dependencies (recommended for Node.js, synchronous API, better performance)
- Alternative: `sqlite3` if async API is preferred
- Remove `classic-level` from dependencies after migration is complete

### 3. Create Database Abstraction Layer

Create `file:src/database/db.js` that exports a database wrapper:

**Core methods to implement:**
- `open()` - Initialize SQLite connection, create tables if not exist, run schema.sql
- `get(key)` - Parse key prefix, query appropriate table, return JSON-compatible object
- `put(key, value)` - Parse key prefix, insert or update in appropriate table
- `del(key)` - Parse key prefix, delete from appropriate table
- `iterator(options)` - Implement range query support for moderation panel (query projects table with WHERE clause)
- `close()` - Close database connection gracefully

**Key parsing logic:**
- Keys starting with `user:` → query/update `users` table
- Keys starting with `project:` → query/update `projects` table
- Keys starting with `session:` → query/update `sessions` table
- Keys like `project_counter`, `projects:recent` → query/update `metadata` table
- Keys like `projects_by_time:` → can be ignored (redundant with created_at column)

**Error handling:**
- Throw errors matching LevelDB behavior (e.g., throw on get() when key not found)
- Wrap SQLite errors to maintain consistent error interface

### 4. Update Database Initialization

Modify `file:src/index.js`:

**Lines 8, 49-62 changes:**
- Replace `const { ClassicLevel } = require('classic-level')` with `const Database = require('./database/db')`
- Replace `const db = new ClassicLevel(dbPath, { valueEncoding: 'json' })` with `const db = new Database(path.join(__dirname, 'telewarp.db'))`
- Update the async initialization block to call `db.open()` instead of `db.open()`
- Keep `app.locals.db = db` unchanged

**Lines 99, 103 changes:**
- No changes needed - the abstraction layer handles the key-based API

### 5. Update API Files

**No changes required** to the following files if the abstraction layer properly mimics LevelDB API:
- `file:src/api/user-api.js` - All db.get/put operations work through abstraction
- `file:src/api/project.js` - All db.get operations work through abstraction
- `file:src/api/upload.js` - All db.get/put operations work through abstraction
- `file:src/api/moderation.js` - All db.get/put/del operations work through abstraction
- `file:src/api/sb3.js` - All db.get operations work through abstraction

**Potential optimization opportunities** (optional, not required):
- Replace key-based lookups with direct SQL queries for better performance
- Add pagination support using SQL LIMIT/OFFSET
- Implement full-text search on project names/descriptions

### 6. Update View Server Files

**No changes required** to the following files if the abstraction layer properly mimics LevelDB API:
- `file:src/views/index/page.server.js` - db.get operations work through abstraction
- `file:src/views/projects/[id]/page.server.js` - db.get operations work through abstraction
- `file:src/views/user/[name]/page.server.js` - db.get operations work through abstraction

**Update required** for `file:src/views/moderation/page.server.js`:
- Lines 4-6: Replace iterator usage with direct SQL query through abstraction layer
- The abstraction layer's `iterator()` method should handle the range query `{ gt: "project:", lt: "project;" }` by querying all rows from projects table

### 7. Data Migration Script

Create `file:src/database/migrate.js` for one-time migration:

**Migration steps:**
- Open existing LevelDB database (read-only)
- Open new SQLite database
- Iterate through all LevelDB entries
- Parse keys and insert into appropriate SQLite tables
- Handle key prefixes: user:, project:, session:, metadata keys
- Validate data integrity after migration
- Create backup of LevelDB directory before migration

**Usage:**
- Run once: `node src/database/migrate.js`
- Keep LevelDB data as backup until migration is verified

### 8. Testing Considerations

**Manual testing checklist:**
- User signup/signin/session management
- Project upload and retrieval
- Avatar upload and display
- Recent projects list on homepage
- User profile pages with project listings
- Moderation panel project listing
- Project deletion and flagging

**Data integrity checks:**
- Verify all users migrated correctly
- Verify all projects migrated correctly
- Verify all sessions migrated correctly
- Check foreign key relationships are valid

### 9. Cleanup

After successful migration and testing:
- Remove `classic-level` from `file:package.json`
- Remove or archive the `leveldb` directory
- Update documentation/README if it mentions LevelDB
- Consider adding database backup/restore scripts

## Architecture Diagram

```mermaid
sequenceDiagram
    participant App as Express App
    participant DB as Database Abstraction
    participant SQLite as SQLite Database
    
    App->>DB: db.get("user:john")
    DB->>DB: Parse key prefix
    DB->>SQLite: SELECT * FROM users WHERE username_lower = 'john'
    SQLite-->>DB: User row
    DB-->>App: JSON object
    
    App->>DB: db.put("project:123", projectData)
    DB->>DB: Parse key prefix
    DB->>SQLite: INSERT OR REPLACE INTO projects (...)
    SQLite-->>DB: Success
    DB-->>App: Success
    
    App->>DB: db.iterator({gt: "project:", lt: "project;"})
    DB->>SQLite: SELECT * FROM projects ORDER BY created_at DESC
    SQLite-->>DB: Project rows
    DB-->>App: Iterator with rows
```

## Data Model Comparison

| LevelDB Key Pattern | SQLite Table | Notes |
|---------------------|--------------|-------|
| `user:{username}` | `users` | Indexed on username_lower |
| `project:{id}` | `projects` | Foreign key to users.username |
| `session:{token}` | `sessions` | Foreign key to users.username |
| `project_counter` | `metadata` | Single row with key='project_counter' |
| `projects:recent` | `metadata` | Single row with JSON array |
| `projects_by_time:{ts}:{id}` | N/A | Redundant, use projects.created_at |
Execution Information

Branch: main
Commit: 7c56588
New Plan

Observations

The codebase currently uses classic-level (LevelDB) as a key-value store with JSON encoding. Database operations are simple: get, put, del, and one iterator for range queries. The data model uses prefixed keys (user:, project:, session:, etc.) to organize different entity types. The database is initialized in file:src/index.js and passed to all route handlers via app.locals.db. All operations are async and use try-catch for error handling. The codebase includes view server files that fetch recent projects and iterate through all projects for moderation purposes.

Approach

The migration will replace LevelDB with SQLite as the sole database solution. SQLite is chosen for its simplicity, no external server requirement, and sufficient performance for the current and near-term scale. We'll use better-sqlite3 for synchronous, performant database access. A normalized relational schema will be created with separate tables for users, projects, sessions, and metadata. A thin database abstraction layer will maintain the existing get/put/del/iterator API surface to minimize changes to business logic. The migration preserves all current functionality while enabling future SQL-based queries and better data integrity through proper schemas and indexes.

Implementation Steps

1. Database Schema Design

Create a new file file:src/database/schema.sql defining the SQLite schema:

Tables to create:

users table: id (INTEGER PRIMARY KEY), username (TEXT UNIQUE), username_lower (TEXT UNIQUE), salt (TEXT), hash (TEXT), bio (TEXT), joined (INTEGER), featured_project_id (TEXT), avatar_file (TEXT)

projects table: id (TEXT PRIMARY KEY), author (TEXT), name (TEXT), description (TEXT), lang_id (TEXT), metadata (TEXT/JSON), thumbnail (INTEGER/BOOLEAN), created_at (INTEGER), flagged (INTEGER/BOOLEAN), FOREIGN KEY to users

sessions table: token (TEXT PRIMARY KEY), username (TEXT), expires (INTEGER), FOREIGN KEY to users

metadata table: key (TEXT PRIMARY KEY), value (TEXT) - for storing counters and arrays like project_counter and projects:recent

Indexes to create:

Index on users.username_lower for case-insensitive lookups

Index on projects.author for user project queries

Index on projects.created_at for time-based sorting

Index on sessions.expires for cleanup queries

2. Install SQLite Dependency

Update file:package.json:

Add better-sqlite3 to dependencies (synchronous API, better performance for Node.js)

Remove classic-level from dependencies after migration is complete

3. Create Database Abstraction Layer

Create file:src/database/db.js that exports a database wrapper using better-sqlite3:

Core methods to implement:

open() - Initialize SQLite connection, create tables if not exist, run schema.sql

get(key) - Parse key prefix, query appropriate table, return JSON-compatible object. Throw error if key not found (matching LevelDB behavior)

put(key, value) - Parse key prefix, insert or update in appropriate table

del(key) - Parse key prefix, delete from appropriate table

iterator(options) - Implement range query support for moderation panel. For { gt: "project:", lt: "project;" }, query all rows from projects table ordered by created_at DESC. Return an async iterable that yields [key, value] pairs

close() - Close database connection gracefully

Key parsing logic:

Keys starting with user: → extract username (lowercase), query/update users table

Keys starting with project: → extract project id, query/update projects table

Keys starting with session: → extract token, query/update sessions table

Keys like project_counter, projects:recent → query/update metadata table with key column

Keys like projects_by_time: → ignore (redundant with created_at column in projects table)

Implementation notes:

Use better-sqlite3 synchronous API for simplicity and performance

Store JSON objects as TEXT in SQLite, parse/stringify in abstraction layer

For projects:recent metadata, store as JSON array string

For project_counter metadata, store as string representation of BigInt

Throw errors matching LevelDB behavior (e.g., throw on get() when key not found)

Wrap SQLite errors to maintain consistent error interface

4. Update Database Initialization

Modify file:src/index.js:

Lines 8, 49-62 changes:

Replace const { ClassicLevel } = require('classic-level') with const Database = require('./database/db')

Replace const db = new ClassicLevel(dbPath, { valueEncoding: 'json' }) with const db = new Database(path.join(__dirname, 'telewarp.db'))

Keep the async initialization block that calls db.open() (no changes needed)

Keep app.locals.db = db unchanged

Lines 99, 103-104 changes:

No changes needed - the abstraction layer handles the key-based API and maintains async/await compatibility

5. Update API Files

No changes required to the following files if the abstraction layer properly mimics LevelDB API:

file:src/api/user-api.js - All db.get/put operations work through abstraction (signup, signin, profile update, avatar upload)

file:src/api/project.js - All db.get operations work through abstraction (fetch project metadata)

file:src/api/upload.js - All db.get/put operations work through abstraction (project counter, recent projects list, project metadata storage)

file:src/api/moderation.js - All db.get/put/del operations work through abstraction (project deletion and flagging)

file:src/api/sb3.js - All db.get operations work through abstraction (fetch project for download)

Note on upload.js: The code stores projects_by_time:{timestamp}:{id} keys which are redundant with the created_at column in the projects table. The abstraction layer can safely ignore these keys on put operations since the created_at timestamp is already stored in the projects table.

Potential optimization opportunities (optional, not required):

Replace key-based lookups with direct SQL queries for better performance

Add pagination support using SQL LIMIT/OFFSET

Implement full-text search on project names/descriptions

6. Update View Server Files

No changes required to the following files if the abstraction layer properly mimics LevelDB API:

file:src/views/index/page.server.js - db.get operations work through abstraction (fetches recent projects list and individual project data)

file:src/views/projects/[id]/page.server.js - db.get operations work through abstraction (fetches project data by id)

file:src/views/projects/[id]/embed/page.server.js - db.get operations work through abstraction (fetches project data for embed)

file:src/views/user/[name]/page.server.js - db.get operations work through abstraction (fetches user data and user's projects)

No changes required for file:src/views/moderation/page.server.js:

Lines 8: The abstraction layer's iterator() method handles the range query { gt: "project:", lt: "project;" } by querying all rows from projects table and yielding [key, value] pairs

The for-await loop continues to work as-is with the abstraction layer's async iterable implementation

7. Data Migration Script

Create file:src/database/migrate.js for one-time migration:

Migration steps:

Create backup of existing leveldb directory before starting migration

Open existing LevelDB database (read-only)

Open new SQLite database

Iterate through all LevelDB entries

Parse keys and insert into appropriate SQLite tables:

user:* keys → insert into users table

project:* keys → insert into projects table

session:* keys → insert into sessions table

project_counter, projects:recent → insert into metadata table

projects_by_time:* keys → skip (redundant)

Validate data integrity after migration (count rows, verify foreign keys)

Print migration summary (users migrated, projects migrated, sessions migrated, etc.)

Usage:

Run once before deploying: node src/database/migrate.js

Keep LevelDB data as backup until migration is verified in production

Script should exit with error code if migration fails

8. Testing Considerations

Manual testing checklist:

User signup/signin/session management (user-api.js actions)

Project upload and retrieval (upload.js and project.js)

Avatar upload and display (user-api.js upload_avatar action)

Recent projects list on homepage (index/page.server.js)

User profile pages with project listings (user/[name]/page.server.js)

Project detail pages (projects/[id]/page.server.js)

Project embed pages (projects/[id]/embed/page.server.js)

Project download as ZIP (sb3.js)

Moderation panel project listing and iteration (moderation/page.server.js)

Project deletion and flagging (moderation.js)

Session expiration handling (index.js auth middleware)

Data integrity checks:

Verify all users migrated correctly with correct usernames and passwords

Verify all projects migrated correctly with metadata and timestamps

Verify all sessions migrated correctly with expiration times

Check foreign key relationships are valid (projects.author references users.username)

Verify project_counter increments correctly on new uploads

Verify projects:recent array maintains correct order and size

9. Cleanup

After successful migration and testing in production:

Remove classic-level from file:package.json

Remove or archive the leveldb directory (keep as backup for at least one release cycle)

Update documentation/README if it mentions LevelDB

Consider adding database backup/restore scripts for SQLite

Document the SQLite schema in a README or schema documentation file

Architecture Diagram
sequenceDiagram
    participant App as Express App
    participant DB as Database Abstraction
    participant SQLite as SQLite Database
    
    App->>DB: db.get("user:john")
    DB->>DB: Parse key prefix
    DB->>SQLite: SELECT * FROM users WHERE username_lower = 'john'
    SQLite-->>DB: User row
    DB-->>App: JSON object
    
    App->>DB: db.put("project:123", projectData)
    DB->>DB: Parse key prefix
    DB->>SQLite: INSERT OR REPLACE INTO projects (...)
    SQLite-->>DB: Success
    DB-->>App: Success
    
    App->>DB: db.iterator({gt: "project:", lt: "project;"})
    DB->>SQLite: SELECT * FROM projects ORDER BY created_at DESC
    SQLite-->>DB: Project rows
    DB-->>App: Iterator with rows
Loading
Data Model Comparison

LevelDB Key Pattern SQLite Table Notes

user:{username} users Indexed on username_lower

project:{id} projects Foreign key to users.username

session:{token} sessions Foreign key to users.username

project_counter metadata Single row with key='project_counter'

projects:recent metadata Single row with JSON array

projects_by_time:{ts}:{id} N/A Redundant, use projects.created_at

Import In IDE
🤖 Prompt for AI Agents
## Observations

The codebase currently uses `classic-level` (LevelDB) as a key-value store with JSON encoding. Database operations are simple: get, put, del, and one iterator for range queries. The data model uses prefixed keys (`user:`, `project:`, `session:`, etc.) to organize different entity types. The database is initialized in `file:src/index.js` and passed to all route handlers via `app.locals.db`. All operations are async and use try-catch for error handling. The codebase includes view server files that fetch recent projects and iterate through all projects for moderation purposes.

## Approach

The migration will replace LevelDB with SQLite as the sole database solution. SQLite is chosen for its simplicity, no external server requirement, and sufficient performance for the current and near-term scale. We'll use `better-sqlite3` for synchronous, performant database access. A normalized relational schema will be created with separate tables for users, projects, sessions, and metadata. A thin database abstraction layer will maintain the existing get/put/del/iterator API surface to minimize changes to business logic. The migration preserves all current functionality while enabling future SQL-based queries and better data integrity through proper schemas and indexes.

## Implementation Steps

### 1. Database Schema Design

Create a new file `file:src/database/schema.sql` defining the SQLite schema:

**Tables to create:**
- `users` table: id (INTEGER PRIMARY KEY), username (TEXT UNIQUE), username_lower (TEXT UNIQUE), salt (TEXT), hash (TEXT), bio (TEXT), joined (INTEGER), featured_project_id (TEXT), avatar_file (TEXT)
- `projects` table: id (TEXT PRIMARY KEY), author (TEXT), name (TEXT), description (TEXT), lang_id (TEXT), metadata (TEXT/JSON), thumbnail (INTEGER/BOOLEAN), created_at (INTEGER), flagged (INTEGER/BOOLEAN), FOREIGN KEY to users
- `sessions` table: token (TEXT PRIMARY KEY), username (TEXT), expires (INTEGER), FOREIGN KEY to users
- `metadata` table: key (TEXT PRIMARY KEY), value (TEXT) - for storing counters and arrays like project_counter and projects:recent

**Indexes to create:**
- Index on `users.username_lower` for case-insensitive lookups
- Index on `projects.author` for user project queries
- Index on `projects.created_at` for time-based sorting
- Index on `sessions.expires` for cleanup queries

### 2. Install SQLite Dependency

Update `file:package.json`:
- Add `better-sqlite3` to dependencies (synchronous API, better performance for Node.js)
- Remove `classic-level` from dependencies after migration is complete

### 3. Create Database Abstraction Layer

Create `file:src/database/db.js` that exports a database wrapper using `better-sqlite3`:

**Core methods to implement:**
- `open()` - Initialize SQLite connection, create tables if not exist, run schema.sql
- `get(key)` - Parse key prefix, query appropriate table, return JSON-compatible object. Throw error if key not found (matching LevelDB behavior)
- `put(key, value)` - Parse key prefix, insert or update in appropriate table
- `del(key)` - Parse key prefix, delete from appropriate table
- `iterator(options)` - Implement range query support for moderation panel. For `{ gt: "project:", lt: "project;" }`, query all rows from projects table ordered by created_at DESC. Return an async iterable that yields [key, value] pairs
- `close()` - Close database connection gracefully

**Key parsing logic:**
- Keys starting with `user:` → extract username (lowercase), query/update `users` table
- Keys starting with `project:` → extract project id, query/update `projects` table
- Keys starting with `session:` → extract token, query/update `sessions` table
- Keys like `project_counter`, `projects:recent` → query/update `metadata` table with key column
- Keys like `projects_by_time:` → ignore (redundant with created_at column in projects table)

**Implementation notes:**
- Use `better-sqlite3` synchronous API for simplicity and performance
- Store JSON objects as TEXT in SQLite, parse/stringify in abstraction layer
- For `projects:recent` metadata, store as JSON array string
- For `project_counter` metadata, store as string representation of BigInt
- Throw errors matching LevelDB behavior (e.g., throw on get() when key not found)
- Wrap SQLite errors to maintain consistent error interface

### 4. Update Database Initialization

Modify `file:src/index.js`:

**Lines 8, 49-62 changes:**
- Replace `const { ClassicLevel } = require('classic-level')` with `const Database = require('./database/db')`
- Replace `const db = new ClassicLevel(dbPath, { valueEncoding: 'json' })` with `const db = new Database(path.join(__dirname, 'telewarp.db'))`
- Keep the async initialization block that calls `db.open()` (no changes needed)
- Keep `app.locals.db = db` unchanged

**Lines 99, 103-104 changes:**
- No changes needed - the abstraction layer handles the key-based API and maintains async/await compatibility

### 5. Update API Files

**No changes required** to the following files if the abstraction layer properly mimics LevelDB API:
- `file:src/api/user-api.js` - All db.get/put operations work through abstraction (signup, signin, profile update, avatar upload)
- `file:src/api/project.js` - All db.get operations work through abstraction (fetch project metadata)
- `file:src/api/upload.js` - All db.get/put operations work through abstraction (project counter, recent projects list, project metadata storage)
- `file:src/api/moderation.js` - All db.get/put/del operations work through abstraction (project deletion and flagging)
- `file:src/api/sb3.js` - All db.get operations work through abstraction (fetch project for download)

**Note on upload.js:** The code stores `projects_by_time:{timestamp}:{id}` keys which are redundant with the `created_at` column in the projects table. The abstraction layer can safely ignore these keys on put operations since the created_at timestamp is already stored in the projects table.

**Potential optimization opportunities** (optional, not required):
- Replace key-based lookups with direct SQL queries for better performance
- Add pagination support using SQL LIMIT/OFFSET
- Implement full-text search on project names/descriptions

### 6. Update View Server Files

**No changes required** to the following files if the abstraction layer properly mimics LevelDB API:
- `file:src/views/index/page.server.js` - db.get operations work through abstraction (fetches recent projects list and individual project data)
- `file:src/views/projects/[id]/page.server.js` - db.get operations work through abstraction (fetches project data by id)
- `file:src/views/projects/[id]/embed/page.server.js` - db.get operations work through abstraction (fetches project data for embed)
- `file:src/views/user/[name]/page.server.js` - db.get operations work through abstraction (fetches user data and user's projects)

**No changes required** for `file:src/views/moderation/page.server.js`:
- Lines 8: The abstraction layer's `iterator()` method handles the range query `{ gt: "project:", lt: "project;" }` by querying all rows from projects table and yielding [key, value] pairs
- The for-await loop continues to work as-is with the abstraction layer's async iterable implementation

### 7. Data Migration Script

Create `file:src/database/migrate.js` for one-time migration:

**Migration steps:**
- Create backup of existing `leveldb` directory before starting migration
- Open existing LevelDB database (read-only)
- Open new SQLite database
- Iterate through all LevelDB entries
- Parse keys and insert into appropriate SQLite tables:
  - `user:*` keys → insert into users table
  - `project:*` keys → insert into projects table
  - `session:*` keys → insert into sessions table
  - `project_counter`, `projects:recent` → insert into metadata table
  - `projects_by_time:*` keys → skip (redundant)
- Validate data integrity after migration (count rows, verify foreign keys)
- Print migration summary (users migrated, projects migrated, sessions migrated, etc.)

**Usage:**
- Run once before deploying: `node src/database/migrate.js`
- Keep LevelDB data as backup until migration is verified in production
- Script should exit with error code if migration fails

### 8. Testing Considerations

**Manual testing checklist:**
- User signup/signin/session management (user-api.js actions)
- Project upload and retrieval (upload.js and project.js)
- Avatar upload and display (user-api.js upload_avatar action)
- Recent projects list on homepage (index/page.server.js)
- User profile pages with project listings (user/[name]/page.server.js)
- Project detail pages (projects/[id]/page.server.js)
- Project embed pages (projects/[id]/embed/page.server.js)
- Project download as ZIP (sb3.js)
- Moderation panel project listing and iteration (moderation/page.server.js)
- Project deletion and flagging (moderation.js)
- Session expiration handling (index.js auth middleware)

**Data integrity checks:**
- Verify all users migrated correctly with correct usernames and passwords
- Verify all projects migrated correctly with metadata and timestamps
- Verify all sessions migrated correctly with expiration times
- Check foreign key relationships are valid (projects.author references users.username)
- Verify project_counter increments correctly on new uploads
- Verify projects:recent array maintains correct order and size

### 9. Cleanup

After successful migration and testing in production:
- Remove `classic-level` from `file:package.json`
- Remove or archive the `leveldb` directory (keep as backup for at least one release cycle)
- Update documentation/README if it mentions LevelDB
- Consider adding database backup/restore scripts for SQLite
- Document the SQLite schema in a README or schema documentation file

## Architecture Diagram

```mermaid
sequenceDiagram
    participant App as Express App
    participant DB as Database Abstraction
    participant SQLite as SQLite Database
    
    App->>DB: db.get("user:john")
    DB->>DB: Parse key prefix
    DB->>SQLite: SELECT * FROM users WHERE username_lower = 'john'
    SQLite-->>DB: User row
    DB-->>App: JSON object
    
    App->>DB: db.put("project:123", projectData)
    DB->>DB: Parse key prefix
    DB->>SQLite: INSERT OR REPLACE INTO projects (...)
    SQLite-->>DB: Success
    DB-->>App: Success
    
    App->>DB: db.iterator({gt: "project:", lt: "project;"})
    DB->>SQLite: SELECT * FROM projects ORDER BY created_at DESC
    SQLite-->>DB: Project rows
    DB-->>App: Iterator with rows
```

## Data Model Comparison

| LevelDB Key Pattern | SQLite Table | Notes |
|---------------------|--------------|-------|
| `user:{username}` | `users` | Indexed on username_lower |
| `project:{id}` | `projects` | Foreign key to users.username |
| `session:{token}` | `sessions` | Foreign key to users.username |
| `project_counter` | `metadata` | Single row with key='project_counter' |
| `projects:recent` | `metadata` | Single row with JSON array |
| `projects_by_time:{ts}:{id}` | N/A | Redundant, use projects.created_at |
Execution Information

Branch: main
Commit: 79ca1bb

LevelDB Key Pattern	SQLite Table	Notes
`user:{username}`	`users`	Indexed on username_lower
`project:{id}`	`projects`	Foreign key to users.username
`session:{token}`	`sessions`	Foreign key to users.username
`project_counter`	`metadata`	Single row with key='project_counter'
`projects:recent`	`metadata`	Single row with JSON array
`projects_by_time:{ts}:{id}`	N/A	Redundant, use projects.created_at

replace level with sql #23

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions