rootandClaude Sonnet 4.6
c573e8bfe6
Deploy / deploy (push) Failing after 20s
- db/schema.sql: полная PostgreSQL 16 схема (12 таблиц + view works_extended) - docker-compose.yml: db + backend + frontend + nginx - nginx/nginx.conf: HTTPS proxy, WebSocket, static uploads - .env.example: все переменные задокументированы - .gitea/workflows/deploy.yml: CI деплой-заготовка - README.md: граф задач флота агентов Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
202 lines
7.3 KiB
SQL
202 lines
7.3 KiB
SQL
-- nika-gallery: полная схема PostgreSQL 16
|
|
-- Создано: ops-agent 2026-08-05
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- Пользователи
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
password TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'buyer' CHECK (role IN ('owner','admin','buyer','subscriber')),
|
|
telegram_id BIGINT,
|
|
email_verified_at TIMESTAMPTZ,
|
|
refresh_token TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Категории работ
|
|
CREATE TABLE categories (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
sort_order INT NOT NULL DEFAULT 0
|
|
);
|
|
|
|
-- Работы
|
|
CREATE TABLE works (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
title TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
medium TEXT, -- техника: масло, акварель...
|
|
dimensions TEXT, -- размер: 60x80 см
|
|
year INT,
|
|
price NUMERIC(12,2),
|
|
status TEXT NOT NULL DEFAULT 'draft'
|
|
CHECK (status IN ('draft','published','sold')),
|
|
auction_mode TEXT NOT NULL DEFAULT 'none'
|
|
CHECK (auction_mode IN ('none','primary','secondary')),
|
|
telegram_message_id BIGINT,
|
|
created_by UUID REFERENCES users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Категории работ (M2M)
|
|
CREATE TABLE work_categories (
|
|
work_id UUID REFERENCES works(id) ON DELETE CASCADE,
|
|
category_id INT REFERENCES categories(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (work_id, category_id)
|
|
);
|
|
|
|
-- Медиа
|
|
CREATE TABLE media (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
work_id UUID REFERENCES works(id) ON DELETE CASCADE,
|
|
url TEXT NOT NULL,
|
|
thumb_url TEXT,
|
|
type TEXT NOT NULL DEFAULT 'image' CHECK (type IN ('image','video')),
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Аукционы
|
|
CREATE TABLE auctions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
work_id UUID REFERENCES works(id) ON DELETE CASCADE,
|
|
type TEXT NOT NULL CHECK (type IN ('primary','secondary')),
|
|
start_price NUMERIC(12,2) NOT NULL,
|
|
current_price NUMERIC(12,2) NOT NULL,
|
|
reserve_price NUMERIC(12,2),
|
|
min_step NUMERIC(12,2) NOT NULL DEFAULT 500,
|
|
start_at TIMESTAMPTZ NOT NULL,
|
|
end_at TIMESTAMPTZ NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending','active','ended','sold','cancelled')),
|
|
winner_id UUID REFERENCES users(id),
|
|
created_by UUID REFERENCES users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Ставки
|
|
CREATE TABLE bids (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
auction_id UUID REFERENCES auctions(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES users(id),
|
|
amount NUMERIC(12,2) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Подписки
|
|
CREATE TABLE subscriptions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email TEXT NOT NULL UNIQUE,
|
|
name TEXT,
|
|
confirm_token TEXT NOT NULL DEFAULT gen_random_uuid()::text,
|
|
unsub_token TEXT NOT NULL DEFAULT gen_random_uuid()::text,
|
|
confirmed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Категории подписки (опционально)
|
|
CREATE TABLE subscription_categories (
|
|
subscription_id UUID REFERENCES subscriptions(id) ON DELETE CASCADE,
|
|
category_id INT REFERENCES categories(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (subscription_id, category_id)
|
|
);
|
|
|
|
-- История рассылок
|
|
CREATE TABLE newsletter_sends (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
subject TEXT NOT NULL,
|
|
body_html TEXT NOT NULL,
|
|
recipient_count INT NOT NULL DEFAULT 0,
|
|
sent_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Комментарии
|
|
CREATE TABLE comments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
work_id UUID REFERENCES works(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES users(id),
|
|
body TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending','approved','rejected')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Лайки
|
|
CREATE TABLE likes (
|
|
work_id UUID REFERENCES works(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (work_id, user_id)
|
|
);
|
|
|
|
-- Отзывы
|
|
CREATE TABLE reviews (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
work_id UUID REFERENCES works(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
|
|
body TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (work_id, user_id)
|
|
);
|
|
|
|
-- Уведомления владельца
|
|
CREATE TABLE notifications (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
owner_id UUID REFERENCES users(id),
|
|
type TEXT NOT NULL
|
|
CHECK (type IN ('comment','like','review','bid','sale','new_bid_lost')),
|
|
entity_id UUID, -- id комментария / лайка / ставки / аукциона
|
|
preview_text TEXT, -- «Иван: "Красиво!" (60 символов)»
|
|
admin_url TEXT, -- /admin/comments/uuid
|
|
read_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Telegram-синхронизация
|
|
CREATE TABLE telegram_sync (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
work_id UUID REFERENCES works(id) ON DELETE CASCADE,
|
|
channel_id TEXT NOT NULL,
|
|
message_id BIGINT NOT NULL,
|
|
direction TEXT NOT NULL DEFAULT 'push' CHECK (direction IN ('push','pull')),
|
|
synced_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Индексы
|
|
CREATE INDEX idx_works_status ON works(status);
|
|
CREATE INDEX idx_works_slug ON works(slug);
|
|
CREATE INDEX idx_auctions_status ON auctions(status);
|
|
CREATE INDEX idx_auctions_end_at ON auctions(end_at);
|
|
CREATE INDEX idx_bids_auction ON bids(auction_id, created_at DESC);
|
|
CREATE INDEX idx_comments_work ON comments(work_id, status);
|
|
CREATE INDEX idx_notifications ON notifications(owner_id, read_at, created_at DESC);
|
|
|
|
-- View: работы с агрегатами
|
|
CREATE VIEW works_extended AS
|
|
SELECT
|
|
w.*,
|
|
COUNT(DISTINCT l.user_id) AS likes_count,
|
|
COUNT(DISTINCT c.id)
|
|
FILTER (WHERE c.status='approved') AS comments_count,
|
|
AVG(r.rating) AS avg_rating,
|
|
COUNT(DISTINCT r.id) AS reviews_count,
|
|
m.url AS cover_url,
|
|
m.thumb_url AS cover_thumb
|
|
FROM works w
|
|
LEFT JOIN likes l ON l.work_id = w.id
|
|
LEFT JOIN comments c ON c.work_id = w.id
|
|
LEFT JOIN reviews r ON r.work_id = w.id
|
|
LEFT JOIN LATERAL (
|
|
SELECT url, thumb_url FROM media
|
|
WHERE work_id = w.id ORDER BY sort_order LIMIT 1
|
|
) m ON true
|
|
GROUP BY w.id, m.url, m.thumb_url;
|