The prototype I built for my team

I was tasked with building a UI for a work project our team took on. The UI would need to display audience data in a table format and get that data from a Postgres database.

The prototype needed to be easy to use, as well as functional allowing a no techie user to create audience segments easily. Allow them to target the segments later on for one of their advertising campaigns.

When deciding what tech to use, I went to straight to what I know and that was React and TailwindCSS. The only back end I have build before was for my ReactFastContacts app, which was a FastAPI backend with an SQLite database. I knew I wanted a database where I can use JS so I didn't have to worry about hosting the backend anywhere or creating any sort of API.

I found supabase and thought it would be perfect for the task at hand, and it was. Being an open-source product the community around supabase is awesome, with tones of help and content to learn. Another reason I knew I picked the right product for the job.

Creating the database

Before this project, I have never heard of an ERD (Entity–relationship model diagram) there are some good articles online about them however I found this one good enough, plus the videos help explain them a bit further.

I was giving the table configurations via an Excel spreadsheet, with table names, column names and so on. Once I got my head around it I created the following ERD.

I used the supabase UI to create the tables and all the relationships, which was pretty easy. Having said that I use DataGrip daily at work, and wrote some SQL to re-create the tables if needed along the lines of

CREATE TABLE "SignalJourneyAudiences"
(
    audience_id serial
        CONSTRAINT signaljourneyaudiences_pk
            PRIMARY KEY,
    segment     varchar,
    enabled     bool
);

CREATE UNIQUE INDEX signaljourneyaudiences_audience_id_uindex
    ON "SignalJourneyAudiences" (audience_id);

CREATE TABLE "SignalJourneySources"
(
    source_id serial
        CONSTRAINT signaljourneysource_pk
            PRIMARY KEY,
    source    varchar
);

...

The UI

Now the backend is up and running, it's time to work on the user interface. The fun part, is the React part. I took the chance to use Vite for this project due to the fact I didn't really need all the bells and whistles that came with something like NextJs. Using Vite was a blast it's pretty simple to use and add on top of.

The UI itself is pretty simple it's just a table with a form that populates some data after the user has submitted it to the database. As I was already using Tailwind I wanted to bring some life to the form and make things look decent. This is where headless.ui came in allowing me to make decent looking form components. I went ahead a built a couple of Listbox components to give the form a better feel. The headless ui library was awesome to use and makes forms and other little components a joy to build. You're even able to combine certain components within each other.

The data

With the form and table more less coded up and looking good, it's time to populate the UI with some data. Supabase makes this super easy with supabase-js all that is required to get going is to create a connection client like so:

First install the supabase-js package

npm install @supabase/supabase-js

Then simply create a client in a seperate file within your project.

import { createClient } from '@supabase/supabase-js'

// Create a single supabase client for interacting with your database
const supabase = createClient('https://xyzcompany.supabase.co', 'public-anon-key')

Then just import that into the files that you need to connect to your database.

The table was where I needed to join a few tables together to get the desired result, in SQL it was pretty straight forward, especially with the auto-completion from DataGrip. I needed to re-create the following SQL query in supabase.

SELECT
     constraint_id,
     segment,
     source,
     constraint_type,
     constraint_value,
     targeting,
     frequency,
     period
FROM "SignalJourneyAudienceConstraints"
JOIN "SignalJourneyAudiences" sja ON sja.audience_id = "SignalJourneyAudienceConstraints".audience_id
join "SignalJourneySources" sjs ON "SignalJourneyAudienceConstraints".source_id = sjs.source_id
join "SignalJourneyConstraintType" sjct ON "SignalJourneyAudienceConstraints".constraint_type_id = sjct.constraint_type_id;

Now time to convert that into a supabase query. Another good thing about supabase is that after creating your tables and relationships, supabase gives you API documentation on how to get what you need from your database.

const {data, error} = await supabase
      .from('SignalJourneyAudienceConstraints')
      .select(
        `
      constraint_id,
      audience_id:SignalJourneyAudiences(audience_id),
      segment:SignalJourneyAudiences(segment) ,
      source:SignalJourneySources(source) ,
      constraint_type:SignalJourneyConstraintType(constraint_type),
      constraint_value,
      targeting,
      frequency,
      period
    `,
      )
      .order('constraint_id', {ascending: true})

    if (data) {
      setTableData(data)
    }
    if (error) {
      setErrorMessage(error.message)
    }
  }

Head over to joins for more information about joins in supabase. With my above query, I learned a few things...

Using the syntax above allows you to query the same foreign table twice. In this case, you can use the name of the joined column to identify which join you intend to use.

How I understood the query

<col you want to join>:<table to join from>(<the FK from joining table>)

When trying to use the data the joins come back as objects like audience_id: {audience_id: 123 } which did throw me when trying to access the data but nothing dot notation can't fix.

Overall my use of supabase was brilliant, the syntax was very simple to use the documentation was brilliant and all in all, supabase was a pleasurable experience.

