fe4cbc3537
- Web UI: login, dashboard, links CRUD, collections, API keys, admin pages - Query engine: AND/OR/XOR with field filters, tag search, preview endpoint - Session management: token expiry detection, 401 interceptor, expiry banner - Links search: tags included, multi-word AND, query mode with set operations - Collections: static/dynamic, query builder with preview, public tree view - Save as Collection: convert search results (static) or query (dynamic) - Dashboard stats: resilient loading with allSettled pattern - Login page: redesigned with public collections tree view - Bug fix: query executor None fields crash (notes/description/url/title) - E2E tests: 20 Playwright tests covering all critical user flows - All 104 tests passing (84 unit/integration + 20 E2E)
120 lines
4.4 KiB
PL/PgSQL
120 lines
4.4 KiB
PL/PgSQL
-- LinkSyncServer Database Schema
|
|
|
|
-- Enable UUID extension
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Users table
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
username VARCHAR(100) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role VARCHAR(20) NOT NULL CHECK (role IN ('admin', 'user')),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- API Keys table
|
|
CREATE TABLE api_keys (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
key_hash VARCHAR(255) NOT NULL,
|
|
name VARCHAR(100),
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Tags table
|
|
CREATE TABLE tags (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
color VARCHAR(7),
|
|
description TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Links table (bookmarks)
|
|
CREATE TABLE links (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
url TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
notes TEXT,
|
|
tags JSONB DEFAULT '[]',
|
|
favicon_url TEXT,
|
|
path TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
visit_count INTEGER DEFAULT 0,
|
|
is_bookmarked BOOLEAN DEFAULT FALSE,
|
|
source_set_id UUID REFERENCES links(id), -- Self-reference for duplicate tracking
|
|
user_id UUID REFERENCES users(id)
|
|
);
|
|
|
|
-- Create indexes for links
|
|
CREATE INDEX links_url_idx ON links(url);
|
|
CREATE INDEX links_title_idx ON links(title);
|
|
CREATE INDEX links_tags_idx ON links USING GIN (tags);
|
|
CREATE INDEX links_created_idx ON links(created_at);
|
|
CREATE INDEX links_user_idx ON links(user_id);
|
|
CREATE INDEX links_fts_idx ON links USING GIN (to_tsvector('english', url || ' ' || title || ' ' || description || ' ' || notes));
|
|
|
|
-- Collections table
|
|
CREATE TABLE collections (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
query_type VARCHAR(20) NOT NULL CHECK (query_type IN ('static', 'dynamic')),
|
|
query_expression JSONB, -- Parsed AST
|
|
is_public BOOLEAN DEFAULT FALSE,
|
|
created_by UUID REFERENCES users(id),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Collection links (for static collections)
|
|
CREATE TABLE collection_links (
|
|
collection_id UUID REFERENCES collections(id) ON DELETE CASCADE,
|
|
link_id UUID REFERENCES links(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (collection_id, link_id)
|
|
);
|
|
|
|
-- Audit log table
|
|
CREATE TABLE audit_log (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
action VARCHAR(100) NOT NULL,
|
|
entity_type VARCHAR(50) NOT NULL,
|
|
entity_id UUID,
|
|
old_value JSONB,
|
|
new_value JSONB,
|
|
ip_address INET,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create audit log index
|
|
CREATE INDEX audit_log_created_idx ON audit_log(created_at);
|
|
CREATE INDEX audit_log_user_idx ON audit_log(user_id);
|
|
|
|
-- Full-text search for tags
|
|
CREATE INDEX tags_name_idx ON tags USING GIN (to_tsvector('english', name || ' ' || description));
|
|
|
|
-- Triggers for updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_timestamps() RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER update_users_timestamps BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_timestamps();
|
|
CREATE TRIGGER update_links_timestamps BEFORE UPDATE ON links FOR EACH ROW EXECUTE FUNCTION update_timestamps();
|
|
CREATE TRIGGER update_collections_timestamps BEFORE UPDATE ON collections FOR EACH ROW EXECUTE FUNCTION update_timestamps();
|
|
CREATE TRIGGER update_tags_timestamps BEFORE UPDATE ON tags FOR EACH ROW EXECUTE FUNCTION update_timestamps(); |