Spaces:

larxius
/

larmedix

Running on CPU Upgrade

larmedix / database.py

larxius

feat(storage): Firebase uploads persistent across rebuilds

aa22f66 about 1 month ago

5.42 kB

"""
Separate database configuration for GCP Cloud SQL PostgreSQL.
  Instance : larmedix-db
  Database : larmedix
Priority order for connection:
  1. DATABASE_URL env var (recommended for HuggingFace Secrets / GCP)
     e.g. postgresql+psycopg2://USER:PASSWORD@HOST:5432/larmedix
  2. Cloud SQL Python Connector (if USE_CLOUD_SQL_CONNECTOR=true)
     Requires: INSTANCE_CONNECTION_NAME="PROJECT:REGION:larmedix-db"
  3. TCP / Unix-socket from individual env vars:
     DB_USER, DB_PASSWORD, DB_HOST (public IP or 127.0.0.1 via proxy),
     DB_PORT (default 5432), DB_NAME (default larmedix),
     DB_UNIX_SOCKET (e.g. /cloudsql/PROJECT:REGION:larmedix-db)
  4. Local SQLite fallback (dev only): sqlite:///hospital.db
On first successful connection, init_database(app) automatically
creates all tables (db.create_all()) and seeds the superadmin user.
We will plug real GCP credentials in later via env vars / secrets —
no code change needed.
"""
import os


# ---- GCP defaults (instance / database names are fixed) ----
GCP_INSTANCE_NAME = os.environ.get("GCP_INSTANCE_NAME", "larmedix-db")
DEFAULT_DB_NAME = os.environ.get("DB_NAME", "larmedix")


def get_database_uri():
    """Build the SQLAlchemy database URI from environment."""
    # 1. Full URL wins (HF Space Secrets -> DATABASE_URL)
    database_url = os.environ.get("DATABASE_URL")
    if database_url:
        # Some providers give postgres:// -> fix to postgresql://
        if database_url.startswith("postgres://"):
            database_url = database_url.replace("postgres://", "postgresql://", 1)
        # Default to psycopg2 driver if no driver specified
        if database_url.startswith("postgresql://"):
            database_url = database_url.replace(
                "postgresql://", "postgresql+psycopg2://", 1
            )
        return database_url

    # 2. Individual parts -> build TCP URL (Cloud SQL public IP / proxy)
    db_user = os.environ.get("DB_USER")
    db_password = os.environ.get("DB_PASSWORD", "")
    db_host = os.environ.get("DB_HOST")  # Cloud SQL public IP
    db_port = os.environ.get("DB_PORT", "5432")
    db_name = os.environ.get("DB_NAME", DEFAULT_DB_NAME)

    # Unix socket path (Cloud Run / GCE with cloud-sql-proxy):
    # e.g. /cloudsql/PROJECT:REGION:larmedix-db
    unix_socket = os.environ.get("DB_UNIX_SOCKET")

    if db_user and unix_socket:
        # Connect via Unix domain socket
        return (
            f"postgresql+psycopg2://{db_user}:{db_password}@/{db_name}"
            f"?host={unix_socket}"
        )

    if db_user and db_host:
        return (
            f"postgresql+psycopg2://{db_user}:{db_password}"
            f"@{db_host}:{db_port}/{db_name}"
        )

    # 3. Local fallback (dev / HF build without secrets)
    return "sqlite:///hospital.db"


def is_postgres(uri: str) -> bool:
    return uri.startswith("postgresql") or uri.startswith("postgres")


def get_engine_options(uri: str) -> dict:
    """Sensible pool defaults for Cloud SQL Postgres."""
    if is_postgres(uri):
        return {
            "pool_size": int(os.environ.get("DB_POOL_SIZE", "5")),
            "max_overflow": int(os.environ.get("DB_MAX_OVERFLOW", "10")),
            "pool_timeout": 30,
            "pool_recycle": 1800,
            "pool_pre_ping": True,
        }
    # SQLite (local dev)
    return {"connect_args": {"timeout": 30}}