The UI with data

The UI is done the data is populated but now I had to give some user feedback when it came to messing with the data, for example.

• How can a user delete a row from the table?
• How can a user enable / disable an audience segment?
• How to present a user with success / error messages?

With React and supabase these two tasks were pretty straightforward, here is how I used supabase to delete a row from the table.

const deleteRow = async constraint_id => {
    const {data, error} = await supabase
      .from('SignalJourneyAudienceConstraints')
      .delete()
      .match({constraint_id: constraint_id})

    if (data) {
      popupValidation('success', 'Constraint deleted successfully')
      window.location.reload()
    }
    if (error) {
      popupValidation('error', error.message)
    }
  }

Using the .delete() method with the match() allowed me to delete a row via an ID the ID being the primary key. As you can see the function is pretty simple this is how easy it was to use supabase.

I used something similar to enable/disable audience segments but used the .update() method instead, which allowed me to update records. I created one function to enable and another to disable like so...

const enableAudience = async audience_id => {
    const {data, error} = await supabase
      .from('SignalJourneyAudiences')
      .update({audience_id: audience_id, enabled: true})
      .match({audience_id: audience_id})

    if (data) {
      window.location.reload(true)
    }
    if (error) {
      popupValidation('error', error.message)
    }
  }

  const disableAudience = async audience_id => {
    const {data, error} = await supabase
      .from('SignalJourneyAudiences')
      .update({audience_id: audience_id, enabled: false})
      .match({audience_id: audience_id})

    if (data) {
      window.location.reload(true)
    }

    if (error) {
      popupValidation('error', error.message)
    }
  }

I then used another function with some conditional logic which would check if a segment was enabled or disabled, then fire off the correct function to make the update.

const handleEnableDisableAudience = async audience_id => {
    segments.map(segment => {
      if (audience_id === segment.audience_id && segment.enabled === false) {
        enableAudience(audience_id)
      }
      if (audience_id === segment.audience_id && segment.enabled === true) {
        disableAudience(audience_id)
      }
    })
  }

I then used a ternary operator with React Icon to give some user feedback to let them know if the audience segment was enabled or disabled. Which on click would fire the function to check if it was enabled/disabled then run the correct function to swap the state.

<BadgeCheckIcon
  className={`h-6 w-6 ${
    segment.enabled ? 'text-green-400' : 'text-red-500'
  } hover:cursor-pointer hover:text-gray-500`}
  onClick={() => handleEnableDisableAudience(segment.audience_id)}
/>

When it came to handling errors or success messages for the user, I had to think of something new as this wasn't something I had touched before. Here I created some states using useState if we take the success state for example it went something like this.

const [success, setSuccess] = useState(false)
const [successMessage, setSuccessMessage] = useState('')

This allowed me to create a function that would use those states to set messages for a popup that was positioned in the top right of the screen. Doing it this way allowed me to set the states of the message anywhere throughout the component. I created myself a function that could handle all the different states.

const popupValidation = (type, message) => {
    if (type === 'success') {
      setLoading(false)
      setSuccess(true)
      setSuccessMessage(message)

      setTimeout(() => {
        window.location.reload()
      }, 2000)
    } else if (type === 'warning') {
      setLoading(false)
      setWarning(true)
      setWarningMessage(message)

      setTimeout(() => {
        setWarning(false)
        setLoading(false)
      }, 2500)
    } else if (type === 'error') {
      setLoading(false)
      setError(true)
      setErrorMessage(message)

      setTimeout(() => {
        setError(false)
        setLoading(false)
      }, 2500)
    }
  }

That in turn was called like so.

if (data) {
  popupValidation('success', 'Successfully added new audience constraint')
}
if (error) {
  popupValidation('error', error.message)
}

Now I am sure for the next bit there is an easier way of doing it, however for me this is what I came up with. I used a ternary operator for every state like this.

{
  success ? (
    <div
      className="fixed top-5 right-5 z-40 rounded-b-lg border-t-4 border-green-500 bg-green-100 px-4 py-3 text-green-900 shadow-md"
      role="alert"
    >
      <div className="flex">
        <div className="mr-3 py-1">
          <LightningBoltIcon size="28" className="h-8 w-8" />
        </div>
        <div>
          <p className="font-bold">Success</p>
          <p className="text-sm">{successMessage}</p>
        </div>
      </div>
    </div>
  ) : null
}

I tried to put the three of them into a component allowing me to use it through out my project without copying and pasting it. I haven't quite figured that out yet. It's on my ToDo list...

Conclusion

With this project, I learned a lot and I for sure haven't gone over everything but I have gone on enough. The project is still a work in progress some tweaks here and there are needed, with this project though I can safely say it did skyrocket my React skills I learnt.

• More about useEffect and how it works
• Using useState to enable the application to perform better
• TheuseRef hook
• supabase and all its wonders
• How to use headless ui in a project
• React router and created routers
• and plenty more.