Help with API front end for base

Hi Howard,

Yeah, this is a common request and not unreasonable in the grand context of Airtable as platform for information workers. What you’re describing is simply an extended use case of the data and rendering it should be simple and straightforward using the Airtable API. Pretty much everything is doable - it’s a simple matter of time and money. :winking_face:

Here are some questions to consider as you unfurl the deeper requirements.

1. 
   Security - is there anything about this content that needs to be rendered in the context of user identity?


2. 
   Data Complexity - how many relationships (linking fields and tables) are involved?


3. 
   Size and Scale - how big is it and how big will it become?


4. 
   Categories and Filtering - is this one big flat list? Or, will the web all require some order and structure to the user interface?


5. 
   Search - will users need to search, and if so, full text? Keywords?


6. 
   Mobility - are there any users of this web ap who are likely to expect a modern mobile UI/UX?

I am biased - I build APIs and use them all the time. I’m not a great UI or UX designer, but I get by. My first inclination is to map the data for the site into a model that probably doesn’t resemble the current model in Airtable. I tend to do this to create “site-ready” content because web sites typically present information in ways that are far different than how information workers gather and manage the data. There’s another reason as well - more on that below (see API quota)

The site-ready data would be created using the Airtable API and would be refreshed daily (or some interval that is appropriate). I would probably lean towards a free instance of Firebase for the site-ready data as this would make the rendering process near-instant, and when you’re dealing with photos, you want to squeeze out all the latency possible.

Firebase would afford the ability to avoid cross-site scripting issues when going directly from a web page to the Airtable API. Using it’s real-time synchronization capabilities, this would provide the additional advantage of syncing the content directly to the browser client without risking separate web clients overtopping the Airtable quota limit of five requests per second. Any distributed interface using the Airtable API is likely to be unsustainable as more users create connections.

Lastly - the UI/UX - I would probably consider a React front end, but plain old HTML/JavaScript would also work well as Firebase doesn’t really care - it works everywhere and with everyone. This decision-point also dives into the nature of the app, the number of likely users, and the intended audience.

Make sense?

Bill-

Thank you for the detailed reply!

1. 
   Security - is there anything about this content that needs to be rendered in the context of user identity? No- this is really a private use db and probably only will have a few users, and generally just one at any time.


2. 
   Data Complexity - how many relationships (linking fields and tables) are involved? Basically there are 3 important tables with links to each other- a table describing the photo, a table with the image file and some other relevent info, and a table with a history of sales and quotes for the images.


3. 
   Size and Scale - how big is it and how big will it become? Currently the largest table is 35,000 records, the history table. The others are in the 2000 record range. I would expect maybe 10% growth a year at most.


4. 
   Categories and Filtering - is this one big flat list? Or, will the web all require some order and structure to the user interface? Not one big flat list(?)


5. 
   Search - will users need to search, and if so, full text? Keywords? Yes search but by two of the fields with structured choices.


6. 
   Mobility - are there any users of this web ap who are likely to expect a modern mobile UI/UX? No.

I am not familiar with FireBase but have heard of React. So is FireBase a host for the data in a different form than Airtable? React, I believe is a UX builder? The whole thing will be fairly simple- just a clean list of returned data- nothing fancy. The client has a hard time understanding how to utilize the links within Airtable once a record is found. They are used to seeing something akin to FileMaker for instance. Would also need an interface to add new records and edit records, again very simple. Do you think this is something I could tackle without taking the next year to do it. I am pretty well-versed in Unix Terminal tasks, HTML, etc., just no actual programming.

Thanks!

Howie

Howie,

It’s a Google product. Very powerful and typically a good choice for mobile app back-ends but is increasingly finding its way into mainstream applications where performance is crucial. We use it in Los Angeles for managing high-speed data synching to appliances running on public transit buses.

Yes, but it deserves a little deeper explanation. Firebase is comprised of five different compute systems - hosting, serverless-functions, real-time messaging, filestore, and datastore. As such, it represents a comprehensive [real-time] technology for web apps. It’s nice to have everything “infrastructure-related” in one security and API/SDK context.

And there it is. It’s no longer simple; you essentially want a web publishing platform that can ALSO create, edit, delete, filter, sort, and find data. :winking_face: CAUTION: Do not ask a consultant to build this without first thoroughly evaluating web content management and rendering platforms - even open-source platforms.

