39 lines
1.2 KiB
SQL
39 lines
1.2 KiB
SQL
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE posts (
|
|
id SERIAL PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
slug VARCHAR(255) UNIQUE NOT NULL,
|
|
markdown_content TEXT NOT NULL,
|
|
compiled_content TEXT NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft, published
|
|
published_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
author_id INTEGER REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE tags (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(50) UNIQUE NOT NULL,
|
|
slug VARCHAR(50) UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE posts_tags (
|
|
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
|
|
tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (post_id, tag_id)
|
|
);
|
|
|
|
CREATE TABLE images (
|
|
id SERIAL PRIMARY KEY,
|
|
filename VARCHAR(255) NOT NULL,
|
|
url VARCHAR(255) NOT NULL,
|
|
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
|
|
uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
); |