43 lines
1.4 KiB
SQL
43 lines
1.4 KiB
SQL
-- +goose Up
|
|
-- Activation de l'extension pour générer des UUID côté base si besoin.
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- Table des utilisateurs.
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Table des refresh tokens (stockés hashés, révocables).
|
|
CREATE TABLE refresh_tokens (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
revoked BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id);
|
|
CREATE INDEX idx_refresh_tokens_token_hash ON refresh_tokens(token_hash);
|
|
|
|
-- Table des documents PDF.
|
|
CREATE TABLE documents (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
file_path TEXT NOT NULL,
|
|
size BIGINT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_documents_user_id ON documents(user_id);
|
|
|
|
-- +goose Down
|
|
DROP TABLE documents;
|
|
DROP TABLE refresh_tokens;
|
|
DROP TABLE users;
|