building a sidebet website: the database

an exercise in building a toy in Google Cloud

I don’t really have an architecture diagram for this toy, which is currently supported by a spreadsheet.

Since this database is tiny, I figured I’d put the data in Cloud Memorystore where I get low-latency guarantees. Cloud Memorystore took about 3 minutes to enable the API. However, even at the smallest size, the cache costs $36/month. Hard pass.

With Cloud SQL, the pricing examples in the documentation indicate that test instances with 24/7 availability are $9/month. Better, but soft pass.

Just for completeness, Cloud Spanner with one node is minimum $657/month. Hard pass.

Cloud Firestore has a free tier that’s way above what I’d expect for usage (50k reads per day, 20k writes per day, and 1GB storage), so that’s the right choice. I selected native mode mainly because this is going to be a web application which means I’m not using any established Cloud Datastore practices. Most of the users are on the East coast so I’ll also choose us-east1 as the region.

It’s surprising that Cloud Firestore is also creating an App Engine app. I don’t plan on using App Engine at all so that’s weird.

I like that I can create a collection and add a document to that collection immediately since that speeds up the time to get the toy running. At this point, I’ll have to design a side bet. The design is basically in the spreadsheet, but I’ll add some auditing columns for when data changes.

  • for.bettor: the bettor who will win if the event happens
  • for.payout: the payout if the event happens
  • for.currency: the currency of the payout if the event happens
  • against.bettor: the bettor who will win if the event doesn’t happen
  • against.payout: the payout if the event doesn’t happen
  • against.currency: the currency of the payout if the event doesn’t happen
  • event: the event that must either happen or not happen
  • settleDate: when someone must record whether the event happened
  • happened: whether the event happened or not
  • paid: whether the payout was paid out
  • bettedTimestamp: when the bet was recorded
  • happenedTimestamp: when the event’s result was recorded
  • paidTimestamp: when the payout was recorded

Arguably, if Firestore has an audit trail, the last three don’t matter, but I’m adding them as an attempt to denormalize that auditing.

The first two documents are in, based on rows 2 and 3 of the spreadsheet. I’m currently using e-mail addresses for bettors, but I can convert to a foreign key later. The next step is to build an API that will first fetch bets by bettor. I’m not going to play with indices yet because Cloud Firestore already gives shallow indices for each field.

Cloud Firestore doesn’t have API documentation that has a Try It! side panel to quickly check API calls, so I’ll use the “web” snippets in the documentation on getting data from Firestore. I still create an API key that’s restricted to the Cloud Firestore API.

I learned via Deepak Vishawakarma on a StackOverflow answer that the start to using the web snippets in Chrome’s developer console is to first run

document.write("<script src=''></script><script src=''></script>")

which loads the Firebase client library and its sibling Firestore client library into the developer console. Then, I can proceed to get data from Firestore with

> var app = firebase.initializeApp({
  apiKey: '### COPIED API KEY ###',
  authDomain: '',
  projectId: 'ashrafulla-sidebet',
> database = firebase.firestore(app)
> database.collection("sidebets").get()

which failed with PERMISSION_DENIED. I can’t figure out the credentials in the developer console, and Cloud Firestore documentation references the Firebase user interface instead of the Cloud Console interface, so I enabled Firebase for ashrafulla-sidebet. Then, in the Firebase console, instead of there being just “Data” and “Indexes” there are also “Rules” and “Usage” tabs. The “Rules” tab immediately showed me the problem: the default rule for a created collection is

rules_version = '2';
service cloud.firestore {
  match /databases/{database}/documents {
    match /{document=**} {
      allow read, write: if false;

which means every database is unreadable and unwritable. Replacing false with true and then running database.collection("sidebets").get() successfully retrieved data.

That means the database is now working in some sense. The next step is to get that data onto a web page.

Published by using 691 words.