DATABASE MANAGEMENT - СТАНДАРТ

{service}_{env}

Примеры:
- platform_ui_prod       ← Production
- platform_ui_dev        ← Development
- platform_ui_test       ← Testing

{entity} (plural, snake_case)

Примеры:
- users
- tasks
- chat_history
- file_permissions

{field_name} (snake_case)

Примеры:
- created_at
- updated_at
- user_id
- is_active

id              SERIAL PRIMARY KEY
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP

deleted_at      TIMESTAMP          -- soft delete
created_by      INT                -- кто создал
updated_by      INT                -- кто обновил

migrations/
├── 001_initial_schema.sql
├── 002_add_roles_table.sql
├── 003_add_permissions.sql
└── ...

Naming: {number}_{description}.sql
Number: 3 цифры, инкрементально

-- UP
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- DOWN (в конце файла, закомментировано)
-- DROP TABLE IF EXISTS users;

idx_{table}_{column}

Примеры:
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_tasks_user_id ON tasks(user_id);

pg_dump -U postgres platform_ui_prod > backup_before_migration_$(date +%Y%m%d_%H%M%S).sql

platform_ui_app     -- для приложения (CRUD)
platform_ui_ro      -- read-only (для мониторинга)
postgres            -- superuser (только для миграций)

-- Приложение
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES TO platform_ui_app;

-- Read-only
GRANT SELECT ON ALL TABLES TO platform_ui_ro;

НЕ в git:
- .env
- credentials.md

В git:
- .env.example (без паролей)
- schema.sql (структура БЕЗ данных)

platform_ui

id              SERIAL PRIMARY KEY
email           VARCHAR(255) NOT NULL UNIQUE
username        VARCHAR(100) NOT NULL UNIQUE
password_hash   VARCHAR(255) NOT NULL
role            VARCHAR(50) NOT NULL
totp_secret     VARCHAR(100)
is_active       BOOLEAN DEFAULT TRUE
last_login      TIMESTAMP
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP

id              SERIAL PRIMARY KEY
name            VARCHAR(100) NOT NULL UNIQUE
display_name    VARCHAR(255)
config          JSONB NOT NULL
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP

id              SERIAL PRIMARY KEY
title           VARCHAR(500) NOT NULL
description     TEXT
status          VARCHAR(50) NOT NULL
priority        VARCHAR(50)
assigned_to     INT REFERENCES users(id)
created_by      INT REFERENCES users(id)
due_date        TIMESTAMP
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP

id              SERIAL PRIMARY KEY
user_id         INT REFERENCES users(id)
role_context    VARCHAR(50)
message         TEXT NOT NULL
response        TEXT NOT NULL
tokens_used     INT
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP

id              SERIAL PRIMARY KEY
user_id         INT REFERENCES users(id)
token           VARCHAR(500) NOT NULL UNIQUE
expires_at      TIMESTAMP NOT NULL
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP

id              SERIAL PRIMARY KEY
user_id         INT REFERENCES users(id)
action          VARCHAR(100) NOT NULL
resource_type   VARCHAR(100)
resource_id     INT
details         JSONB
ip_address      VARCHAR(50)
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_assigned_to ON tasks(assigned_to);
CREATE INDEX idx_chat_history_user_id ON chat_history(user_id);
CREATE INDEX idx_sessions_token ON sessions(token);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
CREATE INDEX idx_audit_log_user_id ON audit_log(user_id);
CREATE INDEX idx_audit_log_created_at ON audit_log(created_at);

# Development
psql -U postgres -d platform_ui_dev -f migrations/001_initial_schema.sql

# Production (с бэкапом!)
pg_dump -U postgres platform_ui_prod > backup_before_001.sql
psql -U postgres -d platform_ui_prod -f migrations/001_initial_schema.sql

# Раскомментировать DOWN секцию в миграции
psql -U postgres -d platform_ui_prod -f migrations/001_initial_schema.sql
# Восстановить из бэкапа
psql -U postgres -d platform_ui_prod < backup_before_001.sql