The Roadmap to Building Offline Drug Database Using Firebase Studio

Introduction

In my previous post on Firebase Studio, I discussed some of the limitations when coding an app powered by artificial intelligence.

• Despite those hurdles, the final outcome - the RxLookup Progressive Web App - stands as a noteworthy milestone and a successful achievement.
• In this post, I want to highlight a few key decision-making points and common pitfalls to watch out for if you are planning to build a drug database or dictionary app.

Deploying as Progressive Web App

Typically, an app can be deployed in several forms: as a web app or a native app (such as those available on the Google Play Store or Apple App Store).

• A rising trend in recent years is the Progressive Web App (PWA).

Technically, a PWA leverages standard web technologies, allowing a single codebase to run seamlessly across laptops, iPhones, and Android devices with minimal platform-specific adjustments.

• Since it is deployed as a hosted website, it enables rapid and frequent updates - unlike traditional native apps that often require lengthy app store approval processes.
• What makes PWAs more powerful than typical websites is their ability to support installation and offline functionality, made possible through tools like sw.js (service worker) and manifest.json.
• Beyond installation and offline access, PWAs offer a native-like experience through features such as full-screen mode, push notifications, and background sync - making them an ideal solution for building modern, responsive, and user-friendly apps.

AppScript to Sync Sheet to Firebase Firestore

Most of my earlier experience in converting spreadsheets into apps involved using no-code platforms like AppSheet, Open As App and Wistify, which handled all backend services automatically.

Conceptually, I planned to build a local IndexedDB to serve as the drug database.

• This approach avoids fetching data from Google Sheets every time and enables offline support, which is essential for a reliable user experience.

While it is technically possible to parse and serve spreadsheet data directly, this method becomes inefficient when dealing with a large number of drug entries and a high user load.

To improve scalability and performance, I explored Firebase, which offers both Realtime Database and Firestore.

• Since the app is primarily read-heavy and does not require frequent write operations, I chose Firestore due to its generous read limits (50k per day) and flexible querying.
• Another strong alternative worth considering is Supabase, especially for developers familiar with SQL.

A simple and effective way to sync a Google Sheet to Firestore is through Google Apps Script.

• In my AppScript setup, I generate a unique key ID based on the generic name, which serves as a consistent identifier throughout the app.
• This key ID is used when syncing data from Firestore to IndexedDB, as well as when fetching entries via the dynamic client-side routes in the app.
• The raw generic name itself is not used as the document key ID, since it can be lengthy and may include special characters that could lead to ID mismatches or errors in URL-based routing.
• The syncing of data from the Google Sheet to Firestore is done in batches using a time-based trigger, as Firestore imposes limits on the maximum number of write operations allowed within a given time period.

To support delta syncing, I added an optional field called last_updated to track the most recent changes, and last_deleted to enable soft deletes.

• The importance of setting this setup will be further explored in the later section of the blog post.

Additionally, I implemented logic for resetting sync progress and handling hard deletes.

• The hard delete mechanism complements the tombstone method used for soft deletes.
• In this setup, when a row is removed from the Google Sheet, a last_deleted timestamp is added to the corresponding document in Firestore instead of immediately deleting it.
• If the document remains absent from the Sheet for more than 30 days (subjective to your hard delete setting), it is then permanently deleted from Firestore based on the last_deleted timestamp. This approach helps prevent accidental data loss while keeping the database clean over time.

NOTE: The spreadsheet should have clear headers that correspond to the fields you want to create in Firestore documents. Each row will typically represent a document.

Setting Up Natural Command in Firebase Studio

In Firebase Studio, you can use the App Prototyping Agent to quickly create a new application using natural language prompts.

• Typically, you start with a rough idea of your app’s core features, then describe them in an AI chat (such as ChatGPT) to generate a natural language command that sets up the app structure.
• For example, the core design of RxLookup involves syncing data from Firestore to IndexedDB, rendering data on the client side through a dynamic app router, and providing a main page with search functionality across fields like generic name, synonyms, and brand name.

While further refining apps using natural language with Gemini Code Assist in prototyper mode is extremely helpful and easy, there are limitations.

• There are instances where you need to switch to manual coding mode to inspect or refine what is actually being generated.
• However, it is important to note that manual code changes are not tracked by Gemini Code Assist.
• So if you make edits directly in code and later switch back to prototyper mode, those manual changes may be overwritten or lost, as the system reverts to its earlier state prior to your edits.
• Hence, any desired change should ideally be made through natural command using Gemini Code Assist.

The key objective before deploying the app for the first time is not to perfect the syncing between Firestore and the app or to finalize the data rendering logic (since Firestore has not been fully set up yet).

• Instead, the focus should be on ensuring that the overall app design, styling, and color scheme align with your visual preferences and user experience goals.
• This process typically involves running the App Prototyping Agent multiple times and making several rounds of fine-tuning, especially since you are not the original designer of the user interface.