At the risk of making you revisit an old story, why not just use FileMaker if that’s what they have and what they like? There was apparently good reason to move from FileMaker to Airtable; color me curious.

In a courtroom, lawyers often ask questions that tend to put witnesses in positions they cannot possibly know; I think this is that moment. LOL The witness (me in this case) cannot speak to what you are thinking. I have no clue if you can be successful at doing this, but I have a hunch you know that much of software development is less about skills and more about the will to get it working.

My advice: Go for it. The worst that will happen is you’ll learn a bunch of new stuff, much of which will be very valuable knowledge.

ps - Have you given any thought to the challenge of data synchronicity that will occur when you have two different apps rendering from and updating to two data stores? Airtable offers no pricing tier for rates or services in excess of 5 API actions per second. Even the smallest user base of a dozen people could overtop this infrastructure limitation. As such, you must buffer your web content separately from Airtable.

Bill-

I appreciate all the info- truly!

The most important question you asked is why not stick with FileMaker. I already ported over a FileMaker db that I had built for them successfully. It was very simple- just a db of projects they had done. They can easily search and add records from the main Airtable interface without any fancy. The reason we moved away from FM is that they were not updating their application for the half dozen users who needed to access it remotely and no longer wanted to maintain the FM server. The strategy was also to give them one place for 3 dbs they use. The second is what I am working on now, kind of a stock photo or fine art sales application which was originally written as a web based application as I mentioned. They wanted some changes but had no access to the original programmer. It is only accessed by about 4 people. There will be a third application to be ported which is a DAM (Digital Asset Management) application- again out dated and used to house thumbnails and info about the images but not really to manage the original files or distribute them. Also only accessed by about 4 people. The ability to have all 3 dbs in one place web-based for remote access was the primary sell point of Airtable. I am pretty sure the only one of the dbs that requires something other than the Airtable SS style interface is the one I am now working on.

The data synchronicity issue worries me. In essence it sounds like working with Firebase would negate the need for Airtable? Why would I want the data in two places? Is there not a way to use React for example and the Airtable API to build the front end with Firebase in the middle, somewhat duplicating the function of Airtable (or am I missing something?)

Howie

Thanks for the underlying story of FileMaker and the lineage. I can relate to this as I have done a fair amount of FileMaker work in the past. Even worked with the documentation team back in the 90’s - yes, I’m that old. This was never going to be a great product with Apple as the custodian.

Actually, no. Firebase has no UI. Look at Airtable - it’s all UI [unfortunately]. It has an API, but there are some key issues using it in a web stack.

You don’t, and neither do I. Stay with me.

Set Firebase aside for a moment.

Imagine you created a React front end using the Airtable API. The web app is communicating with the Airtable database via its API and the skies are sunny, the birds are chirping - all is sweet. You complete the app development and deploy it on the web. Then you get a call - a dreaded call - from your client who says…

The web app continually fails. It seems that when 10 people get on the site at the same time, we can’t look at content or update anything. It’s constantly timing out and it’s very VERY slow most of the time. However, it seems to work great with just three users on it at the same time.

This is the moment you realize you made a huge mistake - you built a web app on an API that cannot handle more than 5 interactions per second before it automatically starts throttling your requests. Now you need Firebase (or something like it) to act as a buffer between live data in Airtable and slightly less live data in Firebase.

I can’t think of any other way to use Airtable as a backend to a web app that will have any chance of sustaining load - even a small load will cause Airtable to hit the ceiling.

But this is far from an indictment of the Airtable API - it is an immature API and the tool itself was never designed to be your web app backend. This is why Firebase (and others) exist. We simply need to be cautious of how we architect solutions and that all starts with questions and requirements concerning scale, performance, data persistence, and anticipated user loads.

Bill-

Continued thanks. Speaking with the client this morning to get a better idea of what she has issues with re: working directly within Airtable. My understanding is that she is basically the only one interacting with this database.

The next question is how would I get started or optionally is this a project you would take on and for how much?

More possibly later.

Howie

Yep - I get that. She’s chosen a way to move forward albeit a one person environment. This is simply her way of getting control of the data and while using a relatively productive tool to do so. This is not unreasonable and is typically the pathway that people (and eventually entire companies) find great value in Airtable as a solution.

