The challenge
Cyprus publishes its duty-pharmacy schedule through the official data.gov.cy open-data portal. I wanted to turn that feed into a dependable, location-aware way to find pharmacies by district and date, complete with contact, map, and navigation details. Duty Pharmacies provides that localized web interface on top of the government data.
What I built
The web app turns the data.gov.cy CKAN feed into a map-first experience in six languages. Visitors can select a district and date or use browser geolocation to find the nearest district. The results include duty pharmacies, contact details, map locations, guidance on dates and hours, and Google Maps directions.
The PWA caches pages and API responses after they load. Localized canonical routes, a sitemap, and structured data make the date-specific pages indexable.
Key features
Daily government data sync
At 03:00 UTC, a Cloudflare Cron Trigger starts a parallel, five-district sync against the data.gov.cy CKAN datastore. Each district fetches pages of 100 rows and checks the returned fields for schema drift. When the schema has changed, the job records a drift status in D1 instead of writing an incompatible payload. Drizzle writes valid pharmacy and duty-schedule rows to D1 in batches of 100 statements.
Geocoding with a safe fallback
After the sync, Google geocoding backfills upcoming pharmacies with missing coordinates. When a lookup fails, the pipeline stores the relevant district centroid and marks the source as centroid; the web UI renders that pin as an approximate location rather than presenting it as exact.
Map-first UI in six languages
The web routes cover Greek, English, French, German, Spanish, and Russian. Duty pharmacies are plotted on a Mapbox map with contact details and approximate-location markers, and each result links to Google Maps for navigation.
Offline-capable PWA
AstroPWA/Workbox caches HTML, API responses, static assets, fonts, and Mapbox tiles with bounded expiration windows. Once a result has been loaded, the cached page can still render without a network connection.
Architecture
Frontend: Astro 6 with the Cloudflare adapter renders localized routes and date-specific duty pages. It requests the shared tRPC endpoint, while AstroPWA provides the service worker and runtime caching.
Backend: A Hono Worker mounts the tRPC API. Drizzle uses Cloudflare D1 for pharmacies, duty schedules, and sync logs. The companion Expo mobile app consumes the same API.
Data operations: The 03:00 UTC Worker trigger runs the five district syncs through Promise.allSettled. It logs schema hashes and drift states, batches writes at 100 statements, then runs the Google geocoding pass with a district-centroid fallback.
Product model: Duty Pharmacies is a personal project on its own domain rather than a client engagement. Google AdSense is wired in as the web monetization path, while the product remains focused on the public-data utility.
Infrastructure: GitHub Actions deploys the web and API as separate Cloudflare Workers and applies D1 migrations during the deployment workflow.
Monorepo: A pnpm workspace managed with Turborepo contains the Astro web app, Hono API, Expo mobile app, and shared database and domain packages.