Seeking Advice on Data Architecture and AI Integration for a User-Interactive Web Application

Hello Glide Community,

I’m currently developing a web application using Glide and have encountered some challenges that I hope to get your insights on. My application’s core functionality revolves around user interactions and AI integration, and I’m struggling with the data architecture and workflow.

Application Overview:

  • User Accounts and Interactions: Users can create accounts, login, and share information about themselves.
  • Question-Answer Feature: Users are presented with questions and can record their answers. These answers are then transcribed and stored in the database.
  • AI Integration: After all questions are answered, the application sends the question and answers to OpenAI’s GPT, which generates a piece of output based on this input.
  • Displaying Output: This AI-generated output is then displayed on a page within the app.

Challenges and Questions:

  1. Data Structuring:
  • I’m trying to create a seamless structure with three main tables: Users, Questions, and Answers. The Questions table is standardized for all users, but the Answers table should be specifically linked to each user and their provided answers.
  • How can I structure these tables effectively in Glide to ensure smooth data flow and accessibility?
  1. AI Integration and Workflow:
  • The current challenge is integrating the AI component (GPT from OpenAI) in a way that allows me to send prompts including references to multiple columns within a table. Currently, I can only include one column (e.g., one answer) in the prompt sent to GPT, which limits the application’s functionality.
  • Is there a way to structure the workflow or data to send a comprehensive prompt to GPT that includes multiple data points from different columns?

I believe these challenges stem from a fundamental question about data structuring in Glide. Any advice, examples, or insights on how to effectively structure the data and integrate AI to fulfill these functionalities would be immensely appreciated.

Thank you in advance for your time and help!

Would users ever need to see each others results?
Or would you (as an Admin user) need to view/summarise the results of any/all users?

If the answer to both of the above is no, then you could combine both Questions and Answers into a single table, with User Specific Columns for the Answers.

If not, then the way to structure your Answers table to make it easiest to work with will be one row per user, per question. So you would have the following columns:

  • QuestionID
  • UserID
  • Answer/Response

The above raises a little red flag for me, as it suggests that you may have answers in multiple columns, rather than multiple rows. If that’s the case, you should consider changing that as it will not scale well, nor will it be easy to maintain. For example, anytime you decide you need another question, you’ll need to add and configure columns and screen components.

1 Like

Have you looked at Template columns :slight_smile: this is the easy way to include multiple column values into a single value to send to OpenAI (or use in the rest of the app). I use this all the time for my Pokkett platform, built on Glide.

Easy template example / column type template ….
Question XXX
Answer YYY

Replacing XXX and YYY with the column values you need.

Clever example (what I use)

  • lookup table with a value for my prompt “you are a clever AI and so analysis on RESULT”
  • in user table (I use all the time), single value to pull in the prompt from the lookup table
  • the magic - in the table where you do your analysis, single value to pick up the prompt text
  • then a template column … using that sv-column as your text…. And replace RESULT (maybe with another template column)
  • and then that value to OpenAI