Requirements development. As I mentioned earlier, if the requirements involve a full-fledged web site/content management system, best to make sure this is (a) well understood, and (b) researched to find the best-cost approach. It’s possible the perfect solution for her already exists and we need only develop an integration that pushes content outward - i.e., caches it forward for web consumption.

Indeed, I could help, but not until you, me, and the client knows a little more about the true cost.

Cost estimation accuracy is directly proportional to time spent hashing out the requirements. It’s like predicting hurricane tracks - 1 day out, you can be pretty sure; 10 days out and the tracking cone (i.e., cost) is very wide.

My recommendation is to define detailed minimum requirements for phase one and very broad requirements for all other phases (i.e., the wishlist).

Phase one should be as specific as possible and despite the added time it might require, it will pay good dividends at the implementation phase. It doesn’t have to be wordy; bullet points are very useful. But it must convey the functional expectation of the client.

Phase two and beyond - just a general one or two-sentence narrative of where this beast is going. This information will help any consultant understand if they’re building a Yak or an Elephant.

To get started, get a simple document going that includes the requirements - a few pages should cover it. Share it with me at bill.french@gmail.com (Google Docs is best because of the integrated commenting), or even better - outline this in Airtable and share. :winking_face:

@Howard_Goldstein,

Have you looked at Table2Site?

@Bill.French @Howard_Goldstein

Idea : Downloading csv files from airtable as opposed to getting it thr’ API .

CSV Files can be fed to destination backend.

Will it help ?

Neal

Sure, CSV export is one way to go. But there are some devils in the details.

• Airtable doesn’t have an automated feature to update CSV exports that I know of. The admin would have to re-export even when a simple price change is made in Airtable.


• The CSV files would have to be imported into something and that would require some code.


• CSV files do not contain relationship links to other records, so you’d still have to write code to re-establish those in the web app itself.

No easy answers.

Thank you nea_lpatil, but I think that would be very complicated for the client. The ultimate idea here is simplicity for the client.

Bill- still thinking about this. Appreciate your continued input.

Howie

I can build prototype very quickly based upon requirement details with the csv option.

Thanks,

Neal

Neal,

I’m confident that a CSV export/import could be achieved and this strategy is one that has been around and used for many decades so we fully understand it. But that’s a red flag - anything that has been around for decades is probably not going to take advantage of modern web or integration architectures or standards.

I use CSV files all the time and typically because I must, not because I can. Integrating with legacy systems that have no other option fall into this category. Some decades-old platforms can export and FTP CSV data automatically. Airtable can’t, nor should it (my opinion). APIs are far more precise for integration processes. Indeed, they are not as simple to implement, but employing them tend to make the connective tissue in software apps less brittle.

Stepping back, I think it’s also wise to define the requirements in advance of any specific implementation approach. Choosing the protocol for data sharing [now] is akin to selecting a V-8 engine for a new mall shuttle that’s on the design table.

Without knowing how this company plans to render information on the web, or how users will interact with the data, an integration implementation choice is a bit premature, right?

API rate limit limits number of options available for solution.

Neal

You’re conflating rate limits with design choices. It is possible to design performant web applications where API access is tightly constrained. It’s called a cache-forward design and it’s used all the time.

Imagine you have a single API account pulling records from Airtable and you need a web app that can support 10,000 simultaneous users all reading and searching the Airtable data.

Given that a direct connection to Airtable is unlikely to support the thousands of requests - many over the 5-per-second threshold - the API is unsuitable for direct use in this case. However, imagine a cache between the API and the front end. Imagine a real-time database (like Firebase) synchronizing Airtable data to all 10,000 users in less than 250 milliseconds. Airtable’s API need only update changes to a single endpoint and Firebase handles the distribution and access to all 10,000 users in near-real-time.

In this architecture, changes to the data by any single client are also synchronized back into Firebase in less than 250ms. Firebase’s real-time cloud functions handle the updates into Airtable at a pace orchestrated in a manner compliant with the API limits.

But, as I said earlier, it’s premature to conclude how wide (or narrow) the implementation approaches are until you know the true requirements.

@Howard_Goldstein,

You may want to sign up for this beta. It appears to meet some of your needs - perhaps many of them.

Bill-

Signed up. It looks like it might do the trick. Thanks! Will let you know how things work out.

Howie