76 lines
2.8 KiB
PL/PgSQL
76 lines
2.8 KiB
PL/PgSQL
\c dcntrlized;
|
|
|
|
CREATE SCHEMA IF NOT EXISTS keepnote;
|
|
|
|
-- ============================================================================
|
|
-- Auth (D3/D5, planning/platform/shared-auth/README.md): identity moved to
|
|
-- the shared dcntrlized-auth service. The per-service `users` table is
|
|
-- DISABLED below; data tables keep a plain-UUID user_id (no FK) that points
|
|
-- at auth.users(id). Existing dev rows are re-pointed by 002_shared_auth.sql.
|
|
-- ============================================================================
|
|
-- CREATE TABLE IF NOT EXISTS keepnote.users (
|
|
-- id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
-- username VARCHAR(255) NOT NULL UNIQUE,
|
|
-- password_hash VARCHAR(255) NOT NULL,
|
|
-- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
-- );
|
|
|
|
CREATE TABLE IF NOT EXISTS keepnote.notes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT,
|
|
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
-- user_id is a plain UUID (platform account id, auth.users) -- no FK (D5).
|
|
-- FOREIGN KEY (user_id) REFERENCES keepnote.users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS keepnote.tags (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS keepnote.note_tags (
|
|
note_id UUID NOT NULL,
|
|
tag_id UUID NOT NULL,
|
|
PRIMARY KEY (note_id, tag_id),
|
|
FOREIGN KEY (note_id) REFERENCES keepnote.notes(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (tag_id) REFERENCES keepnote.tags(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Added IF NOT EXISTS to indexes
|
|
CREATE INDEX IF NOT EXISTS idx_user_id ON keepnote.notes(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_note_id ON keepnote.note_tags(note_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tag_id ON keepnote.note_tags(tag_id);
|
|
|
|
-- Placed the function inside the keepnote schema
|
|
CREATE OR REPLACE FUNCTION keepnote.update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.modified = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Drop the trigger if it exists, then recreate it
|
|
DROP TRIGGER IF EXISTS update_notes_modtime ON keepnote.notes;
|
|
CREATE TRIGGER update_notes_modtime
|
|
BEFORE UPDATE ON keepnote.notes
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION keepnote.update_updated_at_column();
|
|
|
|
-- Idempotent User Creation using a DO block
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'keepnote_user') THEN
|
|
CREATE USER keepnote_user WITH PASSWORD 'keepnote';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- Grants are naturally idempotent (they won't error if re-run)
|
|
GRANT CONNECT ON DATABASE dcntrlized TO keepnote_user;
|
|
GRANT USAGE ON SCHEMA keepnote TO keepnote_user;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA keepnote TO keepnote_user;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA keepnote GRANT ALL ON TABLES TO keepnote_user; |