Webpage caching preventing pages from updating

[ogallagher]

The amount of webpage caching seems to have increased since I was last working on the site, as now (in Safari at least) I have to clear the browser cache every time I add new content to the site.

I've updated the db schema file to have a new updated table, so next items will be:

• [x] apply it to the database
• [x] create add_update(item_table varchar, item_id varchar) procedure
• create triggers to automatically insert/update item versions in the updated table. Not possible to create new triggers with Dreamhost the shared-server database!

• 113

• [x] create db api method for fetching the latest version of any item
• [x] include version as an argument in db client methods to prevent using older cached results
  • fetch_user → fetch_update('people', 'username') This endpoint is apparently only called server side.
  • [x] fetch_user_details → fetch_update('people', 'username')
  • [x] fetch_user_exists → fetch_update('people', 'username')
  • [x] fetch_puzzle, fetch_puzzle_paths → fetch_update('puzzles', 'puzzle_id')
  • [x] fetch_rating → fetch_update('ratings', 'username,puzzle_id')
  • [x] fetch_difficulty → fetch_update('difficulties', 'username,puzzle_id')
  • [x] fetch_user_plays → fetch_update('plays', 'username,puzzle_id').
  • [x] fetch_user_activity → fetch_update(?) This combines fetches from plays, ratings, difficulties, and works, so ideally the version is a combination of these. Fetch versions separately and combine on client side?
  • [x] fetch_user_records → fetch_update('plays', 'username') _This requires that play→add_update includes an update item id without the puzzle id._
  • [x] fetch_works → fetch_update('works', 'username') _This will require an update to the update_works,contribute endpoint triggers, which currently only have works versions by work id._
  • [x] fetch_work_text → fetch_update('works', 'id') _Note item id is not work_id here, only because that's how the update_works→add_update trigger is currently implemented._
  • [x] fetch_work_fragments, fetch_puzzle_fragments → fetch_update('fragments', 'work_id'), fetch_update('fragments', 'puzzle_id') _This is not yet supported automatically; when new puzzles are created (outside website so far), the corresponding add_update() calls will need to be done manually._

After implementing db query versions, update the references to source/asset files to include versions as well. The best way to do this is probably to change the file paths themselves, forcing all pages that reference them to use the updated paths. I could additionally create symlinks to represent the versioned targets for backwards compatibility.

• [x] /js/dbclient.js → /js/dbclient-<version>.js
• [x] etc

[ogallagher]

One way to prevent unnecessary caching would be to maintain a last_updated table, with datetimes corresponding to each table (or rows in a table). These datetimes would then be used as a versioning system for each db api query as part of the query parameters (ex. /db?query=apple&args=[1,2,3]&version=20220510084153).

[ogallagher]

Just reconfirmed that adding contributions does not update the account page unless I clear the site cache in mobile Safari.

[ogallagher]

I think this is also preventing account edits from showing in the webpage (ex. biography text, links).

[ogallagher]

During testing I realized there was an error in the add_update db procedure where I was using a column name instead of the corresponding procedure argument. Currently, the source code for such db items exists only in my local environment, but if not in this GitHub repo, it should at least exist somewhere else that's easily recoverable.