44 lines
1.5 KiB
SQL
44 lines
1.5 KiB
SQL
-- mstfyldz Journal PostgreSQL Schema for psql
|
|
|
|
-- Create Custom Enum Types (if not existing)
|
|
DO $$ BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'post_type') THEN
|
|
CREATE TYPE post_type AS ENUM ('notebook', 'sticky', 'polaroid', 'code', 'audio');
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'mood_type') THEN
|
|
CREATE TYPE mood_type AS ENUM ('focus', 'night', 'spark', 'calm', 'visual');
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Create Posts Table
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
type post_type NOT NULL,
|
|
date VARCHAR(50) NOT NULL,
|
|
timestamp VARCHAR(50) NOT NULL,
|
|
mood mood_type NOT NULL,
|
|
mood_label VARCHAR(100) NOT NULL,
|
|
title TEXT,
|
|
content TEXT NOT NULL,
|
|
margin_notes TEXT[] DEFAULT '{}',
|
|
tags TEXT[] DEFAULT '{}',
|
|
likes INT DEFAULT 0 NOT NULL,
|
|
highlight_words TEXT[] DEFAULT '{}',
|
|
author_note TEXT,
|
|
image_url TEXT,
|
|
image_caption TEXT,
|
|
code_snippet TEXT,
|
|
code_language VARCHAR(50),
|
|
audio_duration VARCHAR(20),
|
|
audio_title VARCHAR(255),
|
|
audio_url TEXT,
|
|
stamped_text VARCHAR(50),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
|
|
);
|
|
|
|
-- Performance Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_posts_mood ON posts(mood);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_type ON posts(type);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at DESC);
|