219 lines
10 KiB
PL/PgSQL
219 lines
10 KiB
PL/PgSQL
-- Esquema inicial do Bot de Pontos v2 (ARQUITETURA.md, seção 6.2).
|
|
-- Executado pelo usuário dono do banco; o usuário da aplicação (botpontos_app)
|
|
-- recebe apenas SELECT/INSERT/UPDATE/DELETE no final.
|
|
|
|
-- +goose Up
|
|
CREATE EXTENSION IF NOT EXISTS citext;
|
|
|
|
-- +goose StatementBegin
|
|
CREATE FUNCTION set_updated_at() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
-- +goose StatementEnd
|
|
|
|
CREATE TYPE user_status AS ENUM ('pending', 'active', 'disabled');
|
|
CREATE TYPE notification_type AS ENUM ('entrada2', 'saida2');
|
|
CREATE TYPE notification_status AS ENUM ('scheduled', 'sending', 'sent', 'failed', 'cancelled', 'expired');
|
|
CREATE TYPE web_role AS ENUM ('owner', 'admin', 'viewer');
|
|
CREATE TYPE audit_source AS ENUM ('telegram', 'web', 'worker', 'cli');
|
|
CREATE TYPE day_status AS ENUM ('awaiting', 'no_points', 'working', 'not_working', 'finished');
|
|
|
|
-- Pessoa que usa o bot
|
|
CREATE TABLE users (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
telegram_chat_id bigint NOT NULL UNIQUE,
|
|
display_name text NOT NULL,
|
|
status user_status NOT NULL DEFAULT 'pending',
|
|
is_bot_admin boolean NOT NULL DEFAULT false,
|
|
notifications_enabled boolean NOT NULL DEFAULT false,
|
|
-- motivo da pausa dos lembretes, exibido no /status
|
|
notifications_paused_reason text CHECK (notifications_paused_reason IN ('manual', 'api_failures', 'admin', 'user_disabled')),
|
|
timezone text NOT NULL DEFAULT 'America/Sao_Paulo',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Regras de jornada e exceção opcional da janela de consulta (9.8 e 9.14)
|
|
CREATE TABLE work_schedules (
|
|
user_id bigint PRIMARY KEY REFERENCES users(id),
|
|
lunch_minutes int NOT NULL DEFAULT 60 CHECK (lunch_minutes BETWEEN 15 AND 180),
|
|
daily_work_minutes int NOT NULL DEFAULT 480 CHECK (daily_work_minutes BETWEEN 60 AND 720),
|
|
entrada2_lead_minutes int NOT NULL DEFAULT 5 CHECK (entrada2_lead_minutes BETWEEN 0 AND 60),
|
|
saida2_lead_minutes int NOT NULL DEFAULT 5 CHECK (saida2_lead_minutes BETWEEN 0 AND 60),
|
|
query_window_start time,
|
|
query_window_end time,
|
|
day_check_deadline time,
|
|
no_points_recheck_until time,
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT work_schedules_window_all_or_none CHECK (
|
|
num_nulls(query_window_start, query_window_end, day_check_deadline, no_points_recheck_until) IN (0, 4)
|
|
)
|
|
);
|
|
|
|
-- Credencial PontoMais (senha cifrada com AES-GCM na aplicação)
|
|
CREATE TABLE point_credentials (
|
|
user_id bigint PRIMARY KEY REFERENCES users(id),
|
|
api_email text NOT NULL,
|
|
encrypted_password bytea NOT NULL,
|
|
key_version smallint NOT NULL,
|
|
last_validated_at timestamptz,
|
|
last_error text,
|
|
consecutive_failures int NOT NULL DEFAULT 0,
|
|
last_failure_at timestamptz,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE notifications (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
user_id bigint NOT NULL REFERENCES users(id),
|
|
reference_date date NOT NULL,
|
|
type notification_type NOT NULL,
|
|
scheduled_at timestamptz NOT NULL, -- quando o aviso deve sair
|
|
predicted_at timestamptz NOT NULL, -- horário previsto do ponto (vai na mensagem)
|
|
status notification_status NOT NULL DEFAULT 'scheduled',
|
|
attempts int NOT NULL DEFAULT 0,
|
|
locked_at timestamptz,
|
|
sent_at timestamptz,
|
|
telegram_message_id bigint,
|
|
error_message text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (user_id, reference_date, type)
|
|
);
|
|
CREATE INDEX notifications_due_idx ON notifications (scheduled_at) WHERE status = 'scheduled';
|
|
CREATE INDEX notifications_sending_idx ON notifications (locked_at) WHERE status = 'sending';
|
|
CREATE INDEX notifications_reference_date_idx ON notifications (reference_date);
|
|
|
|
CREATE TABLE web_accounts (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
username citext NOT NULL UNIQUE,
|
|
password_hash text NOT NULL,
|
|
role web_role NOT NULL,
|
|
user_id bigint REFERENCES users(id),
|
|
failed_login_count int NOT NULL DEFAULT 0,
|
|
locked_until timestamptz,
|
|
disabled_at timestamptz,
|
|
last_login_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE web_sessions (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
account_id bigint NOT NULL REFERENCES web_accounts(id),
|
|
token_hash bytea NOT NULL UNIQUE,
|
|
csrf_token_hash bytea NOT NULL,
|
|
expires_at timestamptz NOT NULL,
|
|
last_seen_at timestamptz NOT NULL,
|
|
revoked_at timestamptz,
|
|
user_agent text,
|
|
ip inet,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX web_sessions_expires_idx ON web_sessions (expires_at);
|
|
|
|
-- Feriados vindos da API e cadastrados manualmente (9.9)
|
|
CREATE TABLE holidays (
|
|
day date PRIMARY KEY,
|
|
name text NOT NULL,
|
|
source text NOT NULL CHECK (source IN ('feriados.dev', 'manual')),
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Situação de cada usuário em cada dia e próxima consulta (9.12)
|
|
CREATE TABLE user_days (
|
|
user_id bigint NOT NULL REFERENCES users(id),
|
|
day date NOT NULL,
|
|
status day_status NOT NULL DEFAULT 'awaiting',
|
|
zero_point_checks smallint NOT NULL DEFAULT 0,
|
|
last_point_count smallint NOT NULL DEFAULT 0,
|
|
next_check_at timestamptz,
|
|
resumed_by text CHECK (resumed_by IN ('ativarponto', 'entrada2', 'saida2', 'web')),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (user_id, day)
|
|
);
|
|
CREATE INDEX user_days_next_check_idx ON user_days (next_check_at) WHERE next_check_at IS NOT NULL;
|
|
|
|
-- Configurações globais editáveis pelo painel (9.13)
|
|
CREATE TABLE app_settings (
|
|
key text PRIMARY KEY,
|
|
value jsonb NOT NULL,
|
|
updated_by_account bigint REFERENCES web_accounts(id),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Estado operacional do worker (ex.: queda da PontoMais em andamento)
|
|
CREATE TABLE service_state (
|
|
key text PRIMARY KEY,
|
|
value jsonb NOT NULL,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE audit_logs (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
occurred_at timestamptz NOT NULL DEFAULT now(),
|
|
source audit_source NOT NULL,
|
|
actor_user_id bigint REFERENCES users(id),
|
|
actor_account bigint REFERENCES web_accounts(id),
|
|
target_user_id bigint REFERENCES users(id),
|
|
event_type text NOT NULL,
|
|
success boolean NOT NULL,
|
|
details jsonb NOT NULL DEFAULT '{}'
|
|
);
|
|
CREATE INDEX audit_logs_time_idx ON audit_logs (occurred_at DESC);
|
|
CREATE INDEX audit_logs_target_idx ON audit_logs (target_user_id, occurred_at DESC);
|
|
|
|
CREATE TRIGGER users_set_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
CREATE TRIGGER work_schedules_set_updated_at BEFORE UPDATE ON work_schedules FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
-- point_credentials não usa o trigger: updated_at marca só a troca da credencial (e invalida o
|
|
-- cache de sessão da PontoMais); contadores de falha não devem forçar novo login.
|
|
CREATE TRIGGER notifications_set_updated_at BEFORE UPDATE ON notifications FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
CREATE TRIGGER web_accounts_set_updated_at BEFORE UPDATE ON web_accounts FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
CREATE TRIGGER user_days_set_updated_at BEFORE UPDATE ON user_days FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
CREATE TRIGGER app_settings_set_updated_at BEFORE UPDATE ON app_settings FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
CREATE TRIGGER service_state_set_updated_at BEFORE UPDATE ON service_state FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- Permissões do usuário da aplicação (criado por postgres/init/01-create-app-role.sh).
|
|
-- As tabelas de migrations futuras herdam as permissões pelo DEFAULT PRIVILEGES.
|
|
-- +goose StatementBegin
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'botpontos_app') THEN
|
|
GRANT USAGE ON SCHEMA public TO botpontos_app;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO botpontos_app;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO botpontos_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO botpontos_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO botpontos_app;
|
|
-- A aplicação não altera o controle de versão das migrations.
|
|
REVOKE INSERT, UPDATE, DELETE ON goose_db_version FROM botpontos_app;
|
|
ELSE
|
|
RAISE NOTICE 'role botpontos_app não existe; permissões da aplicação não aplicadas';
|
|
END IF;
|
|
END;
|
|
$$;
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS audit_logs;
|
|
DROP TABLE IF EXISTS service_state;
|
|
DROP TABLE IF EXISTS app_settings;
|
|
DROP TABLE IF EXISTS user_days;
|
|
DROP TABLE IF EXISTS holidays;
|
|
DROP TABLE IF EXISTS web_sessions;
|
|
DROP TABLE IF EXISTS web_accounts;
|
|
DROP TABLE IF EXISTS notifications;
|
|
DROP TABLE IF EXISTS point_credentials;
|
|
DROP TABLE IF EXISTS work_schedules;
|
|
DROP TABLE IF EXISTS users;
|
|
DROP TYPE IF EXISTS day_status;
|
|
DROP TYPE IF EXISTS audit_source;
|
|
DROP TYPE IF EXISTS web_role;
|
|
DROP TYPE IF EXISTS notification_status;
|
|
DROP TYPE IF EXISTS notification_type;
|
|
DROP TYPE IF EXISTS user_status;
|
|
DROP FUNCTION IF EXISTS set_updated_at();
|