Creating a public web view of an entire base -- caching and filtering ideas?

Check out the Cloudflare Proxy Script running on Cloudflare workers:
https://builtonair.com/resource/airtable-proxy-cloudflare-worker/

I believe it handles caching automatically within Cloudflare workers, but you’ll want to verify.

Thanks for the suggestion @openside, but that seems to require a paid plan on Cloudfare, which we are trying to avoid at the moment – sticking to our existing php hosting.

Hence some trickery with exported json files will be the direction, probably. Anyone knows any good techniques to query this sort of data? Be it on the clied side or server side …

@Chester_McLaughlin’s excellent Airpress Wordpress plugin provides an easy-to-use web interface to Airtable — especially for query-and-display applications. It’s been a while since I used it, but as I recall it performs some degree of caching and flow control transparently. It requires Wordpress, obviously, but if you’re already on php hosting, turning up a basic Wordpress implementation is an hour’s work.

There are only two hard things in Computer Science: cache invalidation and naming things.

That said, the way I handle caching in Airpress is by creating a hash of the exact request sent to the API. Then I use a “refresh” and “expire” limit to deal with invalidation.

When the “refresh” limit (in seconds) is reached, the existing cached data is served while an asynchronous request is made to refresh the cached data. When the “expire” limit is reached the page-load must wait while new data is fetched.

To me, this was a reasonable approach for Wordpress sites that may have high traffic (and will utilize the refresh limit mostly) as well as low traffic sites that need caching but don’t want to serve old data.

Paging is the most difficult thing to deal with as Airtable will only return a maximum of 100 records per request. This is a problem because if you cache “page 4” of the results separately from “page 5” you’ll get duplicate (or missing) records when records are added / removed.

To get around this in Airtable I actually don’t do paging at all!! Gasp. I know. Instead, I’ll loop a request getting 100 at a time until all records are fetched and cache that result with the original request.

On the plus side, adding/deleting/updating records doesn’t mess with the cached records, but on the downside “showing all records” could be problematic from a memory, processing, or even storage standpoint.

However, most queries I’ve ever written against Airtable are filtered/limited to some sane number of records any way (by the way, I think 2000 records is perfectly sane, it’s only 20 requests!). And if that number starts getting high, then I do progressive requests where the first request is for the Record ID and Created At fields only, I then do subsequent requests for specific records (or batches of records) based on what the interface needs.

Beyond that, I would recommend creating an import process (as opposed to a caching process) that cycles through all your Airtable data at a given rate and imports (creates/updates/deletes) a “local” database and never have your application touch Airtable outside that sync/import. That way you’ll have your own “local” that can handle paging, caching, etc (and should be as performant as you want) while still being able to frequently update that data from Airtable. The limit here would be something like 30,000 records a minute ( 5 requests per second * 100 records per request * 60 seconds a minute).

What I do on other projects is create my import script and schedule it to run every 15 minutes. My longest (unoptimized) script takes about 5 minutes to run through several thousand records, fetching related records, updating the local DB, etc.

Hit me up if you want to talk further.

Rather than Vue, I use Elm (which compiles to Javascript) for the frontend. I export my Airtable bases to JSON files, using curl and the command-line JSON processor jq to merge the JSON exports, and to strip out (or rename) fields that I don’t want made public.

(I’ve also written this same code in Elixir to allow greater control, and to be more maintainable as I add more pre-processing steps. I also use Elixir to repeatedly load data into Airtable since it’s resilient, with supervisors that reboot it and keep it running 24/7.)

If you’re on the free Airtable plan, you should be able to handle its data limits on the front end with live filtering, etc. It really makes a smooth experience, since all the data is in memory. For larger sites, I “shard” my data into separate bases and load as necessary as the user browses that part of the website.

The other benefit of having your Airtable data cached is that your Airtable doesn’t need to be in a consistent state. In fact, it would be wonderful if the API permitted you to query a specific snapshot, but you can also achieve that by making a stable backup of your bases.