TL;DR
Today I am going to build a contacts management system:
- You can add all your contacts from different resources from any type/size of file š¤Æ
- Edit them inline dynamically - like an Excel sheet š
- Get live updates when somebody else changes the worksheet ā¤“ļø
Let's do it š
Manage your contacts live šš»
We will build a cool Excel spreadsheet that can be updated live
For that, we must use Websockets or Server-Sent Events (SSE).
To simplify the process, we will use Supabase real-time.
What is Supabase real-time?
Supabase real-time is pretty neat.
Itās basically a Postgres database living up there in the cloud, and when something changes there, it sends an event through WebSockets about the new change.
You can learn more about WebSockets here.
Letās set it up š„
Letās start by initiating a new NextJS project.
npx create-next-app@latest contacts
We will not use the new app router for that project, so please select that you donāt want it.
To use Spreadsheets, letās install react-spreadsheet
. Itās a young library, but I have high hopes for it!
npm install react-spreadsheet --save
Letās open our index.tsx
inside of pages and add our data state and react-spreadsheet.
import Spreadsheet from "react-spreadsheet";
export default function Home() {
const [data, setData] = useState<{ value: string }[][]>([]);
return (
<div className="flex justify-center items-stretch">
<div className="flex flex-col">
<Spreadsheet darkMode={true} data={data} />
</div>
</div>
);
}
Ok, so not much to see, be we will get there.
The react-spreadsheet
comes out of the box with the option to modify the column within.
But itās missing the option to:
- Add new columns
- Add new rows
- Remove columns
- Remove rows
So letās add those, but before we do, there is a small thing we have to take care of.
We donāt want to spam Supabase with changes on every single word change.
The easiest way is to use a debouncer.
Debouncer who?
A debouncer is a way to tell our function - activate me after X time has passed since I was triggered.
So if the users try to change the text, it will only trigger the function 1 second after they finish.
Letās install the debouncer:
npm install use-debounce --save
And import it to our project:
import { useDebouncedCallback } from "use-debounce";
Not we can create our update function
const debouncer = useDebouncedCallback((newData: any, diff) => {
setData((oldData) => {
// update the server with our new data
updateServer(diff);
return newData;
});
}, 500);
As you can see, the debouncer updates our data from the state, but the function will only activate 500ms after the user triggers the function.
The main problem is that the debouncer is unaware of the data mutation (data
) by reference.
And because it doesnāt know, itās better to check it before.
So here is the function that gets the new data from the <Spreadsheet />
, and if something really changes, it will trigger our debouncer.
const setNewData = (newData: {value: string}[][], ignoreDiff?: boolean) => {
// This function will tell us what actually changed in the data (the column / row)
const diff = findDiff(data, newData);
// Only if there was not real change, or we didn't ask to ignore changes, trigger the debouncer.
if (diff || ignoreDiff) {
return debouncer(newData, diff);
}
};
Now, letās write the findDiff
function.
Itās a simple comparison between 2 two-dimensional arrays.
const findDiff = useCallback(
(oldData: { value: string }[][], newData: { value: string }[][]) => {
for (let i = 0; i < oldData.length; i++) {
for (let y = 0; y < oldData[i].length; y++) {
if (oldData[i][y] !== newData[i][y]) {
return {
oldValue: oldData[i][y].value,
value: newData[i][y].value,
row: i,
col: y,
};
}
}
}
},
[]
);
And now š„, we can let our Spreadsheet update our data!
<Spreadsheet
darkMode={true}
data={data}
className="w-full"
onChange={setNewData}
/>
As I said before, react-spreadsheet
is not mature enough yet, so letās build our missing functions.
// Add a new column
const addCol = useCallback(() => {
setNewData(
data.length === 0
? [[{ value: "" }]]
: data.map((p: any) => [...p, { value: "" }]),
true
);
}, [data]);
// Add a new row
const addRow = useCallback(() => {
setNewData(
[...data, data?.[0]?.map(() => ({ value: "" })) || [{ value: "" }]],
true
);
}, [data]);
// Remove a column by index
const removeCol = useCallback(
(index: number) => (event: any) => {
setNewData(
data.map((current) => {
return [
...current.slice(0, index),
...current.slice((index || 0) + 1),
];
}),
true
);
event.stopPropagation();
},
[data]
);
// Remove a row by index
const removeRow = useCallback(
(index: number) => (event: any) => {
setNewData(
[...data.slice(0, index), ...data.slice((index || 0) + 1)],
true
);
event.stopPropagation();
},
[data]
);
Now letās add the buttons to add a new row and column
<div className="flex justify-center items-stretch">
<div className="flex flex-col">
<Spreadsheet
darkMode={true}
data={data}
className="w-full"
onChange={setNewData}
/>
<div
onClick={addRow}
className="bg-[#060606] border border-[#313131] border-t-0 mb-[6px] flex justify-center py-1 cursor-pointer"
>
+
</div>
</div>
<div
onClick={addCol}
className="bg-[#060606] border border-[#313131] border-l-0 mb-[6px] flex items-center px-3 cursor-pointer"
>
+
</div>
</div>
The next part is tricky š²
As I said before, the library is a little immature:
<div className="flex justify-center items-stretch">
<div className="flex flex-col">
<Spreadsheet
columnLabels={data?.[0]?.map((d, index) => (
<div
key={index}
className="flex justify-center items-center space-x-2"
>
<div>{String.fromCharCode(64 + index + 1)}</div>
<div
className="text-xs text-red-500"
onClick={removeCol(index)}
>
X
</div>
</div>
))}
rowLabels={data?.map((d, index) => (
<div
key={index}
className="flex justify-center items-center space-x-2"
>
<div>{index + 1}</div>
<div
className="text-xs text-red-500"
onClick={removeRow(index)}
>
X
</div>
</div>
))}
darkMode={true}
data={data}
className="w-full"
onChange={setNewData}
/>
<div
onClick={addRow}
className="bg-[#060606] border border-[#313131] border-t-0 mb-[6px] flex justify-center py-1 cursor-pointer"
>
+
</div>
</div>
<div
onClick={addCol}
className="bg-[#060606] border border-[#313131] border-l-0 mb-[6px] flex items-center px-3 cursor-pointer"
>
+
</div>
</div>
The columnLabels
and rowLabels
expect to get back an array of strings, but we give it an array of components š
You might need to use it with @ts-ignore
, so this is how it should look now:
Everything runs locally (ATM), letās send a request to the server with our update š
First, letās install axios
npm install axios --save
import it:
import axios from "axios";
And write our updateServer
function!
const updateServer = useCallback(
(serverData?: { value: string; col: number; row: number }) => {
if (!serverData) {
return;
}
console.log(serverData);
return axios.post("/api/update-record", serverData);
},
[]
);
Supabase time! ā°
Head to Supabase and register.
Go to projects and add a new Project.
Now go to the SQL editor and run the following query.
CREATE TABLE public."values" (
"id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
"row" smallint DEFAULT '0'::smallint,
"column" smallint DEFAULT '0'::smallint,
"value" text,
UNIQUE ("row", "column")
);
This query creates the table values
that contain the row
and column
numbers in our Spreadsheet. We also added a UNIQUE
key on both (together) rows and columns because we can only have one match in our DB. We can upsert to the table since we mark them both in UNIQUE
. So if the value exists, we just update it.
Since we will do SELECT
queries from the client, letās give the SELECT
permission to everybody and then enable RLS
.
Now letās review our settings and copy our anon
public and service role
secret keys.
Create a new file inside of your project called .env
touch .env
And add the keys inside
SECRET_KEY=service_role_key
NEXT_PUBLIC_ANON_KEY=anon_key
Now letās install supabase-js
npm install @supabase/supabase-js
Create a new folder called helpers
, add a new file called supabase.ts
, and add the following code:
import {createClient} from "@supabase/supabase-js";
// You can take the URL from the project settings
export const createSupabase = (key: string) => createClient('https://IDENTIFIER.supabase.co', key);
Now create a new folder inside of pages
called api
(most likely, the folder exists already).
Create a new file called update-record.ts
and add the following code:
import type { NextApiRequest, NextApiResponse } from "next";
import { createSupabase } from "@contacts/helpers/supabase";
const supabase = createSupabase(process.env.SECRET_KEY!);
export default async function handler(
req: NextApiRequest,
res: NextApiResponse
) {
if (
req.method !== "POST" ||
typeof req.body.col === "undefined" ||
typeof req.body.row === "undefined" ||
typeof req.body.value === "undefined"
) {
res.status(400).json({ valid: false });
return;
}
const { data, error } = await supabase
.from("values")
.upsert(
{
column: req.body.col,
row: req.body.row,
value: req.body.value,
},
{
onConflict: "row,column",
}
)
.select();
res.status(200).json({ valid: true });
}
Letās see whatās going on here.
We import the previously created supabase.ts
file and initiate a new instance with our SECRET_KEY
- this is important because only with our SECRET_KEY
can we mutate the database.
In the route, we check that the method is POST
and that we have values in col, row, and value.
Itās important to check for undefined
because we might get 0
or empty values.
Then, we do an upsert
query that basically adds the row, column, and value, but if it exists, it just updates it.
Now letās listen to changes on the client side and update our spreadsheet.
Import superbase again, but this time we will use the ANON
key
import { createSupabase } from "@contacts/helpers/supabase";
const supabase = createSupabase(process.env.NEXT_PUBLIC_ANON_KEY!);
And now, letās add a useEffect to our component:
useEffect(() => {
supabase
.channel("any")
.on<any>(
"postgres_changes",
{ event: "*", schema: "public", table: "values" },
(payload) => {
console.log(payload.new);
setData((odata) => {
const totalRows =
payload?.new?.row + 1 > odata.length
? payload.new.row + 1
: odata.length;
const totalCols =
payload.new?.column + 1 > odata[0].length
? payload.new?.column + 1
: odata[0].length;
return [...new Array(totalRows)].map((_, row) => {
return [...new Array(totalCols)].map((_, col) => {
if (payload.new.row === row && payload.new?.column === col) {
return { value: payload?.new?.value || "" };
}
return { value: odata?.[row]?.[col]?.value || "" };
});
});
});
}
)
.subscribe();
}, []);
We subscribe to the values
table and update our data anytime we get changes.
Letās look at a few highlights here.
data
format usually looks something like this:
[
[row1_col1, row1_col2, row1_col3, row1_col4],
[row2_col1, row2_col2, row2_col3, row2_col4]
]
But what happens if we get row2_col4
but we donāt have row2_col1
, row2_col2
, row2_col3
?
So to solve that, we just need to check for the highest row and the highest col and create a 2-dimensional array with the values.
The [ā¦new Array(value)]
is a cool trick to create an array with empty values in your desired size.
Awesome šš»Ā we have built the entire contacts system, but thatās not the end!
Letās import all your contacts from other resources š
Even if you have thousands of contacts, we can easily add them using FlatFile!
FlatFile is the easiest, fastest, and safest way for developers to build the ideal data file import experience. Those are the steps we are going to take:
- We add the FlatFile React Component to load any file type (CSV / XSLX / XML, etc.)
- We create a function that processes this file and insert the contacts into our database.
- We deploy the function to the cloud, and FlatFile will take care of everything without our need to maintain it anymore š¤Æ
So go ahead and register to Flatfile, head to settings and copy the Environment ID
, Publishable Key
, and Secret Key
And paste them into our .env
file.
NEXT_PUBLIC_FLAT_ENVIRONMENT_ID=us_env_
NEXT_PUBLIC_FLAT_PUBLISHABLE_KEY=pk_
FLATFILE_API_KEY=sk_
The Space š½
FlatFile has a concept called Spaces
, which are micro-applications, each with its own database, filestore, and auth.
Inside each space are different WorkBooks
, which are basically a group for different spreadsheets.
Each time we want to load contacts, we will create a new space with one workbook and one sheet.
Now letās install the FlatFile React component!
npm install @flatfile/react --save
Letās create a new folder called components
, and create our file importer.
mkdir components
cd components
touch file.importer.tsx
And then create a button to import our contacts
const FileImporterComponent: FC<{ data: string[] }> = (props) => {
const { data } = props;
const [showSpace, setShowSpace] = useState(false);
return (
<div className="flex justify-center py-5">
<button
className="bg-violet-900 p-3 rounded-3xl"
onClick={() => {
setShowSpace(!showSpace);
}}
>
Import Contacts
</button>
{showSpace && (
<div className="fixed w-full h-full left-0 top-0 z-50 text-black">
<div className="w-[80%] m-auto top-[50%] absolute left-[50%] -translate-x-[50%] -translate-y-[50%] text-black space-modal">
<FlatFileComponent
data={data}
closeSpace={() => setShowSpace(false)}
/>
</div>
</div>
)}
</div>
);
};
export default FileImporterComponent;
As you can see, we are passing a parameter called data
, thatās basically the name of all our headers (the first row in our spreadsheet) from the previous step.
We will send them to FlatFile, and FlatFile will try to guess which field belongs to which field š
Once we click on the Import Contacts button, it will open the FlatFile components.
Now letās create our FlatFile component:
const FlatFileComponent: FC<{ data: string[]; closeSpace: () => void }> = (
props
) => {
const { data, closeSpace } = props;
const theme = useMemo(() => ({
name: "Dynamic Space",
environmentId: "us_env_nSuIcnJx",
publishableKey: process.env.NEXT_PUBLIC_FLAT_PUBLISHABLE_KEY!,
themeConfig: makeTheme({ primaryColor: "#546a76", textColor: "#fff" }),
workbook: {
name: "Contacts Workbook",
sheets: [
{
name: "ContactSheet",
slug: "ContactSheet",
fields: data.map((p, index) => ({
key: String(index),
type: "string",
label: p,
})),
},
],
actions: [
{
label: "Submit",
operation: "contacts:submit",
description: "Would you like to submit your workbook?",
mode: "background",
primary: true,
confirm: true,
},
],
},
} as ISpace), [data]);
const space = useSpace({
...theme,
closeSpace: {
operation: "contacts:close",
onClose: () => closeSpace(),
},
});
return <>{space}</>;
};
Letās see whatās going on here:
- We use the React hook of
useSpace
to initiate a new FlatFile wizard. - We pass the
environmentId
andpublishableKey
that we got from the settings. - We map the
fields
from the name of our headers. In thekey
I pass the header index, so when I insert it later toSupabase
I know the column number. - We set an action of
submit
, and we set themode
to be background because we donāt want to process the data over the front (we basically canāt because ourAnon
user doesnāt have access toINSERT
into our database).
Letās add our component to our main page.
FlatFile uses the window
object. Since we are using NextJS, we cannot access the window object during server rendering. We have to use a dynamic import to add it:
import dynamic from "next/dynamic";
const FileImporterComponent = dynamic(() => import("../components/file.importer"), {
ssr: false,
});
return (
<>
{!!data.length && <SpaceComponent data={data[0].map((p) => p.value)} />}
<div className="flex justify-center items-stretch">
<div className="flex flex-col">
<Spreadsheet
columnLabels={data?.[0]?.map((d, index) => (
<div
key={index}
className="flex justify-center items-center space-x-2"
>
<div>{String.fromCharCode(64 + index + 1)}</div>
<div
className="text-xs text-red-500"
onClick={removeCol(index)}
>
X
</div>
</div>
))}
rowLabels={data?.map((d, index) => (
<div
key={index}
className="flex justify-center items-center space-x-2"
>
<div>{index + 1}</div>
<div
className="text-xs text-red-500"
onClick={removeRow(index)}
>
X
</div>
</div>
))}
darkMode={true}
data={data}
className="w-full"
// @ts-ignore
onChange={setNewData}
/>
<div
onClick={addRow}
className="bg-[#060606] border border-[#313131] border-t-0 mb-[6px] flex justify-center py-1 cursor-pointer"
>
+
</div>
</div>
<div
onClick={addCol}
className="bg-[#060606] border border-[#313131] border-l-0 mb-[6px] flex items-center px-3 cursor-pointer"
>
+
</div>
</div>
</>
);
Once you save everything, you should see something like this:
Awesome!
The only thing left is to load everything into our database.
Letās install some FlatFile dependencies
npm install @flatfile/listener @flatfile/api --save
Create a new file called listener.ts
This is a special file that listens to file imports.
Letās import FlatFile and Supabase.
import { FlatfileEvent, Client } from "@flatfile/listener";
import api from "@flatfile/api";
import { createSupabase } from "./src/database/supabase";
const supabase = createSupabase(process.env.SECRET_KEY!);
We can add our listener of contacts:submit
that we have coded in the previous steps:
export default function flatfileEventListener(listener: Client) {
listener.filter({ job: "workbook:contacts:submit" }, (configure) => {
configure.on(
"job:ready",
async ({ context: { jobId, workbookId }, payload }: FlatfileEvent) => {
// add to supabase
}
);
});
}
To insert the new values, we need to take the highest row currently in the DB and increment it.
const row = await supabase
.from("values")
.select("row")
.order("row", { ascending: false })
.limit(1);
let startRow = row.data?.length ? row.data[0].row + 1 : 0;
Then we take all the records in the imported file
const { data: sheets } = await api.sheets.list({ workbookId });
const records = (await api.records.get(sheets[0].id))?.data?.records || [];
We fetch and add them to our database.
We also use the api.jobs.ack
call to inform the user of the front about the progress of the import.
for (const record of records) {
await api.jobs.ack(jobId, {
info: "Loading contacts",
progress: Math.ceil((index / records.length) * 100),
});
await Promise.all(
Object.keys(record.values).map((key) => {
console.log({
row: startRow,
column: +key,
value: record.values[key].value,
});
return supabase
.from("values")
.upsert(
{
row: startRow,
column: +key,
value: record?.values?.[key]?.value || '',
},
{
onConflict: "row,column",
}
)
.select();
})
);
startRow++;
index++;
}
Once the import is completed, we can finish the job on the client side.
await api.jobs.complete(jobId, {
outcome: {
message: "Loaded all contacts!",
},
});
The full listener.ts
file should look like this:
import { FlatfileEvent, Client } from "@flatfile/listener";
import api from "@flatfile/api";
import { createSupabase } from "./src/database/supabase";
const supabase = createSupabase(process.env.SECRET_KEY!);
export default function flatfileEventListener(listener: Client) {
listener.filter({ job: "workbook:contacts:submit" }, (configure) => {
configure.on(
"job:ready",
async ({ context: { jobId, workbookId }, payload }: FlatfileEvent) => {
const row = await supabase
.from("values")
.select("row")
.order("row", { ascending: false })
.limit(1);
let startRow = row.data?.length ? row.data[0].row + 1 : 0;
const { data: sheets } = await api.sheets.list({ workbookId });
// loading all the records from the client
const records =
(await api.records.get(sheets[0].id))?.data?.records || [];
let index = 1;
try {
for (const record of records) {
// information the client about the amount of contacts loaded
await api.jobs.ack(jobId, {
info: "Loading contacts",
progress: Math.ceil((index / records.length) * 100),
});
// inserting the row to the table (each cell has a separate insert)
await Promise.all(
Object.keys(record.values).map((key) => {
console.log({
row: startRow,
column: +key,
value: record.values[key].value,
});
return supabase
.from("values")
.upsert(
{
row: startRow,
column: +key,
value: record?.values?.[key]?.value || "",
},
{
onConflict: "row,column",
}
)
.select();
})
);
startRow++;
index++;
}
} catch (err) {
// failing the job in case we get an error
await api.jobs.fail(jobId, {
info: 'Could not load contacts'
});
return ;
}
// Finishing the job
await api.jobs.complete(jobId, {
outcome: {
message: "Loaded all contacts!",
},
});
}
);
});
}
To recap everything:
- We created a new file called
listener.ts
that listens to new imports. - We added a filter called
workbook:contacts:submit
to catch all the contacts imports (you can have multiple filters in case you have files import in different places). - We iterate over the contacts and add them to our DB.
- We inform the client about the percentage of our progress with
api.jobs.ack
- If there is a failure, we will inform the client with
[api.jobs.fail](http://api.jobs.fail)
- If everything is okay, we will inform the client with
api.jobs.complete
You can learn more about how to use the events here.
Save the file and run it with
npx flatfile develop listener.ts
And when you are ready to deploy it, just use
npx flatfile deploy listener.ts
This is pretty amazing because if you deploy it, you donāt need to run this command again.
You will also see the logs inside the Flatflie dashboard.
Letās run the develop command, import our CSV file, and see what happens.
I hope you enjoyed this one!
I certainly did š
For the full source code, please visit:
https://github.com/nevo-david/contacts
Worth checking them out
if you plan on implementing any kind of feature that involves importing or exchanging data files,
Go ahead and check FlatFile. I had tons of fun playing with them š„
You can get started here for free
Top comments (20)
A nice well-written blog. Thanks again. @nevodavid
Thank you so much Ankur šš»āāļø
My dude, you exposed all of your .env variables in the .env example file and sent them to git:
github.com/nevo-david/contacts/blo...
They are not valid anymore :)
Can u push all the code to GitHub? i am confused somewhere and want to resolve it
Hi Mirza!
The full code is here:
github.com/nevo-david/contacts
your articles are always so helpful - keep up the good work!
Thank you so much Ophir!
Thanks for writing this. I've given you a shoutout on my substack:
aliirz.substack.com/p/all-things-d...
Thank you so much Ali!
But my name is Nevo not Nova!
But maybe I should change my name, I like this one :)
Did you ever build a big system with contacts support?
Which CRM do you use? :)
Bit confused is full code really here?
github.com/nevo-david/contacts
The tutorial is not complete in that the supplied code snippets are not clear what files they go into, and the repo is only after the "npx create-next-app@latest contacts"
The supplied repo is at the "hello-world" stage, it does not contain working code
Have you looked at the tradeoffs between supabase and hasura? Curious what your thoughts are.
I believe I could do the same task also with Hasura (first time I hear about them)!
There are many alternatives to Supabase, such as Appwrite and even Firebase.
Generally I love supabase and their open-source community :)
(Didn't have time to look into Hasura pricing)
ok
Super awesome
Very high quality
Yay!!! š