Jens
faeb58ef6d
GeoIntel release gates / Compile, test, contracts and builds (push) Successful in 1m49s
GeoIntel release gates / Python and npm vulnerability policy (push) Successful in 21s
GeoIntel release gates / Production AI image, SBOM and container scan (push) Successful in 5m39s
GeoIntel release gates / Deploy exact gated revision to Unraid (push) Failing after 58m43s
335 lines
13 KiB
Python
335 lines
13 KiB
Python
#!/usr/bin/env python3
|
|
"""Emit a bounded read-only database/runtime integrity snapshot as JSON.
|
|
|
|
Run inside the GeoIntel application container. Every SQL statement has a
|
|
timeout; the script never writes application rows or storage artifacts.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import hashlib
|
|
import importlib.metadata
|
|
import json
|
|
import os
|
|
import platform
|
|
from datetime import datetime, timezone
|
|
from pathlib import Path
|
|
from typing import Any
|
|
|
|
from sqlalchemy import text
|
|
|
|
from app.db.session import engine
|
|
|
|
|
|
TABLES = (
|
|
"projects",
|
|
"areas",
|
|
"datasets",
|
|
"dataset_versions",
|
|
"vector_features",
|
|
"jobs",
|
|
"analysis_runs",
|
|
"detections",
|
|
"segmentations",
|
|
"quality_checks",
|
|
"metrics",
|
|
"exports",
|
|
"detection_reviews",
|
|
"aoi_operations",
|
|
"aoi_operation_partitions",
|
|
)
|
|
PATH_QUERIES = {
|
|
"datasets": "SELECT id::text, storage_path FROM datasets WHERE storage_path IS NOT NULL",
|
|
"dataset_versions": "SELECT id::text, storage_path FROM dataset_versions WHERE storage_path IS NOT NULL",
|
|
"exports": "SELECT id::text, storage_path FROM exports WHERE storage_path IS NOT NULL",
|
|
}
|
|
|
|
|
|
def now() -> str:
|
|
return datetime.now(timezone.utc).isoformat()
|
|
|
|
|
|
def sha256_file(path: Path) -> str:
|
|
digest = hashlib.sha256()
|
|
with path.open("rb") as handle:
|
|
for chunk in iter(lambda: handle.read(1024 * 1024), b""):
|
|
digest.update(chunk)
|
|
return digest.hexdigest()
|
|
|
|
|
|
def package_version(name: str) -> str | None:
|
|
try:
|
|
return importlib.metadata.version(name)
|
|
except importlib.metadata.PackageNotFoundError:
|
|
return None
|
|
|
|
|
|
def normalize_path(raw: str, storage_root: Path) -> Path:
|
|
candidate = Path(raw)
|
|
if candidate.is_absolute():
|
|
return candidate
|
|
normalized = raw.replace("\\", "/")
|
|
if normalized.startswith("storage/"):
|
|
normalized = normalized.removeprefix("storage/")
|
|
return storage_root / normalized
|
|
|
|
|
|
def rows(connection: Any, statement: str, parameters: dict[str, Any] | None = None) -> list[dict[str, Any]]:
|
|
result = connection.execute(text(statement), parameters or {})
|
|
return [dict(row._mapping) for row in result]
|
|
|
|
|
|
def scalar(connection: Any, statement: str) -> int:
|
|
return int(connection.execute(text(statement)).scalar() or 0)
|
|
|
|
|
|
def safe_query(connection: Any, name: str, statement: str) -> dict[str, Any]:
|
|
try:
|
|
return {"status": "ok", "rows": rows(connection, statement)}
|
|
except Exception as exc:
|
|
return {
|
|
"status": "error",
|
|
"error_type": type(exc).__name__,
|
|
"message": str(exc).splitlines()[0][:500],
|
|
"query_name": name,
|
|
}
|
|
|
|
|
|
def main() -> int:
|
|
storage_root = Path(os.environ.get("GEOINTEL_STORAGE_ROOT", "/app/storage")).resolve()
|
|
report: dict[str, Any] = {
|
|
"schema_version": 1,
|
|
"generated_at": now(),
|
|
"mode": "read_only",
|
|
"statement_timeout_ms": 30000,
|
|
"runtime": {
|
|
"python": platform.python_version(),
|
|
"platform": platform.platform(),
|
|
"packages": {
|
|
name: package_version(name)
|
|
for name in (
|
|
"geointel-backend",
|
|
"fastapi",
|
|
"sqlalchemy",
|
|
"geoalchemy2",
|
|
"shapely",
|
|
"pyproj",
|
|
"rasterio",
|
|
"geopandas",
|
|
"torch",
|
|
"torchvision",
|
|
"ultralytics",
|
|
)
|
|
},
|
|
},
|
|
"storage_root": str(storage_root),
|
|
}
|
|
try:
|
|
import torch
|
|
|
|
report["runtime"]["cuda"] = {
|
|
"available": torch.cuda.is_available(),
|
|
"runtime_version": torch.version.cuda,
|
|
"device_count": torch.cuda.device_count(),
|
|
"device_names": [
|
|
torch.cuda.get_device_name(index) for index in range(torch.cuda.device_count())
|
|
],
|
|
}
|
|
except Exception as exc:
|
|
report["runtime"]["cuda"] = {"available": False, "error": type(exc).__name__}
|
|
|
|
model_path = Path(os.environ.get("YOLO_MODEL_PATH", ""))
|
|
report["active_model"] = {
|
|
"configured_path": str(model_path) if str(model_path) else None,
|
|
"exists": model_path.is_file(),
|
|
"size_bytes": model_path.stat().st_size if model_path.is_file() else None,
|
|
"sha256": sha256_file(model_path) if model_path.is_file() else None,
|
|
"model_id": os.environ.get("YOLO_MODEL_ID"),
|
|
"model_version": os.environ.get("YOLO_MODEL_VERSION"),
|
|
"classes": os.environ.get("YOLO_MODEL_CLASSES"),
|
|
"device": os.environ.get("YOLO_DEVICE"),
|
|
"require_cuda": os.environ.get("YOLO_REQUIRE_CUDA"),
|
|
"validated_area_names": os.environ.get("YOLO_VALIDATED_AREA_NAMES"),
|
|
"validation_scope_enforced": os.environ.get("YOLO_ENFORCE_VALIDATION_SCOPE"),
|
|
}
|
|
|
|
with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection:
|
|
connection.execute(text("SET statement_timeout TO '30s'"))
|
|
report["database"] = {
|
|
"version": connection.execute(text("SELECT version()")).scalar(),
|
|
"postgis_version": connection.execute(text("SELECT PostGIS_Full_Version()")).scalar(),
|
|
"migration_heads": [row["version_num"] for row in rows(connection, "SELECT version_num FROM alembic_version")],
|
|
"table_counts": {table: scalar(connection, f'SELECT count(*) FROM "{table}"') for table in TABLES},
|
|
}
|
|
report["database"]["dataset_statuses"] = rows(
|
|
connection,
|
|
"SELECT status, count(*)::bigint AS count FROM datasets GROUP BY status ORDER BY status",
|
|
)
|
|
report["database"]["job_statuses"] = rows(
|
|
connection,
|
|
"SELECT status, count(*)::bigint AS count FROM jobs GROUP BY status ORDER BY status",
|
|
)
|
|
report["database"]["analysis_statuses"] = rows(
|
|
connection,
|
|
"SELECT status, count(*)::bigint AS count FROM analysis_runs GROUP BY status ORDER BY status",
|
|
)
|
|
report["database"]["dataset_lineage_gaps"] = rows(
|
|
connection,
|
|
"""
|
|
SELECT
|
|
count(*)::bigint AS total,
|
|
count(*) FILTER (WHERE crs IS NULL OR btrim(crs) = '')::bigint AS missing_crs,
|
|
count(*) FILTER (WHERE checksum_sha256 IS NULL OR btrim(checksum_sha256) = '')::bigint AS missing_checksum,
|
|
count(*) FILTER (WHERE source_version IS NULL OR btrim(source_version) = '')::bigint AS missing_source_version,
|
|
count(*) FILTER (WHERE source_metadata IS NULL OR source_metadata::text = '{}')::bigint AS missing_source_metadata,
|
|
count(*) FILTER (WHERE provenance_metadata IS NULL OR provenance_metadata::text = '{}')::bigint AS missing_provenance_metadata,
|
|
count(*) FILTER (WHERE imported_at IS NULL)::bigint AS missing_imported_at,
|
|
count(*) FILTER (WHERE observed_at IS NULL)::bigint AS missing_observed_at
|
|
FROM datasets
|
|
""",
|
|
)
|
|
report["database"]["dataset_version_gaps"] = rows(
|
|
connection,
|
|
"""
|
|
SELECT
|
|
count(*)::bigint AS total,
|
|
count(*) FILTER (WHERE checksum_sha256 IS NULL OR btrim(checksum_sha256) = '')::bigint AS missing_checksum,
|
|
count(*) FILTER (WHERE source_metadata IS NULL OR source_metadata::text = '{}')::bigint AS missing_source_metadata,
|
|
count(*) FILTER (WHERE provenance_metadata IS NULL OR provenance_metadata::text = '{}')::bigint AS missing_provenance_metadata,
|
|
count(*) FILTER (WHERE storage_path IS NULL OR btrim(storage_path) = '')::bigint AS missing_storage_path
|
|
FROM dataset_versions
|
|
""",
|
|
)
|
|
report["database"]["geometry_integrity"] = {}
|
|
for table, nullable in (
|
|
("areas", False),
|
|
("vector_features", False),
|
|
("detections", True),
|
|
("segmentations", False),
|
|
):
|
|
where = "WHERE geometry IS NOT NULL" if nullable else ""
|
|
statement = f"""
|
|
SELECT
|
|
count(*)::bigint AS populated,
|
|
count(*) FILTER (WHERE ST_IsEmpty(geometry))::bigint AS empty,
|
|
count(*) FILTER (WHERE NOT ST_IsValid(geometry))::bigint AS invalid,
|
|
count(*) FILTER (WHERE ST_SRID(geometry) <> 4326)::bigint AS wrong_srid,
|
|
count(*) FILTER (
|
|
WHERE ST_XMin(Box3D(geometry)) < -180
|
|
OR ST_XMax(Box3D(geometry)) > 180
|
|
OR ST_YMin(Box3D(geometry)) < -90
|
|
OR ST_YMax(Box3D(geometry)) > 90
|
|
)::bigint AS outside_epsg4326_domain
|
|
FROM {table} {where}
|
|
"""
|
|
report["database"]["geometry_integrity"][table] = safe_query(
|
|
connection, f"{table}_geometry_integrity", statement
|
|
)
|
|
report["database"]["outside_domain_detection_records"] = rows(
|
|
connection,
|
|
"""
|
|
SELECT
|
|
d.id::text AS detection_id,
|
|
d.analysis_run_id::text AS analysis_run_id,
|
|
d.dataset_id::text AS dataset_id,
|
|
p.name AS project_name,
|
|
ds.name AS dataset_name,
|
|
ds.source_name AS dataset_source_name,
|
|
ar.analysis_type,
|
|
ar.status AS analysis_status,
|
|
d.model_name,
|
|
d.model_version,
|
|
d.class_name,
|
|
d.confidence,
|
|
d.source_tile_path,
|
|
d.bbox_json,
|
|
ST_XMin(Box3D(d.geometry)) AS min_x,
|
|
ST_YMin(Box3D(d.geometry)) AS min_y,
|
|
ST_XMax(Box3D(d.geometry)) AS max_x,
|
|
ST_YMax(Box3D(d.geometry)) AS max_y,
|
|
d.created_at
|
|
FROM detections d
|
|
LEFT JOIN analysis_runs ar ON ar.id = d.analysis_run_id
|
|
LEFT JOIN datasets ds ON ds.id = d.dataset_id
|
|
LEFT JOIN projects p ON p.id = d.project_id
|
|
WHERE d.geometry IS NOT NULL
|
|
AND (
|
|
ST_XMin(Box3D(d.geometry)) < -180
|
|
OR ST_XMax(Box3D(d.geometry)) > 180
|
|
OR ST_YMin(Box3D(d.geometry)) < -90
|
|
OR ST_YMax(Box3D(d.geometry)) > 90
|
|
)
|
|
ORDER BY d.created_at, d.id
|
|
LIMIT 100
|
|
""",
|
|
)
|
|
report["database"]["confidence_integrity"] = rows(
|
|
connection,
|
|
"""
|
|
SELECT
|
|
(SELECT count(*) FROM detections WHERE confidence < 0 OR confidence > 1)::bigint
|
|
AS detections_outside_unit_interval,
|
|
(SELECT count(*) FROM segmentations
|
|
WHERE confidence IS NOT NULL AND (confidence < 0 OR confidence > 1))::bigint
|
|
AS segmentations_outside_unit_interval
|
|
""",
|
|
)
|
|
report["database"]["metric_nulls"] = rows(
|
|
connection,
|
|
"""
|
|
SELECT metric_key, count(*)::bigint AS total,
|
|
count(*) FILTER (WHERE metric_value IS NULL)::bigint AS null_values
|
|
FROM metrics GROUP BY metric_key ORDER BY metric_key
|
|
""",
|
|
)
|
|
report["database"]["model_run_summary"] = rows(
|
|
connection,
|
|
"""
|
|
SELECT analysis_type, status, coalesce(model_name, '<none>') AS model_name,
|
|
coalesce(model_version, '<none>') AS model_version, count(*)::bigint AS count
|
|
FROM analysis_runs
|
|
GROUP BY analysis_type, status, model_name, model_version
|
|
ORDER BY count(*) DESC, analysis_type
|
|
LIMIT 100
|
|
""",
|
|
)
|
|
report["database"]["source_summary"] = rows(
|
|
connection,
|
|
"""
|
|
SELECT coalesce(source_name, source, '<none>') AS source_name,
|
|
status, count(*)::bigint AS count,
|
|
count(*) FILTER (WHERE dataset_role = 'reference')::bigint AS reference_count
|
|
FROM datasets
|
|
GROUP BY coalesce(source_name, source, '<none>'), status
|
|
ORDER BY count(*) DESC, source_name
|
|
LIMIT 200
|
|
""",
|
|
)
|
|
|
|
path_records = []
|
|
for table, statement in PATH_QUERIES.items():
|
|
for row in rows(connection, statement):
|
|
path = normalize_path(row["storage_path"], storage_root)
|
|
path_records.append({
|
|
"table": table,
|
|
"id": row["id"],
|
|
"storage_path": row["storage_path"],
|
|
"resolved_path": str(path),
|
|
"exists": path.is_file() or path.is_dir(),
|
|
})
|
|
missing = [row for row in path_records if not row["exists"]]
|
|
report["storage_references"] = {
|
|
"checked_count": len(path_records),
|
|
"missing_count": len(missing),
|
|
"missing_records": missing[:500],
|
|
"records_truncated": len(missing) > 500,
|
|
"scope": "direct datasets, dataset_versions and exports storage_path columns",
|
|
}
|
|
|
|
print(json.dumps(report, ensure_ascii=False, indent=2, sort_keys=True, default=str))
|
|
return 0
|
|
|
|
|
|
if __name__ == "__main__":
|
|
raise SystemExit(main())
|