Help with API front end for base

[Howard_Goldstei]

I have a base that is a migration from a MySQL db and a web based (I think JS) app. I did not build the original and the person who did has disappeared. I am not a coder. Successfully migrated the data and created the proper links within the base, but the client really wants something pretty on the front end. The db is a list of photographs with info like size, medium, etc. and pieces sold for how much, how many in the series, etc. They want an interface to interact with the db like they had.

Is this doable and how might I go about it or might there be someone who is interested in doing the work?

Thanks in advance.

Howard Goldstein

[Bill_French]

Hi Howard,

Howard_Goldstein:

Is this doable

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?

Howard_Goldstein:

how might I go about it

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?

[Howard_Goldstei]

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

[Bill_French]

Howie,

Howard_Goldstein:

not familiar with FireBase

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.

Howard_Goldstein:

is FireBase a host for the data in a different form than Airtable?

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.

Howard_Goldstein:

The whole thing will be fairly simple … would also need an interface to add new records and edit records.

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.

Howard_Goldstein:

They are used to seeing something akin to FileMaker

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.

Howard_Goldstein:

Do you think this is something I could tackle without taking the next year to do it.

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.

[Howard_Goldstei]

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

[Bill_French]

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.

Howard_Goldstein:

In essence it sounds like working with Firebase would negate the need for Airtable?

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.

Howard_Goldstein:

Why would I want the data in two places?

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

Howard_Goldstein:

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?)

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.

[Howard_Goldstei]

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

[Bill_French]

Howard_Goldstein:

she is basically the only one interacting with this database.

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.

Howard_Goldstein:

how would I get started

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.

Howard_Goldstein:

is this a project you would take on and for how much?

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:

[Bill_French]

@Howard_Goldstein,

Have you looked at Table2Site?

[nea_lpatil]

@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