mirror of
https://github.com/idrainformatica/PecFlow.git
synced 2026-06-16 12:45:42 +02:00
359 lines
19 KiB
Python
359 lines
19 KiB
Python
"""Initial schema – tutte le tabelle PEChub Fase 1
|
||
|
||
Revision ID: 0001
|
||
Revises:
|
||
Create Date: 2026-03-18 00:00:00.000000
|
||
"""
|
||
|
||
from alembic import op
|
||
|
||
revision = "0001"
|
||
down_revision = None
|
||
branch_labels = None
|
||
depends_on = None
|
||
|
||
|
||
def upgrade() -> None:
|
||
# Esegui l'intero schema come SQL puro (più affidabile con ENUM types)
|
||
op.execute("""
|
||
-- ── Estensioni ───────────────────────────────────────────────────────
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
||
""")
|
||
|
||
# ── ENUM types ────────────────────────────────────────────────────────────
|
||
op.execute("CREATE TYPE user_role AS ENUM ('super_admin','admin','supervisor','operator','readonly')")
|
||
op.execute("CREATE TYPE mailbox_status AS ENUM ('active','paused','error','deleted')")
|
||
op.execute("CREATE TYPE pec_direction AS ENUM ('inbound','outbound')")
|
||
op.execute("CREATE TYPE pec_state AS ENUM ('draft','queued','sent','accepted','delivered','anomaly','failed','received')")
|
||
op.execute("CREATE TYPE pec_msg_type AS ENUM ('posta_certificata','accettazione','non_accettazione','presa_in_carico','avvenuta_consegna','mancata_consegna','errore_consegna','preavviso_mancata_consegna','rilevazione_virus','unknown')")
|
||
op.execute("CREATE TYPE send_job_status AS ENUM ('pending','sending','sent','failed','retrying')")
|
||
op.execute("CREATE TYPE archival_status AS ENUM ('pending','building_sip','uploading','uploaded','confirmed','rejected','failed')")
|
||
|
||
# ── 1. TENANTS ────────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE tenants (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
slug VARCHAR(63) NOT NULL UNIQUE,
|
||
name VARCHAR(255) NOT NULL,
|
||
plan VARCHAR(50) NOT NULL DEFAULT 'starter',
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
max_mailboxes INT NOT NULL DEFAULT 5,
|
||
max_users INT NOT NULL DEFAULT 10,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_tenants_slug ON tenants (slug)")
|
||
|
||
# ── 2. USERS ──────────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE users (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
email VARCHAR(255) NOT NULL,
|
||
password_hash TEXT NOT NULL,
|
||
full_name VARCHAR(255) NOT NULL,
|
||
role user_role NOT NULL DEFAULT 'operator',
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
totp_secret TEXT,
|
||
totp_enabled BOOLEAN NOT NULL DEFAULT FALSE,
|
||
last_login_at TIMESTAMPTZ,
|
||
failed_login_count INT NOT NULL DEFAULT 0,
|
||
locked_until TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT uq_user_email_tenant UNIQUE (tenant_id, email)
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_users_tenant ON users (tenant_id)")
|
||
op.execute("CREATE INDEX idx_users_email ON users (email)")
|
||
op.execute("ALTER TABLE users ENABLE ROW LEVEL SECURITY")
|
||
op.execute("""
|
||
CREATE POLICY users_tenant_isolation ON users
|
||
USING (tenant_id = current_setting('app.current_tenant_id', TRUE)::UUID)
|
||
""")
|
||
|
||
# ── 3. REFRESH TOKENS ─────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE refresh_tokens (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
token_hash TEXT NOT NULL UNIQUE,
|
||
issued_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
expires_at TIMESTAMPTZ NOT NULL,
|
||
revoked_at TIMESTAMPTZ,
|
||
user_agent TEXT,
|
||
ip_address INET
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_rt_user ON refresh_tokens (user_id)")
|
||
op.execute("CREATE INDEX idx_rt_expires ON refresh_tokens (expires_at) WHERE revoked_at IS NULL")
|
||
|
||
# ── 4. MAILBOXES ──────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE mailboxes (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
email_address VARCHAR(255) NOT NULL,
|
||
display_name VARCHAR(255),
|
||
provider VARCHAR(100),
|
||
imap_host_enc TEXT NOT NULL,
|
||
imap_port_enc TEXT NOT NULL,
|
||
imap_user_enc TEXT NOT NULL,
|
||
imap_pass_enc TEXT NOT NULL,
|
||
imap_use_ssl BOOLEAN NOT NULL DEFAULT TRUE,
|
||
smtp_host_enc TEXT NOT NULL,
|
||
smtp_port_enc TEXT NOT NULL,
|
||
smtp_user_enc TEXT NOT NULL,
|
||
smtp_pass_enc TEXT NOT NULL,
|
||
smtp_use_tls BOOLEAN NOT NULL DEFAULT TRUE,
|
||
status mailbox_status NOT NULL DEFAULT 'active',
|
||
last_sync_at TIMESTAMPTZ,
|
||
last_sync_uid BIGINT,
|
||
sync_error_msg TEXT,
|
||
sync_error_count INT NOT NULL DEFAULT 0,
|
||
created_by UUID REFERENCES users(id),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT uq_mailbox_email_tenant UNIQUE (tenant_id, email_address)
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_mailboxes_tenant ON mailboxes (tenant_id)")
|
||
op.execute("CREATE INDEX idx_mailboxes_status ON mailboxes (status) WHERE status = 'active'")
|
||
op.execute("ALTER TABLE mailboxes ENABLE ROW LEVEL SECURITY")
|
||
op.execute("""
|
||
CREATE POLICY mailboxes_tenant_isolation ON mailboxes
|
||
USING (tenant_id = current_setting('app.current_tenant_id', TRUE)::UUID)
|
||
""")
|
||
|
||
# ── 5. MESSAGES ───────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE messages (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
mailbox_id UUID NOT NULL REFERENCES mailboxes(id) ON DELETE CASCADE,
|
||
message_id_header TEXT,
|
||
imap_uid BIGINT,
|
||
imap_folder VARCHAR(255) NOT NULL DEFAULT 'INBOX',
|
||
direction pec_direction NOT NULL,
|
||
pec_type pec_msg_type NOT NULL DEFAULT 'posta_certificata',
|
||
state pec_state NOT NULL,
|
||
subject TEXT,
|
||
from_address VARCHAR(255),
|
||
to_addresses TEXT[],
|
||
cc_addresses TEXT[],
|
||
sent_at TIMESTAMPTZ,
|
||
received_at TIMESTAMPTZ,
|
||
size_bytes BIGINT,
|
||
body_text TEXT,
|
||
body_html TEXT,
|
||
has_attachments BOOLEAN NOT NULL DEFAULT FALSE,
|
||
parent_message_id UUID REFERENCES messages(id),
|
||
is_read BOOLEAN NOT NULL DEFAULT FALSE,
|
||
is_starred BOOLEAN NOT NULL DEFAULT FALSE,
|
||
is_archived BOOLEAN NOT NULL DEFAULT FALSE,
|
||
archived_at TIMESTAMPTZ,
|
||
raw_eml_path TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_messages_tenant ON messages (tenant_id)")
|
||
op.execute("CREATE INDEX idx_messages_mailbox ON messages (mailbox_id)")
|
||
op.execute("CREATE INDEX idx_messages_state ON messages (state)")
|
||
op.execute("CREATE INDEX idx_messages_received_at ON messages (received_at DESC)")
|
||
op.execute("CREATE INDEX idx_messages_imap_uid ON messages (mailbox_id, imap_uid)")
|
||
op.execute("CREATE INDEX idx_messages_parent ON messages (parent_message_id) WHERE parent_message_id IS NOT NULL")
|
||
op.execute("CREATE INDEX idx_messages_subject_fts ON messages USING GIN (to_tsvector('italian', COALESCE(subject, '')))")
|
||
op.execute("ALTER TABLE messages ENABLE ROW LEVEL SECURITY")
|
||
op.execute("""
|
||
CREATE POLICY messages_tenant_isolation ON messages
|
||
USING (tenant_id = current_setting('app.current_tenant_id', TRUE)::UUID)
|
||
""")
|
||
|
||
# ── 6. ATTACHMENTS ────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE attachments (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
||
filename VARCHAR(512) NOT NULL,
|
||
content_type VARCHAR(255),
|
||
size_bytes BIGINT,
|
||
storage_path TEXT NOT NULL,
|
||
checksum_sha256 CHAR(64),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_attachments_message ON attachments (message_id)")
|
||
|
||
# ── 7. SEND_JOBS ──────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE send_jobs (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
mailbox_id UUID NOT NULL REFERENCES mailboxes(id),
|
||
message_id UUID REFERENCES messages(id),
|
||
status send_job_status NOT NULL DEFAULT 'pending',
|
||
attempt_count INT NOT NULL DEFAULT 0,
|
||
max_attempts INT NOT NULL DEFAULT 5,
|
||
next_retry_at TIMESTAMPTZ,
|
||
last_error TEXT,
|
||
queued_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
sent_at TIMESTAMPTZ,
|
||
created_by UUID REFERENCES users(id)
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_sendjobs_tenant ON send_jobs (tenant_id)")
|
||
op.execute("CREATE INDEX idx_sendjobs_status ON send_jobs (status, next_retry_at) WHERE status IN ('pending','retrying')")
|
||
|
||
# ── 8. ARCHIVAL ───────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE archival_batches (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
conservatore_id VARCHAR(100) NOT NULL,
|
||
status archival_status NOT NULL DEFAULT 'pending',
|
||
sip_path TEXT,
|
||
sip_checksum CHAR(64),
|
||
versamento_id TEXT,
|
||
rdv_received_at TIMESTAMPTZ,
|
||
rdv_path TEXT,
|
||
rdv_checksum CHAR(64),
|
||
attempt_count INT NOT NULL DEFAULT 0,
|
||
max_attempts INT NOT NULL DEFAULT 3,
|
||
next_retry_at TIMESTAMPTZ,
|
||
last_error TEXT,
|
||
period_from DATE NOT NULL,
|
||
period_to DATE NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_archival_tenant ON archival_batches (tenant_id)")
|
||
op.execute("CREATE INDEX idx_archival_status ON archival_batches (status, next_retry_at)")
|
||
|
||
op.execute("""
|
||
CREATE TABLE archival_batch_messages (
|
||
batch_id UUID NOT NULL REFERENCES archival_batches(id) ON DELETE CASCADE,
|
||
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (batch_id, message_id)
|
||
)
|
||
""")
|
||
|
||
op.execute("""
|
||
CREATE TABLE archival_dips (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
batch_id UUID REFERENCES archival_batches(id),
|
||
requested_by UUID REFERENCES users(id),
|
||
dip_path TEXT,
|
||
status VARCHAR(50) NOT NULL DEFAULT 'requested',
|
||
requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
received_at TIMESTAMPTZ
|
||
)
|
||
""")
|
||
|
||
# ── 9. AUDIT_LOG ──────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE audit_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
tenant_id UUID REFERENCES tenants(id),
|
||
user_id UUID REFERENCES users(id),
|
||
action VARCHAR(100) NOT NULL,
|
||
resource_type VARCHAR(100),
|
||
resource_id UUID,
|
||
ip_address INET,
|
||
user_agent TEXT,
|
||
payload JSONB,
|
||
outcome VARCHAR(20) NOT NULL DEFAULT 'success',
|
||
occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_audit_tenant_date ON audit_log (tenant_id, occurred_at DESC)")
|
||
op.execute("CREATE INDEX idx_audit_user ON audit_log (user_id, occurred_at DESC)")
|
||
op.execute("CREATE INDEX idx_audit_action ON audit_log (action)")
|
||
op.execute("ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY")
|
||
op.execute("CREATE POLICY audit_no_delete ON audit_log FOR DELETE USING (FALSE)")
|
||
op.execute("CREATE POLICY audit_no_update ON audit_log FOR UPDATE USING (FALSE)")
|
||
op.execute("""
|
||
CREATE POLICY audit_tenant_read ON audit_log FOR SELECT
|
||
USING (tenant_id = current_setting('app.current_tenant_id', TRUE)::UUID)
|
||
""")
|
||
|
||
# ── 10. LABELS ────────────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE labels (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
name VARCHAR(100) NOT NULL,
|
||
color CHAR(7),
|
||
CONSTRAINT uq_label_name_tenant UNIQUE (tenant_id, name)
|
||
)
|
||
""")
|
||
|
||
op.execute("""
|
||
CREATE TABLE message_labels (
|
||
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
||
label_id UUID NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (message_id, label_id)
|
||
)
|
||
""")
|
||
|
||
# ── 11. MAILBOX_PERMISSIONS (Fase 1-A) ────────────────────────────────────
|
||
op.execute("""
|
||
CREATE TABLE mailbox_permissions (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
mailbox_id UUID NOT NULL REFERENCES mailboxes(id) ON DELETE CASCADE,
|
||
can_read BOOLEAN NOT NULL DEFAULT TRUE,
|
||
can_send BOOLEAN NOT NULL DEFAULT FALSE,
|
||
can_manage BOOLEAN NOT NULL DEFAULT FALSE,
|
||
granted_by UUID REFERENCES users(id),
|
||
granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT uq_perm_user_mailbox UNIQUE (user_id, mailbox_id)
|
||
)
|
||
""")
|
||
op.execute("CREATE INDEX idx_mbperm_user ON mailbox_permissions (user_id)")
|
||
op.execute("CREATE INDEX idx_mbperm_mailbox ON mailbox_permissions (mailbox_id)")
|
||
op.execute("CREATE INDEX idx_mbperm_tenant ON mailbox_permissions (tenant_id)")
|
||
|
||
# ── Trigger updated_at ────────────────────────────────────────────────────
|
||
op.execute("""
|
||
CREATE OR REPLACE FUNCTION set_updated_at()
|
||
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
|
||
$$
|
||
""")
|
||
for table in ["tenants", "users", "mailboxes", "messages", "archival_batches"]:
|
||
op.execute(f"""
|
||
CREATE TRIGGER trg_{table}_updated_at
|
||
BEFORE UPDATE ON {table}
|
||
FOR EACH ROW EXECUTE FUNCTION set_updated_at()
|
||
""")
|
||
|
||
|
||
def downgrade() -> None:
|
||
# Rimuovi trigger
|
||
for table in ["tenants", "users", "mailboxes", "messages", "archival_batches"]:
|
||
op.execute(f"DROP TRIGGER IF EXISTS trg_{table}_updated_at ON {table}")
|
||
op.execute("DROP FUNCTION IF EXISTS set_updated_at()")
|
||
|
||
# Rimuovi tabelle (ordine inverso per FK)
|
||
for table in [
|
||
"mailbox_permissions", "message_labels", "labels",
|
||
"audit_log", "archival_dips", "archival_batch_messages",
|
||
"archival_batches", "send_jobs", "attachments", "messages",
|
||
"mailboxes", "refresh_tokens", "users", "tenants",
|
||
]:
|
||
op.execute(f"DROP TABLE IF EXISTS {table} CASCADE")
|
||
|
||
# Rimuovi enum types
|
||
for enum_name in [
|
||
"archival_status", "send_job_status", "pec_msg_type",
|
||
"pec_state", "pec_direction", "mailbox_status", "user_role",
|
||
]:
|
||
op.execute(f"DROP TYPE IF EXISTS {enum_name}")
|