Ichitux/FarmaFinder
1
0
Fork 0
Code Issues Pull Requests 1 Actions Packages Projects Releases Wiki Activity
Files
main
FarmaFinder/apps/pip-platform/alembic/versions/001_initial.py
T
Antoni Nuñez Romeu 190b3d163d
Run Tests on Branches / Backend Tests (push) Successful in 3m38s
Details
Run Tests on Branches / Frontend Tests (push) Successful in 3m28s
Details
Restructure with Turborepo
2026-07-06 15:51:53 +02:00

307 lines
16 KiB
Python
Raw Permalink Blame History

"""Phase 1 initial schema
Revision ID: 001_initial
Revises: None
Create Date: 2026-06-30
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB, UUID
revision: str = "001_initial"
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:
op.create_table(
"erp_systems",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("name", sa.String(255), nullable=False),
sa.Column("erp_type", sa.String(50), nullable=False, index=True),
sa.Column("version", sa.String(50)),
sa.Column("base_url", sa.String(500)),
sa.Column("auth_config", JSONB),
sa.Column("status", sa.String(20), default="pending", index=True),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
)
op.create_table(
"pharmacies",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("code", sa.String(50), unique=True, nullable=False, index=True),
sa.Column("name", sa.String(255), nullable=False),
sa.Column("address", sa.String(500)),
sa.Column("city", sa.String(100)),
sa.Column("province", sa.String(100)),
sa.Column("postal_code", sa.String(20)),
sa.Column("country", sa.String(2), default="ES"),
sa.Column("latitude", sa.Float),
sa.Column("longitude", sa.Float),
sa.Column("phone", sa.String(50)),
sa.Column("email", sa.String(255)),
sa.Column("opening_hours", JSONB),
sa.Column("status", sa.String(20), default="pending", index=True),
sa.Column("erp_type", sa.String(50)),
sa.Column("erp_version", sa.String(50)),
sa.Column("sync_strategy", sa.String(20), default="incremental"),
sa.Column("sync_interval_minutes", sa.Integer, default=30),
sa.Column("last_sync_at", sa.DateTime(timezone=True)),
sa.Column("last_sync_status", sa.String(20)),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
)
op.create_index("ix_pharmacies_location", "pharmacies", ["latitude", "longitude"])
op.create_index("ix_pharmacies_status_erp", "pharmacies", ["status", "erp_type"])
op.create_table(
"connectors",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("erp_system_id", UUID(as_uuid=True), sa.ForeignKey("erp_systems.id"), nullable=False, index=True),
sa.Column("pharmacy_id", UUID(as_uuid=True), sa.ForeignKey("pharmacies.id"), nullable=False, index=True),
sa.Column("name", sa.String(255), nullable=False),
sa.Column("connector_type", sa.String(50), nullable=False, index=True),
sa.Column("version", sa.String(20), default="1.0.0"),
sa.Column("config", JSONB),
sa.Column("status", sa.String(20), default="pending", index=True),
sa.Column("last_heartbeat_at", sa.DateTime(timezone=True)),
sa.Column("last_error", sa.Text),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
sa.UniqueConstraint("erp_system_id", "pharmacy_id", name="uq_connector_erp_pharmacy"),
)
op.add_column("pharmacies", sa.Column("connector_id", UUID(as_uuid=True), sa.ForeignKey("connectors.id"), nullable=True))
op.create_index("ix_pharmacies_connector_id", "pharmacies", ["connector_id"])
op.create_table(
"connector_versions",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("connector_id", UUID(as_uuid=True), sa.ForeignKey("connectors.id"), nullable=False, index=True),
sa.Column("connector_type", sa.String(50), nullable=False, index=True),
sa.Column("version", sa.String(20), nullable=False),
sa.Column("changelog", sa.Text),
sa.Column("download_url", sa.String(500)),
sa.Column("checksum", sa.String(128)),
sa.Column("is_latest", sa.Boolean, default=False, index=True),
sa.Column("is_required", sa.Boolean, default=False),
sa.Column("released_at", sa.DateTime(timezone=True)),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
sa.UniqueConstraint("connector_type", "version", name="uq_connector_version"),
)
op.create_table(
"connector_configs",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("connector_id", UUID(as_uuid=True), sa.ForeignKey("connectors.id"), nullable=False, index=True),
sa.Column("key", sa.String(100), nullable=False),
sa.Column("value", sa.Text, nullable=False),
sa.Column("encrypted", sa.Boolean, default=False),
sa.Column("description", sa.String(500)),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
sa.UniqueConstraint("connector_id", "key", name="uq_connector_config_key"),
)
op.create_table(
"medications",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("nregistro", sa.String(50), unique=True, nullable=False, index=True),
sa.Column("name", sa.String(500), nullable=False, index=True),
sa.Column("active_ingredient", sa.String(500)),
sa.Column("dosage", sa.String(100)),
sa.Column("form", sa.String(100)),
sa.Column("atc_code", sa.String(20), index=True),
sa.Column("prescription_required", sa.Boolean, default=False),
sa.Column("manufacturer", sa.String(255)),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
)
op.create_table(
"medication_catalog",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("pharmacy_id", UUID(as_uuid=True), sa.ForeignKey("pharmacies.id"), nullable=False, index=True),
sa.Column("medication_id", UUID(as_uuid=True), sa.ForeignKey("medications.id"), nullable=False, index=True),
sa.Column("price", sa.Float),
sa.Column("stock", sa.Integer, default=0),
sa.Column("is_available", sa.Boolean, default=False, index=True),
sa.Column("last_verified_at", sa.DateTime(timezone=True)),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
sa.UniqueConstraint("pharmacy_id", "medication_id", name="uq_catalog_pharmacy_medication"),
)
op.create_table(
"inventory_records",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("pharmacy_id", UUID(as_uuid=True), sa.ForeignKey("pharmacies.id"), nullable=False, index=True),
sa.Column("medication_id", UUID(as_uuid=True), sa.ForeignKey("medications.id"), nullable=False, index=True),
sa.Column("stock", sa.Integer, default=0),
sa.Column("reserved_stock", sa.Integer, default=0),
sa.Column("available_stock", sa.Integer, default=0),
sa.Column("min_stock", sa.Integer),
sa.Column("max_stock", sa.Integer),
sa.Column("last_updated_at", sa.DateTime(timezone=True), index=True),
sa.Column("source", sa.String(100)),
sa.Column("batch_id", sa.String(100)),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
sa.UniqueConstraint("pharmacy_id", "medication_id", name="uq_inventory_pharmacy_medication"),
)
op.create_table(
"inventory_history",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("inventory_record_id", UUID(as_uuid=True), sa.ForeignKey("inventory_records.id"), nullable=False, index=True),
sa.Column("pharmacy_id", UUID(as_uuid=True), sa.ForeignKey("pharmacies.id"), nullable=False, index=True),
sa.Column("medication_id", UUID(as_uuid=True), sa.ForeignKey("medications.id"), nullable=False, index=True),
sa.Column("previous_stock", sa.Integer, nullable=False),
sa.Column("new_stock", sa.Integer, nullable=False),
sa.Column("delta", sa.Integer, nullable=False),
sa.Column("reason", sa.String(255)),
sa.Column("source", sa.String(100)),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, index=True),
)
op.create_table(
"technical_users",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("username", sa.String(100), unique=True, nullable=False, index=True),
sa.Column("email", sa.String(255)),
sa.Column("full_name", sa.String(255)),
sa.Column("password_hash", sa.String(255), nullable=False),
sa.Column("role", sa.String(20), default="viewer", index=True),
sa.Column("is_active", sa.Boolean, default=True, index=True),
sa.Column("scopes", JSONB, default=list),
sa.Column("last_login_at", sa.DateTime(timezone=True)),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
)
op.create_table(
"api_clients",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("name", sa.String(255), nullable=False),
sa.Column("description", sa.Text),
sa.Column("owner_id", UUID(as_uuid=True), sa.ForeignKey("technical_users.id"), nullable=True, index=True),
sa.Column("is_active", sa.Boolean, default=True, index=True),
sa.Column("scopes", JSONB, default=list),
sa.Column("rate_limit", sa.Integer),
sa.Column("allowed_ips", JSONB, default=list),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
)
op.create_table(
"api_keys",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("api_client_id", UUID(as_uuid=True), sa.ForeignKey("api_clients.id"), nullable=False, index=True),
sa.Column("key_prefix", sa.String(10), nullable=False, index=True),
sa.Column("key_hash", sa.LargeBinary(64), nullable=False),
sa.Column("name", sa.String(255), nullable=False),
sa.Column("is_active", sa.Boolean, default=True, index=True),
sa.Column("expires_at", sa.DateTime(timezone=True)),
sa.Column("last_used_at", sa.DateTime(timezone=True)),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
)
op.create_index("ix_api_keys_prefix_active", "api_keys", ["key_prefix", "is_active"])
op.create_table(
"reservations",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("pharmacy_id", UUID(as_uuid=True), sa.ForeignKey("pharmacies.id"), nullable=False, index=True),
sa.Column("medication_id", UUID(as_uuid=True), sa.ForeignKey("medications.id"), nullable=False, index=True),
sa.Column("user_id", UUID(as_uuid=True), sa.ForeignKey("technical_users.id"), nullable=True, index=True),
sa.Column("external_reference", sa.String(255)),
sa.Column("quantity", sa.Integer, nullable=False),
sa.Column("status", sa.String(20), default="pending", index=True),
sa.Column("expires_at", sa.DateTime(timezone=True), index=True),
sa.Column("confirmed_at", sa.DateTime(timezone=True)),
sa.Column("cancelled_at", sa.DateTime(timezone=True)),
sa.Column("idempotency_key", sa.String(255), unique=True, index=True),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
)
op.create_index("ix_reservations_pharmacy_status", "reservations", ["pharmacy_id", "status"])
op.create_index("ix_reservations_expires", "reservations", ["expires_at"])
op.create_table(
"sync_jobs",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("pharmacy_id", UUID(as_uuid=True), sa.ForeignKey("pharmacies.id"), nullable=False, index=True),
sa.Column("connector_id", UUID(as_uuid=True), sa.ForeignKey("connectors.id"), nullable=False, index=True),
sa.Column("strategy", sa.String(20), nullable=False),
sa.Column("status", sa.String(20), default="pending", index=True),
sa.Column("started_at", sa.DateTime(timezone=True)),
sa.Column("completed_at", sa.DateTime(timezone=True)),
sa.Column("items_total", sa.Integer),
sa.Column("items_processed", sa.Integer),
sa.Column("items_failed", sa.Integer),
sa.Column("error_message", sa.Text),
sa.Column("metadata", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), onupdate=sa.func.now(), nullable=False),
)
op.create_table(
"sync_logs",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("sync_job_id", UUID(as_uuid=True), sa.ForeignKey("sync_jobs.id"), nullable=False, index=True),
sa.Column("level", sa.String(20), default="INFO", nullable=False),
sa.Column("message", sa.Text, nullable=False),
sa.Column("details", JSONB),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, index=True),
)
op.create_table(
"audit_logs",
sa.Column("id", UUID(as_uuid=True), primary_key=True),
sa.Column("actor_id", UUID(as_uuid=True), index=True),
sa.Column("actor_type", sa.String(50)),
sa.Column("action", sa.String(100), nullable=False, index=True),
sa.Column("resource_type", sa.String(100), nullable=False, index=True),
sa.Column("resource_id", sa.String(255), index=True),
sa.Column("old_value", JSONB),
sa.Column("new_value", JSONB),
sa.Column("ip_address", sa.String(45)),
sa.Column("user_agent", sa.String(500)),
sa.Column("request_id", sa.String(100), index=True),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, index=True),
)
op.create_index("ix_audit_logs_action_resource", "audit_logs", ["action", "resource_type"])
op.create_index("ix_audit_logs_actor_created", "audit_logs", ["actor_id", "created_at"])
def downgrade() -> None:
op.drop_table("audit_logs")
op.drop_table("sync_logs")
op.drop_table("sync_jobs")
op.drop_table("reservations")
op.drop_table("api_keys")
op.drop_table("api_clients")
op.drop_table("technical_users")
op.drop_table("inventory_history")
op.drop_table("inventory_records")
op.drop_table("medication_catalog")
op.drop_table("medications")
op.drop_table("connector_configs")
op.drop_table("connector_versions")
op.drop_column("pharmacies", "connector_id")
op.drop_table("connectors")
op.drop_table("pharmacies")
op.drop_table("erp_systems")
Reference in New Issue View Git Blame Copy Permalink