mporwoll/dak.c2s
1
0
Fork 0
mirror of https://github.com/complexcaresolutions/dak.c2s.git synced 2026-03-17 18:23:42 +00:00
Code Issues Projects Releases Packages Wiki Activity Actions
dak.c2s/backend/alembic/versions/062ccae5457b_initial_schema.py
CCS Admin 4649f7a082 feat: Alembic migrations, initial schema 
- Initialize Alembic with MySQL/MariaDB-targeted configuration
- Configure env.py to read DB URL from app.config.get_settings()
- Create initial migration (062ccae5457b) for all 11 tables:
  users, refresh_tokens, invitation_links, allowed_domains,
  cases, case_icd_codes, weekly_reports, yearly_summary,
  import_log, audit_log, notifications
- Include all indexes, foreign keys, check constraints, and
  MySQL text prefix index (icd(20))
- Add seed script (scripts/init_db.py) for dak.de domain whitelist
- DB apply deferred: MariaDB on Hetzner 1 not reachable from dev

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
2026-02-24 07:33:27 +00:00

447 lines
19 KiB
Python
Raw Permalink Blame History

"""initial schema
Revision ID: 062ccae5457b
Revises:
Create Date: 2026-02-24 07:31:10.140166
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
# revision identifiers, used by Alembic.
revision: str = "062ccae5457b"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
# ---- 1. users (no FK dependencies) ----
op.create_table(
"users",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("username", sa.String(100), nullable=False),
sa.Column("email", sa.String(255), nullable=False),
sa.Column("password_hash", sa.String(255), nullable=False),
sa.Column(
"role", sa.String(20), nullable=False, server_default="dak_mitarbeiter"
),
sa.Column("mfa_secret", sa.String(255), nullable=True),
sa.Column("mfa_enabled", sa.Boolean(), nullable=False, server_default="0"),
sa.Column("is_active", sa.Boolean(), nullable=False, server_default="1"),
sa.Column(
"must_change_password", sa.Boolean(), nullable=False, server_default="0"
),
sa.Column("last_login", sa.DateTime(), nullable=True),
sa.Column(
"failed_login_attempts", sa.Integer(), nullable=False, server_default="0"
),
sa.Column("locked_until", sa.DateTime(), nullable=True),
sa.Column(
"created_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.Column(
"updated_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("username", name="uk_username"),
sa.UniqueConstraint("email", name="uk_email"),
sa.CheckConstraint(
"role IN ('admin', 'dak_mitarbeiter')", name="chk_role"
),
)
# ---- 2. refresh_tokens (FK -> users) ----
op.create_table(
"refresh_tokens",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("user_id", sa.Integer(), nullable=False),
sa.Column("token_hash", sa.String(255), nullable=False),
sa.Column("expires_at", sa.DateTime(), nullable=False),
sa.Column("revoked", sa.Boolean(), nullable=False, server_default="0"),
sa.Column(
"created_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(
["user_id"], ["users.id"], ondelete="CASCADE"
),
sa.Index("idx_user", "user_id"),
sa.Index("idx_token", "token_hash"),
)
# ---- 3. invitation_links (FK -> users) ----
op.create_table(
"invitation_links",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("token", sa.String(255), nullable=False),
sa.Column("email", sa.String(255), nullable=True),
sa.Column(
"role", sa.String(20), nullable=False, server_default="dak_mitarbeiter"
),
sa.Column("created_by", sa.Integer(), nullable=True),
sa.Column("expires_at", sa.DateTime(), nullable=False),
sa.Column("used_at", sa.DateTime(), nullable=True),
sa.Column("used_by", sa.Integer(), nullable=True),
sa.Column("is_active", sa.Boolean(), nullable=False, server_default="1"),
sa.Column(
"created_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(["created_by"], ["users.id"]),
sa.ForeignKeyConstraint(["used_by"], ["users.id"]),
sa.UniqueConstraint("token", name="uk_token"),
)
# ---- 4. allowed_domains (no FK) ----
op.create_table(
"allowed_domains",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("domain", sa.String(255), nullable=False),
sa.Column(
"role", sa.String(20), nullable=False, server_default="dak_mitarbeiter"
),
sa.Column("is_active", sa.Boolean(), nullable=False, server_default="1"),
sa.Column(
"created_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("domain", name="uk_domain"),
)
# ---- 5. cases (FK -> users for updated_by, icd_entered_by, coding_completed_by) ----
op.create_table(
"cases",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("fall_id", sa.String(100), nullable=True),
sa.Column("crm_ticket_id", sa.String(20), nullable=True),
sa.Column("jahr", sa.SmallInteger(), nullable=False),
sa.Column("kw", sa.SmallInteger(), nullable=False),
sa.Column("datum", sa.Date(), nullable=False),
sa.Column("anrede", sa.String(20), nullable=True),
sa.Column("vorname", sa.String(100), nullable=True),
sa.Column("nachname", sa.String(100), nullable=False),
sa.Column("geburtsdatum", sa.Date(), nullable=True),
sa.Column("kvnr", sa.String(20), nullable=True),
sa.Column(
"versicherung", sa.String(50), nullable=False, server_default="DAK"
),
sa.Column("icd", sa.Text(), nullable=True),
sa.Column("fallgruppe", sa.String(20), nullable=False),
sa.Column("strasse", sa.String(255), nullable=True),
sa.Column("plz", sa.String(10), nullable=True),
sa.Column("ort", sa.String(100), nullable=True),
sa.Column("email", sa.String(255), nullable=True),
sa.Column("ansprechpartner", sa.String(200), nullable=True),
sa.Column("telefonnummer", sa.String(50), nullable=True),
sa.Column("mobiltelefon", sa.String(50), nullable=True),
sa.Column("email2", sa.String(255), nullable=True),
sa.Column("telefon2", sa.String(50), nullable=True),
sa.Column("unterlagen", sa.Boolean(), nullable=False, server_default="0"),
sa.Column("unterlagen_verschickt", sa.Date(), nullable=True),
sa.Column("erhalten", sa.Boolean(), nullable=True),
sa.Column("unterlagen_erhalten", sa.Date(), nullable=True),
sa.Column("unterlagen_an_gutachter", sa.Date(), nullable=True),
sa.Column("gutachten", sa.Boolean(), nullable=False, server_default="0"),
sa.Column("gutachter", sa.String(100), nullable=True),
sa.Column("gutachten_erstellt", sa.Date(), nullable=True),
sa.Column("gutachten_versendet", sa.Date(), nullable=True),
sa.Column(
"schweigepflicht", sa.Boolean(), nullable=False, server_default="0"
),
sa.Column("ablehnung", sa.Boolean(), nullable=False, server_default="0"),
sa.Column("abbruch", sa.Boolean(), nullable=False, server_default="0"),
sa.Column("abbruch_datum", sa.Date(), nullable=True),
sa.Column("gutachten_typ", sa.String(20), nullable=True),
sa.Column("therapieaenderung", sa.String(5), nullable=True),
sa.Column(
"ta_diagnosekorrektur",
sa.Boolean(),
nullable=False,
server_default="0",
),
sa.Column(
"ta_unterversorgung", sa.Boolean(), nullable=False, server_default="0"
),
sa.Column(
"ta_uebertherapie", sa.Boolean(), nullable=False, server_default="0"
),
sa.Column("kurzbeschreibung", sa.Text(), nullable=True),
sa.Column("fragestellung", sa.Text(), nullable=True),
sa.Column("kommentar", sa.Text(), nullable=True),
sa.Column("sonstiges", sa.Text(), nullable=True),
sa.Column("abgerechnet", sa.Boolean(), nullable=False, server_default="0"),
sa.Column("abrechnung_datum", sa.Date(), nullable=True),
sa.Column("import_source", sa.String(255), nullable=True),
sa.Column(
"imported_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.Column(
"updated_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.Column("updated_by", sa.Integer(), nullable=True),
sa.Column("icd_entered_by", sa.Integer(), nullable=True),
sa.Column("icd_entered_at", sa.DateTime(), nullable=True),
sa.Column("coding_completed_by", sa.Integer(), nullable=True),
sa.Column("coding_completed_at", sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(["updated_by"], ["users.id"]),
sa.ForeignKeyConstraint(["icd_entered_by"], ["users.id"]),
sa.ForeignKeyConstraint(["coding_completed_by"], ["users.id"]),
sa.UniqueConstraint("fall_id", name="uk_fall_id"),
sa.Index("idx_jahr_kw", "jahr", "kw"),
sa.Index("idx_kvnr", "kvnr"),
sa.Index("idx_fallgruppe", "fallgruppe"),
sa.Index("idx_datum", "datum"),
sa.Index("idx_nachname_vorname", "nachname", "vorname"),
sa.Index("idx_pending_coding", "gutachten", "gutachten_typ"),
sa.CheckConstraint(
"fallgruppe IN ('onko','kardio','intensiv','galle','sd')",
name="chk_fallgruppe",
),
sa.CheckConstraint(
"gutachten_typ IS NULL OR gutachten_typ IN "
"('Bestätigung','Alternative')",
name="chk_gutachten_typ",
),
sa.CheckConstraint(
"therapieaenderung IS NULL OR therapieaenderung IN ('Ja','Nein')",
name="chk_ta",
),
)
# The idx_pending_icd index uses a prefix length on the `icd` TEXT column.
# SQLAlchemy / Alembic cannot express mysql_length via sa.Index in create_table,
# so we create it with raw SQL.
op.execute(
"CREATE INDEX idx_pending_icd ON cases (jahr, kw, fallgruppe, icd(20))"
)
# ---- 6. case_icd_codes (FK -> cases) ----
op.create_table(
"case_icd_codes",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("case_id", sa.Integer(), nullable=False),
sa.Column("icd_code", sa.String(20), nullable=False),
sa.Column("icd_hauptgruppe", sa.String(10), nullable=True),
sa.Column(
"created_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(
["case_id"], ["cases.id"], ondelete="CASCADE"
),
sa.Index("idx_case", "case_id"),
sa.Index("idx_code", "icd_code"),
sa.Index("idx_haupt", "icd_hauptgruppe"),
)
# ---- 7. weekly_reports (FK -> users) ----
op.create_table(
"weekly_reports",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("jahr", sa.SmallInteger(), nullable=False),
sa.Column("kw", sa.SmallInteger(), nullable=False),
sa.Column("report_date", sa.Date(), nullable=False),
sa.Column("report_file_path", sa.String(500), nullable=True),
sa.Column("report_data", mysql.JSON(), nullable=True),
sa.Column("generated_by", sa.Integer(), nullable=True),
sa.Column(
"generated_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(["generated_by"], ["users.id"]),
sa.UniqueConstraint("jahr", "kw", name="uk_jahr_kw"),
)
# ---- 8. yearly_summary (no FK) ----
op.create_table(
"yearly_summary",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("jahr", sa.SmallInteger(), nullable=False),
sa.Column("kw", sa.SmallInteger(), nullable=False),
# Overall counts
sa.Column("erstberatungen", sa.Integer(), server_default="0"),
sa.Column("ablehnungen", sa.Integer(), server_default="0"),
sa.Column("unterlagen", sa.Integer(), server_default="0"),
sa.Column("keine_rueckmeldung", sa.Integer(), server_default="0"),
sa.Column("gutachten_gesamt", sa.Integer(), server_default="0"),
sa.Column("gutachten_alternative", sa.Integer(), server_default="0"),
sa.Column("gutachten_bestaetigung", sa.Integer(), server_default="0"),
# Per-Fallgruppe: onko
sa.Column("onko_anzahl", sa.Integer(), server_default="0"),
sa.Column("onko_gutachten", sa.Integer(), server_default="0"),
sa.Column("onko_keine_rm", sa.Integer(), server_default="0"),
# Per-Fallgruppe: kardio
sa.Column("kardio_anzahl", sa.Integer(), server_default="0"),
sa.Column("kardio_gutachten", sa.Integer(), server_default="0"),
sa.Column("kardio_keine_rm", sa.Integer(), server_default="0"),
# Per-Fallgruppe: intensiv
sa.Column("intensiv_anzahl", sa.Integer(), server_default="0"),
sa.Column("intensiv_gutachten", sa.Integer(), server_default="0"),
sa.Column("intensiv_keine_rm", sa.Integer(), server_default="0"),
# Per-Fallgruppe: galle
sa.Column("galle_anzahl", sa.Integer(), server_default="0"),
sa.Column("galle_gutachten", sa.Integer(), server_default="0"),
sa.Column("galle_keine_rm", sa.Integer(), server_default="0"),
# Per-Fallgruppe: sd
sa.Column("sd_anzahl", sa.Integer(), server_default="0"),
sa.Column("sd_gutachten", sa.Integer(), server_default="0"),
sa.Column("sd_keine_rm", sa.Integer(), server_default="0"),
# Gutachten-Typ per Fallgruppe
sa.Column("onko_alternative", sa.Integer(), server_default="0"),
sa.Column("onko_bestaetigung", sa.Integer(), server_default="0"),
sa.Column("kardio_alternative", sa.Integer(), server_default="0"),
sa.Column("kardio_bestaetigung", sa.Integer(), server_default="0"),
sa.Column("intensiv_alternative", sa.Integer(), server_default="0"),
sa.Column("intensiv_bestaetigung", sa.Integer(), server_default="0"),
sa.Column("galle_alternative", sa.Integer(), server_default="0"),
sa.Column("galle_bestaetigung", sa.Integer(), server_default="0"),
sa.Column("sd_alternative", sa.Integer(), server_default="0"),
sa.Column("sd_bestaetigung", sa.Integer(), server_default="0"),
# Therapieaenderung counts
sa.Column("ta_ja", sa.Integer(), server_default="0"),
sa.Column("ta_nein", sa.Integer(), server_default="0"),
sa.Column("ta_diagnosekorrektur", sa.Integer(), server_default="0"),
sa.Column("ta_unterversorgung", sa.Integer(), server_default="0"),
sa.Column("ta_uebertherapie", sa.Integer(), server_default="0"),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("jahr", "kw", name="uk_jahr_kw"),
)
# ---- 9. import_log (FK -> users) ----
op.create_table(
"import_log",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("filename", sa.String(255), nullable=False),
sa.Column("import_type", sa.String(50), nullable=False),
sa.Column(
"cases_imported", sa.Integer(), nullable=False, server_default="0"
),
sa.Column(
"cases_skipped", sa.Integer(), nullable=False, server_default="0"
),
sa.Column(
"cases_updated", sa.Integer(), nullable=False, server_default="0"
),
sa.Column("errors", sa.Text(), nullable=True),
sa.Column("details", mysql.JSON(), nullable=True),
sa.Column("imported_by", sa.Integer(), nullable=True),
sa.Column(
"imported_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(["imported_by"], ["users.id"]),
sa.CheckConstraint(
"import_type IN "
"('csv_crm','icd_xlsx','historical_excel','excel_sync')",
name="chk_imp_type",
),
)
# ---- 10. audit_log (FK -> users) ----
op.create_table(
"audit_log",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("user_id", sa.Integer(), nullable=True),
sa.Column("action", sa.String(100), nullable=False),
sa.Column("entity_type", sa.String(50), nullable=True),
sa.Column("entity_id", sa.Integer(), nullable=True),
sa.Column("old_values", mysql.JSON(), nullable=True),
sa.Column("new_values", mysql.JSON(), nullable=True),
sa.Column("ip_address", sa.String(45), nullable=True),
sa.Column("user_agent", sa.Text(), nullable=True),
sa.Column(
"created_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(["user_id"], ["users.id"]),
sa.Index("idx_user", "user_id"),
sa.Index("idx_entity", "entity_type", "entity_id"),
sa.Index("idx_created", "created_at"),
)
# ---- 11. notifications (FK -> users) ----
op.create_table(
"notifications",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("recipient_id", sa.Integer(), nullable=False),
sa.Column("notification_type", sa.String(50), nullable=False),
sa.Column("title", sa.String(255), nullable=False),
sa.Column("message", sa.Text(), nullable=True),
sa.Column("related_entity_type", sa.String(50), nullable=True),
sa.Column("related_entity_id", sa.Integer(), nullable=True),
sa.Column("is_read", sa.Boolean(), nullable=False, server_default="0"),
sa.Column(
"email_sent", sa.Boolean(), nullable=False, server_default="0"
),
sa.Column("email_sent_at", sa.DateTime(), nullable=True),
sa.Column(
"created_at",
sa.DateTime(),
nullable=False,
server_default=sa.func.now(),
),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(["recipient_id"], ["users.id"]),
sa.Index("idx_recipient", "recipient_id", "is_read"),
sa.CheckConstraint(
"notification_type IN ("
"'new_cases_uploaded','icd_entered','icd_uploaded',"
"'report_ready','coding_completed')",
name="chk_notif",
),
)
def downgrade() -> None:
# Drop in reverse order of creation (respecting FK dependencies).
op.drop_table("notifications")
op.drop_table("audit_log")
op.drop_table("import_log")
op.drop_table("yearly_summary")
op.drop_table("weekly_reports")
op.drop_table("case_icd_codes")
op.drop_index("idx_pending_icd", table_name="cases")
op.drop_table("cases")
op.drop_table("allowed_domains")
op.drop_table("invitation_links")
op.drop_table("refresh_tokens")
op.drop_table("users")
Reference in a new issue View git blame Copy permalink