67 lines
No EOL
1.9 KiB
TypeScript
67 lines
No EOL
1.9 KiB
TypeScript
import pg from 'pg'
|
|
|
|
const pool = new pg.Pool({
|
|
host: process.env.POSTGRES_HOST,
|
|
port: Number(process.env.POSTGRES_PORT),
|
|
database: process.env.POSTGRES_DB,
|
|
user: process.env.POSTGRES_USER,
|
|
password: process.env.POSTGRES_PASSWORD,
|
|
})
|
|
|
|
const initDb = async () => {
|
|
try {
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
keycloak_id VARCHAR(255) UNIQUE NOT NULL,
|
|
email VARCHAR(255),
|
|
first_login_completed BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
sfid VARCHAR(255),
|
|
pid INTEGER
|
|
);
|
|
`)
|
|
|
|
await pool.query(`
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS sfid VARCHAR(255);
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS pid INTEGER;
|
|
`)
|
|
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
log_id VARCHAR(255) UNIQUE NOT NULL,
|
|
editor_sfid VARCHAR(255),
|
|
editor_pid INTEGER,
|
|
target_name VARCHAR(255),
|
|
target_pid INTEGER,
|
|
changes JSONB NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
`)
|
|
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS ban_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
log_id VARCHAR(255) UNIQUE NOT NULL,
|
|
target_pid INTEGER NOT NULL,
|
|
target_name VARCHAR(255),
|
|
editor_sfid VARCHAR(255),
|
|
editor_pid INTEGER,
|
|
ban_level INTEGER NOT NULL,
|
|
reason VARCHAR(255) NOT NULL,
|
|
mod_note TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
`)
|
|
|
|
console.log('[DB INIT]: Tables "users", "audit_logs", and "ban_logs" checked/updated successfully.')
|
|
} catch (err) {
|
|
console.error('[DB INIT ERROR]:', err)
|
|
}
|
|
}
|
|
|
|
initDb()
|
|
|
|
export const query = (text: string, params?: any[]) => pool.query(text, params) |