82 lines
2.5 KiB
SQL
82 lines
2.5 KiB
SQL
CREATE TABLE users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
email TEXT NOT NULL UNIQUE COLLATE NOCASE,
|
|
display_name TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
user_handle BLOB NOT NULL UNIQUE,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
token_hash BLOB PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
csrf_token BLOB NOT NULL,
|
|
expires_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE passkeys (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
credential_id TEXT NOT NULL UNIQUE,
|
|
credential TEXT NOT NULL,
|
|
counter INTEGER NOT NULL DEFAULT 0,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE lists (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
revision INTEGER NOT NULL DEFAULT 0,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE COLLATE NOCASE,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE invitations (
|
|
token_hash BLOB PRIMARY KEY,
|
|
created_by INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
list_id INTEGER NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
quantity TEXT NOT NULL DEFAULT '',
|
|
note TEXT NOT NULL DEFAULT '',
|
|
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
checked INTEGER NOT NULL DEFAULT 0,
|
|
version INTEGER NOT NULL DEFAULT 1,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE meals (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE meal_ingredients (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
meal_id INTEGER NOT NULL REFERENCES meals(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
quantity TEXT NOT NULL DEFAULT '',
|
|
note TEXT NOT NULL DEFAULT '',
|
|
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
position INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX items_list_idx ON items(list_id);
|
|
CREATE INDEX meal_ingredients_meal_idx ON meal_ingredients(meal_id);
|
|
CREATE INDEX sessions_user_idx ON sessions(user_id);
|
|
CREATE INDEX passkeys_user_idx ON passkeys(user_id);
|