Schema overview, tables, relationships, and sample queries for the SONAN Location Launchpad D1 database.
Core table. One row per store location.
| Column | Type | Notes |
|---|---|---|
| id | INTEGER PK | Auto-increment |
| name | TEXT | Required. e.g. "SONAN TECH Wood Ridge" |
| status | TEXT | Planning | Lease Signed | Buildout | Pre-Opening | Open | Archived |
| target_opening_date | TEXT | ISO date string YYYY-MM-DD |
| rent_commencement_date | TEXT | ISO date string |
| ti_allowance | REAL | Dollar amount |
| monthly_rent | REAL | Dollar amount |
Master template. Tasks auto-generated from this when a location is created.
| Column | Type | Notes |
|---|---|---|
| id | INTEGER PK | |
| category | TEXT | e.g. "Buildout", "Signage" |
| title | TEXT | Task name |
| default_priority | TEXT | Low | Normal | High | Critical |
| sort_order | INTEGER | Display order within category |
| active | INTEGER | 1=active, 0=disabled |
Tasks per location. Linked to a location via location_id.
| Column | Type | Notes |
|---|---|---|
| id | INTEGER PK | |
| location_id | INTEGER FK | → locations.id (CASCADE DELETE) |
| template_id | INTEGER FK | → task_templates.id (nullable) |
| status | TEXT | Not Started | In Progress | Waiting | Done | Blocked |
| priority | TEXT | Low | Normal | High | Critical |
| due_date | TEXT | ISO date |
| completed_date | TEXT | Auto-set when status → Done |
| link_url | TEXT | Link to document or reference |
Budget line items per location.
| Column | Type | Notes |
|---|---|---|
| ti_eligible | TEXT | Yes | No | Maybe |
| paid | INTEGER | 0=No, 1=Yes |
| estimated_cost | REAL | Dollar amount |
| actual_cost | REAL | Nullable until paid |
-- Location progress summary
SELECT l.name, l.status,
COUNT(t.id) as total,
SUM(CASE WHEN t.status='Done' THEN 1 ELSE 0 END) as done
FROM locations l
LEFT JOIN tasks t ON t.location_id = l.id
GROUP BY l.id;
-- All overdue tasks
SELECT t.title, t.due_date, l.name as location
FROM tasks t JOIN locations l ON l.id = t.location_id
WHERE t.due_date < date('now') AND t.status != 'Done'
ORDER BY t.due_date;
-- TI eligible budget total
SELECT SUM(estimated_cost) as ti_total
FROM budget_items
WHERE ti_eligible = 'Yes' AND location_id = 1;