MediLink AI

MediLink AI tracks real-time drug availability, prices, and shortages using Fivetran + Google Cloud, with AI insights and maps to help communities access affordable medicines faster.

Comment

MediLink AI — Real-Time Drug Shortage & Price Intelligence

Stack: Fivetran (Connector SDK) → Google Cloud (BigQuery, Vertex AI, Cloud Storage) → Gradio (Python)
Focus: Healthcare access, affordability, and transparency through automated data pipelines + AI.

🌟 Inspiration

When essential medicines run low, patients bounce between pharmacies, pay more, or skip treatment. The status quo is opaque. MediLink AI turns scattered inventory/pricing signals into real-time availability, fair prices, and early shortage warnings.

💡 What it does

  • Search availability & prices across pharmacies by medicine/strength/city.
  • Shortage Watchlist with 14-day trends (LOW/OUT risk) and live, embedded maps.
  • AI Insights: Vertex AI (Gemini) converts raw signals to concise, human-readable guidance.
  • Interactive Agent: Natural language agent that interprets queries (e.g., “Find amoxicillin 500mg in Accra under 120 within 5km”), calls tools for live inventory/shortage data, and summarizes results with Maps links.
  • Open Analytics: A reusable BigQuery view (v_shortage_signal) powers dashboards and policy briefs.

🎨 Product Design

Principles: Instant clarity, zero-config onboarding, and trust-by-default.

  • Gradio UI: Clean search interface with filters, risk chips, and results table.
  • Actionable defaults: City prefilled; price caps configurable.
  • Spatial sense: Embedded maps and Google Maps deep-links for every result.
  • Explainability: Micro-copy shows why an item is flagged low/out of stock.
  • Accessibility: Keyboard-first design, high contrast, minimal friction.
  • Agent Tab: Dedicated tab where users can “Ask MediLink Agent,” get immediate “Agent is working…” feedback, and receive AI-guided answers.

🧭 Architecture

       Pharmacy Systems (POS, CSV, APIs)
              │
              ▼
     Fivetran Connector SDK
              │
              ▼
         Google BigQuery
   (inventory, price, product,
      pharmacy, shortage view)
              │
              ▼
       Vertex AI (Gemini)
 (Summaries & interactive Agent)
              │
              ▼
        Gradio Frontend
 (Search • Watchlist • Agent • Maps)

Vertical Architecture Diagram

Vertical Architecture Diagram

🔄 Data Flow

  1. Extract: Fivetran connector pulls data from POS/APIs/CSV/GCS.
  2. Load: Fivetran auto-synchronizes tables into BigQuery (medilink_*).
  3. Transform: SQL in BigQuery creates 14-day moving averages and shortage signals.
  4. Serve: Gradio queries BigQuery with parameters and renders maps.
  5. Explain: Vertex AI generates concise availability/price summaries.
  6. Interact: The MediLink Agent interprets natural language, calls functions like query_inventory or watchlist_table, and returns human-readable results.
  7. Feedback loop: Watchlist interactions inform caching and next-gen forecasting.

🗃 BigQuery Modeling

  • pharmacy(pharmacy_id, name, city, lat, lng, …)
  • product(product_id, name, strength, form, …)
  • inventory_snapshot(observed_at, qty_available, min_qty, …) — partitioned by time
  • price_snapshot(observed_at, unit_price, currency, …) — partitioned by time
  • View: v_shortage_signal with rolling 14-day mean & z-drop.
  • Optimizations: Partition pruning, clustering, result caching.

🧠 Vertex AI (Gemini)

  • Prompts convert BigQuery metrics (availability, price spread, trends) into <100-word summaries.
  • Guardrails ensure outputs are factual, non-clinical, and action-oriented.
  • Agent function calling: Gemini triggers custom Python tools (query_inventory, watchlist_table) and then finalizes with a plain text summary.
  • Provides clarity for pharmacists, NGOs, and policymakers.

🧩 Fivetran Deep Dive

  • Connector SDK (Python) handles idempotent extraction with observed_at watermark.
  • Auto-schema evolution and retries reduce maintenance overhead.
  • BigQuery destination (medilink_bigquery_medilink) is secured via IAM service accounts.
  • Zero pipeline ops: pharmacies feed in raw data, pipelines “just work.”

🧪 How we built it

  • Frontend: Gradio app with tabs for Search, Watchlist, Agent, and embedded maps.
  • Backend: BigQuery SQL views, partitioned tables, parameterized queries.
  • AI Layer: Vertex AI adds human-readable context and powers the Agent.
  • Data ingestion: Fivetran connector continuously updates BigQuery with new snapshots.

🔐 Security & Compliance

  • IAM roles separate ingestion and app-level queries.
  • Data locality enforced.
  • PII excluded; only inventory and prices are tracked.
  • Auditability: BigQuery logs + Fivetran run history.

🚧 Challenges we ran into

  • Designing 14-day trend analysis without expensive windowing.
  • De-duplicating multi-source data reliably.
  • Making queries performant and low-cost under real-time loads.
  • Implementing Vertex AI function-calling and ensuring responses finalize cleanly without looping tool calls.

🏆 Accomplishments

  • Fully automated pipeline from pharmacy systems → BigQuery → AI → frontend.
  • Predictive shortage detection using rolling trends.
  • Agent integration: Seamlessly blended natural language interface with structured queries.
  • A UX that feels simple while hiding complex pipelines underneath.
  • Demonstrated scalable model for health supply-chain transparency.

📚 What we learned

  • Fivetran simplifies ingestion more than custom ETL ever could.
  • BigQuery partitions + clustering unlock near-instant queries.
  • Vertex AI works best when prompts focus on decision support.
  • The Agent experience requires careful handling of tool responses vs. text finalization.
  • UX matters: maps + risk chips build trust with non-technical users.

🔮 What’s next

  • Forecasting with BigQuery ML/Prophet for 30-day predictions.
  • City-level dashboards for ministries and NGOs.
  • SMS/WhatsApp integration for low-bandwidth users.
  • Open APIs so developers can embed shortage insights in their apps.
  • Agent extensions: Adding pharmacy registration and alerts when items go OUT/LOW in specific regions.

🌍 Why it matters for the industry

MediLink AI shows how cloud data pipelines + AI can directly improve healthcare supply chains. Pharmacies reduce lost sales, patients gain affordable access, and policymakers gain transparent, real-time visibility into shortages. The new Agent feature further lowers the barrier for non-technical users by allowing natural language queries and instant insights. This model can scale nationwide and globally to strengthen resilience against drug shortages.

💬 Agent Example

User query:

“Find paracetamol 1g in Accra under 50 within 3km”

Agent response:

  • Paracetamol 1g at CityCare Pharmacy (Accra) — Qty: 20, Price: 45 GHS. Maps
  • Paracetamol 1g at TrustMed Pharmacy (Accra) — Qty: 10, Price: 47 GHS. Maps
  • Paracetamol 1g at GoodHealth Chemist (Accra) — Qty: 8, Price: 49 GHS. Maps

Built With

Share this project:

Updates