Setting Up Firestore Database

When the app is deployed, a Firebase project should be created in the Firebase Console.

• Within your Firebase project, navigate to “Build” > “Firestore Database”, then click “Create Database”.

After setting up Firestore, use the previously configured Google Apps Script to sync data from your Google Sheet to Firestore.

• Once the sync is complete, verify that all documents have been successfully generated in Cloud Firestore.

If possible, configure your Firestore security rules to allow read access only to authenticated clients.

• This helps protect your Firestore from unauthorized access and abuse.

Synchronization to Local IndexedDB

The next phase involves syncing Firestore data to local IndexedDB for offline access.

• Since many field titles contain multiple words, camelCase formatting is adopted for consistency.
• Firestore documents may include empty fields, which IndexedDB cannot store properly. These should be normalized to null during synchronization.

Ensure that IndexedDB utility fetches and syncs all data from Firestore to local storage initially. On subsequent syncs, it

• Compares local data with Firestore.
• Removes documents no longer present in Firestore.
• Updates existing records if any fields have changed.
• Inserts new drug entries added in Firestore.

Offline Support with Service Worker and Manifest

To enable offline functionality and instability, two key components are required:

• A Service Worker (sw.js)
• A Web App Manifest (manifest.json)

The service worker is responsible for caching the app shell (e.g. /, _offline.html, and Next.js static chunks) to enable offline functionality.

• Many opt for tools like Workbox or next-pwa for a more automated and comprehensive setup.
• However, a manually configured sw.js allows for fine-tuning caching strategies, such as stale-while-revalidate, cache-first and network-first. These can be applied depending on content freshness and usage scenarios.
• To ensure offline routing works correctly, make sure that unvisited dynamic routes (e.g., /drug/[id]) do not fallback to _offline.html. Instead, they are hydrated at the client side using IndexedDB data.

Additionally, sw.js should be versioned and set up to automatically update when a new version is deployed.

During setup, use browser’s developer tools to:

• Confirm that the service worker is registered and actively running to control the page.
• Verify the caching of essential app shell assets, including /, _offline.html and other static chunks.
• Check that Firestore data is correctly synced to IndexedDB.
• Test offline behavior, including routing and rendering.

When testing offline functionality, always monitor the console logs.

• Any error in sw.js will usually be reported there, and even a small mistake may cause offline navigation to fail.
• From my experience, this was the most challenging part, and it took almost endless trials and errors to debug and resolve properly.

The manifest.json file defines your PWA’s installability, including app name, icons, theme color, and display mode (e.g. standalone, full screen).

• It is essential for enabling “Add to Home Screen” prompts.
• Use tools like the Web Appp Manifest Validator to ensure your manifest is correctly configured and meets PWA standards.

Delta Sync and JSON Pre-Bundling

If everything has been set up correctly, your PWA should now:

• Load successfully.
• Sync and render Firestore data.
• Support offline navigation.

For personal use, this setup is usually sufficient.

• However, for scalability and commercial use, be mindful of Firestore’s read quota limits.
• Remember, each drug is stored as a separate document in Firestore.
• On first launch, the app may consume 3000-4000 document reads per user, just to initialize and sync the drug database.
• Then, the app will consume another similar amount of document reads when user decide to manual sync the indexedDB database from Firestore.

To reduce read costs, i have implemented

• Delta syncing, where the app only fetches documents that have changed (last_updated or last_deleted) since the last sync.
• A build-time script to export all Firestore documents into a drugs.json file. Pre-bundle this drugs.json file with the app and use it for initial IndexedDB population, to drastically reduce read operations during first load.

Ongoing Refinement

Refining the app is a continuous process. Improvements may include:

• Creating utility functions for data formatting, indexedDB interactions, fetch logic, etc., and organizing code into modular components to follow the DRY (Don’t Repeat Yourself) principle.
• Using tools like Lighthouse (in Chrome developer tools) to audit PWA performance, accessibility, best practices, and SEO.
• Strengthening security with Content Security Policy (CSP) to mitigate XSS attacks.
• Adding toast notifications for sync events or updates.
• Displaying offline indicators or visual cues when the user is offline.
• Implementing client-side analytics to understand how users interact with the app.
• Regularly updating Node.js, dependencies, and framework versions, but test carefully.

NOTE: Keep in mind that upgrading dependencies can sometimes break app functionality. For example, Tailwind CSS v3.4 is used in RxLookup, while v4 improves performance but drops support for older browsers (requires Safari 16.4+, Chrome 111+, Firefox 128+).

Summary

Hopefully, this blog post provides some insight into the development of a drug database app.

• For now, though, I feel too exhausted to start another offline PWA project.
• Honestly, I feel it is best to leave app development jobs to the professional coders.