def init_database(app):
    """
    Call once inside app context after db.init_app(app).
    Automatically creates all tables on first connect + seeds superadmin.
    Safe to call on every startup (create_all is idempotent).
    """
    # Import here to avoid circular imports (models defines db)
    from models import db, User
    from werkzeug.security import generate_password_hash

    with app.app_context():
        db.create_all()

        # Auto-migrate: older Postgres DBs have user.password VARCHAR(150),
        # but Werkzeug scrypt hashes are ~160+ chars. Widen if needed.
        # Also widen filepath columns for Firebase Storage URLs.
        try:
            from sqlalchemy import text
            if is_postgres(str(db.engine.url)):
                with db.engine.begin() as conn:
                    conn.execute(text(
                        'ALTER TABLE "user" ALTER COLUMN password TYPE VARCHAR(500)'
                    ))
                    conn.execute(text(
                        'ALTER TABLE report ALTER COLUMN filepath TYPE VARCHAR(1024)'
                    ))
                    conn.execute(text(
                        'ALTER TABLE patient_profile ALTER COLUMN insurance_document TYPE VARCHAR(1024)'
                    ))
        except Exception as e:
            print(f"column migrate skipped: {e}")

        # Superadmin lives in the DB (User table, role=superadmin).
        # Seeded once with defaults; afterwards DB is source of truth.
        # Changing Secrets will NOT overwrite it — edit via SQL/app.
        sa = User.query.filter_by(role="superadmin").first()
        if not sa:
            db.session.add(
                User(
                    email="superadmin@system.com",
                    username="superadmin",
                    password=generate_password_hash("superadmin123"),
                    role="superadmin",
                    hospital_id=None,
                )
            )
            db.session.commit()
            print("Superadmin seeded (superadmin@system.com).")

	"""
	Separate database configuration for GCP Cloud SQL PostgreSQL.

	Instance : larmedix-db
	Database : larmedix

	Priority order for connection:
	1. DATABASE_URL env var (recommended for HuggingFace Secrets / GCP)
	e.g. postgresql+psycopg2://USER:PASSWORD@HOST:5432/larmedix
	2. Cloud SQL Python Connector (if USE_CLOUD_SQL_CONNECTOR=true)
	Requires: INSTANCE_CONNECTION_NAME="PROJECT:REGION:larmedix-db"
	3. TCP / Unix-socket from individual env vars:
	DB_USER, DB_PASSWORD, DB_HOST (public IP or 127.0.0.1 via proxy),
	DB_PORT (default 5432), DB_NAME (default larmedix),
	DB_UNIX_SOCKET (e.g. /cloudsql/PROJECT:REGION:larmedix-db)
	4. Local SQLite fallback (dev only): sqlite:///hospital.db

	On first successful connection, init_database(app) automatically
	creates all tables (db.create_all()) and seeds the superadmin user.
	We will plug real GCP credentials in later via env vars / secrets —
	no code change needed.
	"""
	import os


	# ---- GCP defaults (instance / database names are fixed) ----
	GCP_INSTANCE_NAME = os.environ.get("GCP_INSTANCE_NAME", "larmedix-db")
	DEFAULT_DB_NAME = os.environ.get("DB_NAME", "larmedix")


	def get_database_uri():
	"""Build the SQLAlchemy database URI from environment."""
	# 1. Full URL wins (HF Space Secrets -> DATABASE_URL)
	database_url = os.environ.get("DATABASE_URL")
	if database_url:
	# Some providers give postgres:// -> fix to postgresql://
	if database_url.startswith("postgres://"):
	database_url = database_url.replace("postgres://", "postgresql://", 1)
	# Default to psycopg2 driver if no driver specified
	if database_url.startswith("postgresql://"):
	database_url = database_url.replace(
	"postgresql://", "postgresql+psycopg2://", 1
	)
	return database_url

	# 2. Individual parts -> build TCP URL (Cloud SQL public IP / proxy)
	db_user = os.environ.get("DB_USER")
	db_password = os.environ.get("DB_PASSWORD", "")
	db_host = os.environ.get("DB_HOST") # Cloud SQL public IP
	db_port = os.environ.get("DB_PORT", "5432")
	db_name = os.environ.get("DB_NAME", DEFAULT_DB_NAME)

	# Unix socket path (Cloud Run / GCE with cloud-sql-proxy):
	# e.g. /cloudsql/PROJECT:REGION:larmedix-db
	unix_socket = os.environ.get("DB_UNIX_SOCKET")

	if db_user and unix_socket:
	# Connect via Unix domain socket
	return (
	f"postgresql+psycopg2://{db_user}:{db_password}@/{db_name}"
	f"?host={unix_socket}"
	)

	if db_user and db_host:
	return (
	f"postgresql+psycopg2://{db_user}:{db_password}"
	f"@{db_host}:{db_port}/{db_name}"
	)

	# 3. Local fallback (dev / HF build without secrets)
	return "sqlite:///hospital.db"


	def is_postgres(uri: str) -> bool:
	return uri.startswith("postgresql") or uri.startswith("postgres")


	def get_engine_options(uri: str) -> dict:
	"""Sensible pool defaults for Cloud SQL Postgres."""
	if is_postgres(uri):
	return {
	"pool_size": int(os.environ.get("DB_POOL_SIZE", "5")),
	"max_overflow": int(os.environ.get("DB_MAX_OVERFLOW", "10")),
	"pool_timeout": 30,
	"pool_recycle": 1800,
	"pool_pre_ping": True,
	}
	# SQLite (local dev)
	return {"connect_args": {"timeout": 30}}


	def init_database(app):
	"""
	Call once inside app context after db.init_app(app).
	Automatically creates all tables on first connect + seeds superadmin.
	Safe to call on every startup (create_all is idempotent).
	"""
	# Import here to avoid circular imports (models defines db)
	from models import db, User
	from werkzeug.security import generate_password_hash

	with app.app_context():
	db.create_all()

	# Auto-migrate: older Postgres DBs have user.password VARCHAR(150),
	# but Werkzeug scrypt hashes are ~160+ chars. Widen if needed.
	# Also widen filepath columns for Firebase Storage URLs.
	try:
	from sqlalchemy import text
	if is_postgres(str(db.engine.url)):
	with db.engine.begin() as conn:
	conn.execute(text(
	'ALTER TABLE "user" ALTER COLUMN password TYPE VARCHAR(500)'
	))
	conn.execute(text(
	'ALTER TABLE report ALTER COLUMN filepath TYPE VARCHAR(1024)'
	))
	conn.execute(text(
	'ALTER TABLE patient_profile ALTER COLUMN insurance_document TYPE VARCHAR(1024)'
	))
	except Exception as e:
	print(f"column migrate skipped: {e}")

	# Superadmin lives in the DB (User table, role=superadmin).
	# Seeded once with defaults; afterwards DB is source of truth.
	# Changing Secrets will NOT overwrite it — edit via SQL/app.
	sa = User.query.filter_by(role="superadmin").first()
	if not sa:
	db.session.add(
	User(
	email="superadmin@system.com",
	username="superadmin",
	password=generate_password_hash("superadmin123"),
	role="superadmin",
	hospital_id=None,
	)
	)
	db.session.commit()
	print("Superadmin seeded (superadmin@system.com).")