Cedric Abonnel
31 lines
1.2 KiB
SQL
31 lines
1.2 KiB
SQL
-- Réactions visiteurs (cookie anti-doublon)
|
|
CREATE TABLE article_reactions (
|
|
id SERIAL PRIMARY KEY,
|
|
article_uuid TEXT NOT NULL,
|
|
reaction_type TEXT NOT NULL CHECK (reaction_type IN ('useful', 'important', 'interesting')),
|
|
visitor_hash TEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
UNIQUE (article_uuid, reaction_type, visitor_hash)
|
|
);
|
|
CREATE INDEX ON article_reactions (article_uuid);
|
|
|
|
-- Commentaires avec vérification par email
|
|
CREATE TABLE comments (
|
|
id SERIAL PRIMARY KEY,
|
|
article_uuid TEXT NOT NULL,
|
|
author_name TEXT NOT NULL,
|
|
author_email TEXT NOT NULL,
|
|
content TEXT NOT NULL CHECK (LENGTH(content) <= 2000),
|
|
verify_token TEXT,
|
|
verification_code TEXT,
|
|
verify_attempts INTEGER NOT NULL DEFAULT 0,
|
|
verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
published BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
ip_address TEXT,
|
|
user_agent TEXT
|
|
);
|
|
CREATE INDEX ON comments (article_uuid, verified, published);
|
|
CREATE INDEX ON comments (verify_token)
|
|
WHERE verified = FALSE AND verify_token IS NOT NULL;
|