Padreug
30754bfa8f
Every step now uses CREATE [TABLE|INDEX] IF NOT EXISTS or is wrapped via a _safe(stmt) helper that swallows OperationalError, and backfill INSERTs become INSERT OR IGNORE. So a partially-applied m002 (interrupted by a crash before the dbversion bump) re-runs cleanly on next startup instead of failing on duplicate-table / duplicate-index / duplicate-PK errors. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
478 lines
18 KiB
Python
478 lines
18 KiB
Python
async def m001_initial(db):
|
||
"""
|
||
Initial schema for the restaurant extension.
|
||
|
||
Design notes
|
||
------------
|
||
* One LNbits wallet → one *or many* restaurants. We do not assume 1:1.
|
||
A single owner can run multiple kitchens out of one LNbits account.
|
||
* Each `restaurants` row carries its own Nostr identity (pubkey + relay
|
||
hints). The wallet's account keypair is the default signing key, but
|
||
a per-restaurant override is allowed for venues that want to keep
|
||
their public identity separate from their LNbits owner identity.
|
||
* Every publishable row (restaurants, menu_items) has nostr_event_id +
|
||
nostr_event_created_at columns so reconciliation against relay state
|
||
is cheap.
|
||
* We do NOT store an "umbrella order" spanning multiple restaurants.
|
||
Cross-restaurant grouping is the customer/webapp's concern; the
|
||
extension only ever knows about its own restaurant's orders.
|
||
* Modifiers and addons are unified under modifier_groups +
|
||
modifiers (a `kind` column on the group distinguishes "required
|
||
choice" from "optional addon"). Flat is better than nested.
|
||
"""
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Restaurants #
|
||
# ---------------------------------------------------------------- #
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.restaurants (
|
||
id TEXT PRIMARY KEY,
|
||
wallet TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
slug TEXT NOT NULL,
|
||
description TEXT,
|
||
currency TEXT NOT NULL DEFAULT 'sat',
|
||
timezone TEXT NOT NULL DEFAULT 'UTC',
|
||
location TEXT,
|
||
geohash TEXT,
|
||
logo_url TEXT,
|
||
banner_url TEXT,
|
||
social_links TEXT,
|
||
open_hours TEXT,
|
||
is_open BOOLEAN NOT NULL DEFAULT TRUE,
|
||
accepts_cash BOOLEAN NOT NULL DEFAULT TRUE,
|
||
accepts_lightning BOOLEAN NOT NULL DEFAULT TRUE,
|
||
tip_presets TEXT,
|
||
tax_rate REAL NOT NULL DEFAULT 0,
|
||
printer_endpoint TEXT,
|
||
nostr_pubkey TEXT,
|
||
nostr_relays TEXT,
|
||
nostr_event_id TEXT,
|
||
nostr_event_created_at INTEGER,
|
||
extra TEXT,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Categories + Subcategories #
|
||
# ---------------------------------------------------------------- #
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.categories (
|
||
id TEXT PRIMARY KEY,
|
||
restaurant_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
description TEXT,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
image_url TEXT,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_categories_restaurant ON categories(restaurant_id);"
|
||
)
|
||
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.subcategories (
|
||
id TEXT PRIMARY KEY,
|
||
category_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_subcategories_category ON subcategories(category_id);"
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Menu items #
|
||
# ---------------------------------------------------------------- #
|
||
# `dietary` and `allergens` are JSON-encoded string arrays:
|
||
# dietary → ["vegan", "gluten_free", ...]
|
||
# allergens → ["nuts", "dairy", "shellfish", ...]
|
||
# `images` is a JSON array of URLs (first one is the cover).
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.menu_items (
|
||
id TEXT PRIMARY KEY,
|
||
restaurant_id TEXT NOT NULL,
|
||
category_id TEXT,
|
||
subcategory_id TEXT,
|
||
name TEXT NOT NULL,
|
||
description TEXT,
|
||
price REAL NOT NULL DEFAULT 0,
|
||
currency TEXT NOT NULL DEFAULT 'sat',
|
||
sku TEXT,
|
||
images TEXT,
|
||
dietary TEXT,
|
||
allergens TEXT,
|
||
ingredients TEXT,
|
||
calories INTEGER,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
is_available BOOLEAN NOT NULL DEFAULT TRUE,
|
||
is_featured BOOLEAN NOT NULL DEFAULT FALSE,
|
||
stock INTEGER,
|
||
low_stock_threshold INTEGER,
|
||
nostr_event_id TEXT,
|
||
nostr_event_created_at INTEGER,
|
||
extra TEXT,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_menu_items_restaurant ON menu_items(restaurant_id);"
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_menu_items_category ON menu_items(category_id);"
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Modifier groups + modifiers (covers required choices AND addons) #
|
||
# ---------------------------------------------------------------- #
|
||
# kind: 'required' (e.g. "Choose your protein"), 'optional' (e.g. "Extras")
|
||
# selection: 'one' (radio) | 'many' (checkbox)
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.modifier_groups (
|
||
id TEXT PRIMARY KEY,
|
||
menu_item_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
kind TEXT NOT NULL DEFAULT 'required',
|
||
selection TEXT NOT NULL DEFAULT 'one',
|
||
min_selections INTEGER NOT NULL DEFAULT 0,
|
||
max_selections INTEGER,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_modgroups_item ON modifier_groups(menu_item_id);"
|
||
)
|
||
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.modifiers (
|
||
id TEXT PRIMARY KEY,
|
||
group_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
description TEXT,
|
||
price_delta REAL NOT NULL DEFAULT 0,
|
||
is_default BOOLEAN NOT NULL DEFAULT FALSE,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_modifiers_group ON modifiers(group_id);"
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Availability windows (per item) #
|
||
# ---------------------------------------------------------------- #
|
||
# weekday: 0-6 (Mon-Sun), or NULL for "every day"
|
||
# start_time / end_time: 'HH:MM' 24h, restaurant-local timezone
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.availability_windows (
|
||
id TEXT PRIMARY KEY,
|
||
menu_item_id TEXT NOT NULL,
|
||
weekday INTEGER,
|
||
start_time TEXT NOT NULL,
|
||
end_time TEXT NOT NULL,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_availability_item ON availability_windows(menu_item_id);"
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Orders #
|
||
# ---------------------------------------------------------------- #
|
||
# status:
|
||
# pending → invoice issued, not yet paid
|
||
# paid → invoice settled (kicked off by tasks.py listener)
|
||
# accepted → restaurant has acknowledged, prep in progress
|
||
# ready → ready for pickup / served
|
||
# completed → finished
|
||
# canceled → manually canceled
|
||
# refunded → paid then refunded
|
||
#
|
||
# customer_pubkey is the Nostr pubkey of the ordering customer (when
|
||
# the order arrived via NIP-17 DM). Optional; cash and walk-in orders
|
||
# have no pubkey.
|
||
#
|
||
# parent_order_ref is opaque metadata so a webapp can correlate this
|
||
# order with its own multi-restaurant umbrella order. The extension
|
||
# never reads this — it just stores and echoes it back.
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.orders (
|
||
id TEXT PRIMARY KEY,
|
||
restaurant_id TEXT NOT NULL,
|
||
wallet TEXT NOT NULL,
|
||
customer_pubkey TEXT,
|
||
customer_name TEXT,
|
||
customer_contact TEXT,
|
||
status TEXT NOT NULL DEFAULT 'pending',
|
||
channel TEXT NOT NULL DEFAULT 'rest',
|
||
payment_method TEXT NOT NULL DEFAULT 'lightning',
|
||
payment_hash TEXT,
|
||
bolt11 TEXT,
|
||
subtotal_msat INTEGER NOT NULL DEFAULT 0,
|
||
tip_msat INTEGER NOT NULL DEFAULT 0,
|
||
tax_msat INTEGER NOT NULL DEFAULT 0,
|
||
total_msat INTEGER NOT NULL DEFAULT 0,
|
||
currency_display TEXT NOT NULL DEFAULT 'sat',
|
||
fiat_amount REAL,
|
||
fiat_rate REAL,
|
||
note TEXT,
|
||
parent_order_ref TEXT,
|
||
paid_at TIMESTAMP,
|
||
accepted_at TIMESTAMP,
|
||
ready_at TIMESTAMP,
|
||
completed_at TIMESTAMP,
|
||
canceled_at TIMESTAMP,
|
||
extra TEXT,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_orders_restaurant ON orders(restaurant_id);"
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_orders_payment_hash ON orders(payment_hash);"
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_orders_status ON orders(status);"
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Order items #
|
||
# ---------------------------------------------------------------- #
|
||
# selected_modifiers is a JSON snapshot of the modifier names + price
|
||
# deltas at order time. We snapshot rather than FK so price/menu
|
||
# changes don't retroactively rewrite history.
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.order_items (
|
||
id TEXT PRIMARY KEY,
|
||
order_id TEXT NOT NULL,
|
||
menu_item_id TEXT,
|
||
name TEXT NOT NULL,
|
||
quantity INTEGER NOT NULL DEFAULT 1,
|
||
unit_price_msat INTEGER NOT NULL DEFAULT 0,
|
||
line_total_msat INTEGER NOT NULL DEFAULT 0,
|
||
selected_modifiers TEXT,
|
||
note TEXT,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_order_items_order ON order_items(order_id);"
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Print jobs #
|
||
# ---------------------------------------------------------------- #
|
||
# status: queued | sent | acknowledged | failed
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE restaurant.print_jobs (
|
||
id TEXT PRIMARY KEY,
|
||
restaurant_id TEXT NOT NULL,
|
||
order_id TEXT NOT NULL,
|
||
status TEXT NOT NULL DEFAULT 'queued',
|
||
attempts INTEGER NOT NULL DEFAULT 0,
|
||
last_error TEXT,
|
||
sent_at TIMESTAMP,
|
||
acknowledged_at TIMESTAMP,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_print_jobs_order ON print_jobs(order_id);"
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX restaurant.idx_print_jobs_status ON print_jobs(status);"
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Settings #
|
||
# ---------------------------------------------------------------- #
|
||
await db.execute(
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS restaurant.settings (
|
||
id INTEGER PRIMARY KEY DEFAULT 1,
|
||
nostr_publish_enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
nostr_orders_enabled BOOLEAN NOT NULL DEFAULT FALSE,
|
||
invoice_expiry_seconds INTEGER NOT NULL DEFAULT 900,
|
||
auto_accept_orders BOOLEAN NOT NULL DEFAULT FALSE
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"""
|
||
INSERT INTO restaurant.settings (id) VALUES (1)
|
||
ON CONFLICT (id) DO NOTHING;
|
||
"""
|
||
)
|
||
|
||
|
||
async def m002_menu_tree(db):
|
||
"""
|
||
Replace the fixed `categories` + `subcategories` two-level model
|
||
with a single self-referential `menu_nodes` table (adjacency list
|
||
+ denormalized materialized path).
|
||
|
||
Why adjacency + path (not closure table, not Postgres ltree):
|
||
|
||
* Scale: 5–50 nodes per restaurant, depth ≤ 4. Closure table
|
||
is overhead at this size.
|
||
* Backend portability: works identically on SQLite + Postgres
|
||
with no extensions. ltree is Postgres-only.
|
||
* `path` ('rootid' or 'rootid/childid' / ...) gives O(1)
|
||
subtree queries (`WHERE path LIKE :p || '%'`), trivial
|
||
cycle detection on move, and a single-statement subtree
|
||
rewrite (substring + concat).
|
||
* `depth` is denormalized so we can reject "would exceed 4"
|
||
without walking the tree.
|
||
|
||
Items can attach to ANY node (not just leaves). On node delete,
|
||
the default cascade detaches items (sets node_id NULL) rather
|
||
than hard-deleting them; items are revenue-bearing and carry
|
||
nostr_event_ids, so orphaning them so the operator can re-home
|
||
via the CMS is friendlier than wiping.
|
||
"""
|
||
|
||
# Idempotent: every step uses IF [NOT] EXISTS or is wrapped in
|
||
# try/except so a partially-applied m002 (interrupted by a crash
|
||
# before the dbversion bump) re-runs cleanly on next startup.
|
||
|
||
async def _safe(stmt):
|
||
try:
|
||
await db.execute(stmt)
|
||
except Exception:
|
||
pass
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# New menu_nodes table #
|
||
# ---------------------------------------------------------------- #
|
||
await db.execute(
|
||
f"""
|
||
CREATE TABLE IF NOT EXISTS restaurant.menu_nodes (
|
||
id TEXT PRIMARY KEY,
|
||
restaurant_id TEXT NOT NULL,
|
||
parent_id TEXT,
|
||
name TEXT NOT NULL,
|
||
description TEXT,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
image_url TEXT,
|
||
depth INTEGER NOT NULL DEFAULT 0,
|
||
path TEXT NOT NULL,
|
||
time TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
|
||
);
|
||
"""
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX IF NOT EXISTS restaurant.idx_menu_nodes_restaurant "
|
||
"ON menu_nodes(restaurant_id);"
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX IF NOT EXISTS restaurant.idx_menu_nodes_parent "
|
||
"ON menu_nodes(parent_id);"
|
||
)
|
||
await db.execute(
|
||
"CREATE INDEX IF NOT EXISTS restaurant.idx_menu_nodes_path "
|
||
"ON menu_nodes(path);"
|
||
)
|
||
|
||
# Backfill from categories/subcategories. INSERT OR IGNORE in case
|
||
# an earlier run partially populated, and the SELECTs no-op on a
|
||
# retry where categories/subcategories have already been dropped.
|
||
categories_exists = await db.fetchone(
|
||
"SELECT name FROM restaurant.sqlite_master "
|
||
"WHERE type='table' AND name='categories'"
|
||
)
|
||
subcategories_exists = await db.fetchone(
|
||
"SELECT name FROM restaurant.sqlite_master "
|
||
"WHERE type='table' AND name='subcategories'"
|
||
)
|
||
|
||
if categories_exists:
|
||
await db.execute(
|
||
"""
|
||
INSERT OR IGNORE INTO restaurant.menu_nodes
|
||
(id, restaurant_id, parent_id, name, description, sort_order,
|
||
image_url, depth, path, time)
|
||
SELECT id, restaurant_id, NULL, name, description, sort_order,
|
||
image_url, 0, id, time
|
||
FROM restaurant.categories;
|
||
"""
|
||
)
|
||
|
||
if categories_exists and subcategories_exists:
|
||
await db.execute(
|
||
"""
|
||
INSERT OR IGNORE INTO restaurant.menu_nodes
|
||
(id, restaurant_id, parent_id, name, description, sort_order,
|
||
image_url, depth, path, time)
|
||
SELECT s.id, c.restaurant_id, s.category_id, s.name, NULL,
|
||
s.sort_order, NULL, 1, c.id || '/' || s.id, s.time
|
||
FROM restaurant.subcategories s
|
||
JOIN restaurant.categories c ON c.id = s.category_id;
|
||
"""
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Add menu_items.node_id and backfill #
|
||
# subcategory wins if both set #
|
||
# ---------------------------------------------------------------- #
|
||
await _safe("ALTER TABLE restaurant.menu_items ADD COLUMN node_id TEXT;")
|
||
|
||
item_cols = {
|
||
r["name"]
|
||
for r in await db.fetchall("PRAGMA restaurant.table_info(menu_items)")
|
||
}
|
||
if "subcategory_id" in item_cols and "category_id" in item_cols:
|
||
await db.execute(
|
||
"UPDATE restaurant.menu_items "
|
||
"SET node_id = COALESCE(subcategory_id, category_id);"
|
||
)
|
||
elif "category_id" in item_cols:
|
||
await db.execute(
|
||
"UPDATE restaurant.menu_items SET node_id = category_id "
|
||
"WHERE node_id IS NULL;"
|
||
)
|
||
|
||
await db.execute(
|
||
"CREATE INDEX IF NOT EXISTS restaurant.idx_menu_items_node "
|
||
"ON menu_items(node_id);"
|
||
)
|
||
|
||
# ---------------------------------------------------------------- #
|
||
# Drop old columns + tables #
|
||
# #
|
||
# `ALTER TABLE ... DROP COLUMN` requires SQLite ≥ 3.35 (2021-03) #
|
||
# and refuses to drop a column referenced by an index — drop the #
|
||
# index first. #
|
||
# ---------------------------------------------------------------- #
|
||
await db.execute("DROP INDEX IF EXISTS restaurant.idx_menu_items_category;")
|
||
await _safe("ALTER TABLE restaurant.menu_items DROP COLUMN subcategory_id;")
|
||
await _safe("ALTER TABLE restaurant.menu_items DROP COLUMN category_id;")
|
||
await db.execute("DROP TABLE IF EXISTS restaurant.subcategories;")
|
||
await db.execute("DROP TABLE IF EXISTS restaurant.categories;")
|