Language & Programming / Coding Projects · Destination 4 of 6
Relational Database Development
Seven relational SQLite tables (study_queue, theory_queue, applications_queue, wishlist_todos, rap_lyrics, voice_notes, site_traffic) backing the live Workbench at Workbench/index.html: 11 studies, 4 theory modules, and 4 applications.
Live Engineering Triad Database Workbench
Direct access: Open the Notebook Workbench (Workbench/index.html) ↗ to add Theory, Application, or Study entries, wishlist tasks, spontaneous rap bars, or voice notes from desktop or phone.
CREATE TABLE IF NOT EXISTS study_queue (
id TEXT PRIMARY KEY,
parent_id TEXT,
created_on TEXT NOT NULL,
title TEXT NOT NULL,
atlas_path TEXT NOT NULL,
question TEXT NOT NULL,
dataset TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('published', 'in-progress', 'queued')),
study_route TEXT,
paper_route TEXT,
clicks INTEGER NOT NULL DEFAULT 0,
chart_types TEXT NOT NULL DEFAULT 'auto',
linkages TEXT NOT NULL DEFAULT '',
notes TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS theory_queue (
id TEXT PRIMARY KEY,
created_on TEXT NOT NULL,
category TEXT NOT NULL,
title TEXT NOT NULL,
atlas_path TEXT NOT NULL,
scope_prompt TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('published', 'in-progress', 'queued')),
linkages TEXT NOT NULL DEFAULT '',
notes TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS applications_queue (
id TEXT PRIMARY KEY,
created_on TEXT NOT NULL,
domain TEXT NOT NULL,
title TEXT NOT NULL,
atlas_path TEXT NOT NULL,
system_design TEXT NOT NULL,
societal_benefit TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('published', 'in-progress', 'queued')),
linkages TEXT NOT NULL DEFAULT '',
notes TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS wishlist_todos (
id TEXT PRIMARY KEY,
created_on TEXT NOT NULL,
title TEXT NOT NULL,
category TEXT NOT NULL,
priority TEXT NOT NULL CHECK (priority IN ('high', 'medium', 'low')),
atlas_path TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('done', 'in-progress', 'planned')),
linkages TEXT NOT NULL DEFAULT '',
notes TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS rap_lyrics (
id TEXT PRIMARY KEY,
created_on TEXT NOT NULL,
bucket TEXT NOT NULL,
title TEXT NOT NULL,
bars TEXT NOT NULL,
theme_cue TEXT NOT NULL DEFAULT '',
status TEXT NOT NULL CHECK (status IN ('draft', 'recorded', 'published'))
);
CREATE TABLE IF NOT EXISTS voice_notes (
id TEXT PRIMARY KEY,
created_on TEXT NOT NULL,
channel TEXT NOT NULL CHECK (channel IN ('Journal', 'Podcast', 'Study Memo', 'Freestyle')),
title TEXT NOT NULL,
duration_seconds INTEGER NOT NULL CHECK (duration_seconds >= 0),
audio_format TEXT NOT NULL,
transcript TEXT NOT NULL,
summary TEXT NOT NULL,
atlas_path TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('processed', 'queued'))
);
CREATE TABLE IF NOT EXISTS site_traffic (
ip_index TEXT PRIMARY KEY,
masked_ip TEXT NOT NULL,
role TEXT NOT NULL,
visits INTEGER NOT NULL DEFAULT 1,
study_clicks INTEGER NOT NULL DEFAULT 0,
top_section TEXT NOT NULL,
last_seen TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS reabs_reminders (
id TEXT PRIMARY KEY,
created_on TEXT NOT NULL,
title TEXT NOT NULL,
urgency TEXT NOT NULL CHECK (urgency IN ('High', 'Medium', 'Low')),
schedule_text TEXT NOT NULL,
days_of_week TEXT NOT NULL,
time_of_day TEXT NOT NULL,
phone_number TEXT NOT NULL DEFAULT '',
phone_source TEXT NOT NULL DEFAULT 'direct',
reasoning TEXT NOT NULL,
default_email TEXT NOT NULL DEFAULT 'l2mcgrat@uwaterloo.ca',
extra_emails TEXT NOT NULL DEFAULT '',
sms_enabled INTEGER NOT NULL DEFAULT 1,
reminders_enabled INTEGER NOT NULL DEFAULT 1,
status TEXT NOT NULL CHECK (status IN ('active', 'completed', 'muted')),
completed_on TEXT,
hours_spent REAL NOT NULL DEFAULT 0.0
);