31 lines
1.3 KiB
SQL
31 lines
1.3 KiB
SQL
|
-- Initial database setup for Imphnen Backend
|
||
|
|
-- This file is executed when PostgreSQL container starts for the first time
|
||
|
|
|
||
|
|
-- Create extensions
|
||
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
|
|
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
|
||
|
|
|
||
|
|
-- Create schemas for better organization
|
||
|
|
CREATE SCHEMA IF NOT EXISTS auth;
|
||
|
|
CREATE SCHEMA IF NOT EXISTS content;
|
||
|
|
CREATE SCHEMA IF NOT EXISTS gacha;
|
||
|
|
CREATE SCHEMA IF NOT EXISTS mentoring;
|
||
|
|
|
||
|
|
-- Set default privileges for future tables
|
||
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO PUBLIC;
|
||
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA content GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO PUBLIC;
|
||
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA gacha GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO PUBLIC;
|
||
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA mentoring GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO PUBLIC;
|
||
|
|
|
||
|
|
-- Create indexes for better performance
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_created_at ON users(created_at) WHERE created_at IS NOT NULL;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_updated_at ON users(updated_at) WHERE updated_at IS NOT NULL;
|
||
|
|
|
||
|
|
-- Grant necessary permissions
|
||
|
|
GRANT USAGE ON SCHEMA auth TO PUBLIC;
|
||
|
|
GRANT USAGE ON SCHEMA content TO PUBLIC;
|
||
|
|
GRANT USAGE ON SCHEMA gacha TO PUBLIC;
|
||
|
|
GRANT USAGE ON SCHEMA mentoring TO PUBLIC;
|
||
|
|
|
||
|
|
-- Set timezone
|
||
|
|
SET timezone = 'UTC';
|