Use the owner's Google Sheet as the only backend
Avoiding a server means the family keeps ownership of its data, can open or back up the sheet directly, and the product carries no hosting cost or data-retention liability.
Giftbook
website
A Bangla-language gift ledger for family events that keeps the record in the owner's own Google Sheet, works offline through a local IndexedDB copy, and lets relatives write to the same ledger by scanning a QR code.
- Role
- Product designer and frontend engineer
- Scope
- Product specification, Bangla-first interface design, local-first data model, Google Sheets synchronization, sharing workflow, PWA packaging, testing, and Netlify deployment
- Status
- Publicly deployed; Google sign-in limited to approved test users while the OAuth app remains in testing mode
Problem
Bangladeshi families track wedding, aqiqah, and birthday gifts in paper notebooks that are hard to search, easy to lose, and impossible for several relatives to update at once, while generic apps store the family's financial records on a third-party server and ignore Bangla numerals and lakh formatting.
Constraints
- C1
The ledger must live in the owner's Google Drive with no application server or database, so the app may request only the drive.file scope and never see the rest of the Drive.
- C2
Gift entry happens at crowded events with unreliable connectivity, so writes must land locally first and reach the sheet later without duplicating rows.
- C3
Several family members edit the same sheet from different phones, so the sync rule has to be simple, predictable, and safe when two devices change the same row.
- C4
The sheet remains a spreadsheet that people may edit by hand, so the app must detect header changes and stop syncing instead of corrupting data.
- C5
The whole interface, including numerals, dates, currency, and validation errors, must read naturally in Bangla rather than relying on browser defaults in English.
Architecture
A TanStack Start single-page PWA keeps a per-ledger Dexie database as the working copy, and a sync engine pulls, merges, and pushes rows against a three-tab Google Sheet whose columns are derived from Zod schemas.
01
Ledger domain
Zod schemas for events, people, and gifts define both form validation and the exact column order of the sheet, with every record carrying updatedAt, updatedBy, and a soft-delete marker.
02
Local persistence
Each ledger gets its own Dexie IndexedDB database; a single repository module is the only write path, and live queries feed the pages so edits appear instantly across tabs.
03
Sync engine
A pull, merge, save, push cycle runs after writes, on an interval, and when the device comes back online, resolving conflicts by last-write-wins on updatedAt and guarding against duplicate appends.
04
Google boundary
Typed Drive, Sheets, Picker, and Identity adapters sit behind one interface with a localStorage mock, so the complete workflow runs and is tested without credentials.
05
Sharing and export
Owners share a QR link that grants Drive permission on the sheet; members join through the link or Picker, and any event or the whole ledger exports to Excel or a print page.
Decisions
Write locally first, sync later
Dexie holds the working copy so entries never block on the network; the navbar reports synced, syncing, offline with a pending count, or needs attention.
Keep conflict resolution last-write-wins with soft deletes
Family ledgers rarely see simultaneous edits of the same row, so a simple, explainable rule beat a merge system; deleted rows stay in the sheet with a deletedAt timestamp.
Validate sheet headers before every sync
Only the schema's leading columns must match, so hand-added columns on the right are tolerated, while a renamed header halts sync with a clear Bangla banner instead of writing bad data.
Build a full mock of Google behind the same interface
Account chooser, Drive, Picker, and Sheets mocks make the entire product testable in a browser without an OAuth client and let two profiles rehearse the sharing flow.
Contribution
- Specified the product from an approved HTML prototype and rebuilt it in TanStack Start with TypeScript, Tailwind CSS v4, shadcn/ui, and Hugeicons across mobile, tablet, and desktop layouts.
- Designed the Zod-driven ledger schema that doubles as the sheet column contract, the per-ledger Dexie store, the single repository write path, and live-query hooks.
- Implemented the sync engine with header verification, last-write-wins merge, duplicate-append protection, and nine Node.js test runner cases covering rows, merge, and the full cycle.
- Built Google Drive, Sheets, Picker, and Identity Services adapters plus a complete localStorage mock, QR and link sharing with member management, Excel export, and a print view.
- Wrote the Bangla copy, numeral, date, and lakh-grouped currency formatting, custom form validation, PWA manifest and offline shell, social preview assets, and deployed to Netlify with secrets from Infisical.
Outcomes
- Families can record who gave what at weddings, aqiqahs, and birthdays from any phone, offline, and see per-event received, given, and unopened-envelope summaries.
- The ledger stays in the owner's Google Drive as an editable spreadsheet, with the app holding only drive.file access and no server-side copy.
- Multiple relatives write to the same ledger through a QR link, and conflicting edits resolve predictably without duplicate rows.
- The complete workflow, including sharing and sync failure simulations, runs in mock mode without Google credentials, and nine automated sync tests guard the merge and cycle logic.
- The app is live at giftbook.netlify.app as an installable PWA with a fully Bangla interface, including numerals and currency.
Product screenshot
Verified stack
- TanStack Start
- React 19
- TypeScript
- Tailwind CSS
- shadcn/ui
- Radix UI
- TanStack Form
- Zod
- Dexie
- IndexedDB
- Google Sheets API
- Google Drive API
- Google Identity Services
- SheetJS
- Vite PWA
- Netlify
- Infisical
- Node.js Test Runner
- System Design
Working through something similar?
Share your challenge, scope, and timeline to start a focused conversation.