003_favorites.sql RawHistoryFolder

1-- +goose Up
2-- Favorites (bookmarks) with folders, tags, and shortcuts
3
4-- Folders for organizing favorites
5CREATE TABLE IF NOT EXISTS favorite_folders (
6    id INTEGER PRIMARY KEY AUTOINCREMENT,
7    name TEXT NOT NULL,
8    icon TEXT,
9    parent_id INTEGER REFERENCES favorite_folders(id) ON DELETE CASCADE,
10    position INTEGER NOT NULL DEFAULT 0,
11    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
12);
13
14CREATE INDEX IF NOT EXISTS idx_favorite_folders_position ON favorite_folders(position);
15CREATE INDEX IF NOT EXISTS idx_favorite_folders_parent ON favorite_folders(parent_id);
16
17-- Tags for labeling favorites
18CREATE TABLE IF NOT EXISTS favorite_tags (
19    id INTEGER PRIMARY KEY AUTOINCREMENT,
20    name TEXT NOT NULL UNIQUE,
21    color TEXT NOT NULL DEFAULT '#808080',
22    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
23);
24
25-- Favorites table
26CREATE TABLE IF NOT EXISTS favorites (
27    id INTEGER PRIMARY KEY AUTOINCREMENT,
28    url TEXT NOT NULL UNIQUE,
29    title TEXT,
30    favicon_url TEXT,
31    folder_id INTEGER REFERENCES favorite_folders(id) ON DELETE SET NULL,
32    shortcut_key INTEGER CHECK (shortcut_key BETWEEN 1 AND 9),
33    position INTEGER NOT NULL DEFAULT 0,
34    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
35    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
36);
37
38CREATE INDEX IF NOT EXISTS idx_favorites_position ON favorites(position);
39CREATE INDEX IF NOT EXISTS idx_favorites_url ON favorites(url);
40CREATE INDEX IF NOT EXISTS idx_favorites_folder ON favorites(folder_id);
41CREATE UNIQUE INDEX IF NOT EXISTS idx_favorites_shortcut ON favorites(shortcut_key) WHERE shortcut_key IS NOT NULL;
42
43-- Tag assignments (junction table)
44CREATE TABLE IF NOT EXISTS favorite_tag_assignments (
45    favorite_id INTEGER NOT NULL,
46    tag_id INTEGER NOT NULL,
47    PRIMARY KEY (favorite_id, tag_id),
48    FOREIGN KEY (favorite_id) REFERENCES favorites(id) ON DELETE CASCADE,
49    FOREIGN KEY (tag_id) REFERENCES favorite_tags(id) ON DELETE CASCADE
50);
51
52CREATE INDEX IF NOT EXISTS idx_fta_favorite ON favorite_tag_assignments(favorite_id);
53CREATE INDEX IF NOT EXISTS idx_fta_tag ON favorite_tag_assignments(tag_id);
54
55-- +goose Down
56DROP TABLE IF EXISTS favorite_tag_assignments;
57DROP TABLE IF EXISTS favorites;
58DROP TABLE IF EXISTS favorite_tags;
59DROP TABLE IF EXISTS favorite_folders;