Database design

[srliao]

THIS IS A WIP DOCUMENT DETAILING THE DATABASE REQUIREMENT AND PROPOSAL

Design

API endpoints

• /api/db:
  • / (GET): retrieve the entirety of the current db (should use search query for some basic filtering and pagination)
  • /admin: db administrative route; require sufficient authorization; for managing which sims to add to db
    • /approve/{share-key} (POST): approves simulation with sharekey for db.
    • list (GET): lists share-key of all simulations waiting for approval
    • reject/{share-key} (POST): rejects simulation with share-key
    • submit (POST): add new submission
    • /{share-key} (DELETE): deletes submission
  • /tag: tag administration; require sufficient authorization; for tagging sims
    • /{db-key} (PUT): tags a db simulation
    • /{db-key} (DELETE): deletes a db simulation
• /api/share:
  • / (POST): uploads a new share. can be temporary or permanent
  • /{share-key} (GET): retrieves a share by share-key
  • /preview/{share-key}.png (GET): retrieves the embed/preview image associated with share-key

Cloudflare Workers

Cloudflare Workers effectively act as a reverse proxy for the API. All traffic to *.gcsim.app/api/* will be directed to the worker.

For the following routes, cache will be applied:

• /api/db/ (GET)
• /api/share/{share-key} (GET)
• /api/share/preview/{share-key}.png (GET)

For /api/db specifically, the cache can be programmatically manipulated i.e. cache is purged on any changes to the db (calls to /api/db/admin/*).

Cloudflare worker also adds in an addition security layer by hiding the external IP of the backend behind Cloudflare Tunnel. This allows the backend to be locked down completely, denying access from all external traffic outside of the tunnel.

Note: although Cloudflare KV is included in the orignal diagram, currently it is not being utilized for the backend. Considering potentially storing temporary shares in KV instead of in the backend directly.

API Gateway

Provides a gateway for all HTTP REST API endpoints exposed to the Cloudflare worker. The gateway also defines interfaces for the different services.

Preview/Embed Service

Handles generation of preview/embeded images. Generated images should be stored in memory temproarily (memcache/redis). Image is generated based on some predefined metadata structure

DB Store

Handles storing simulation results for any sims submitted to the db along with associated metadata. Also provides endpoint for handling upgrades on new releases. This should be triggered via GitHub actions with a secret key.

Flow

Sharing temporary results

Client will POST a well formed JSON containing a simulation result to /api/share. If it is a temporary share, worker will store it in KV. Otherwise it will be stored in the user store db, subject to limitations on maximum number of permanent shares allowed for that user

Retrieving shared results