Put Your Team App on the Organisation's Own Database, and Prove You Can Restore It

T06-L04 · Build apps · Level 4 Integrator · 20-minute read, plus about 6 hours of doing over two weeks

The weekly export nobody made

The antibody list from T06-L03 did its job. Two more groups on the floor now use it to request reorders, and the institute's purchasing office wants each order to carry a cost centre. Then three things happen in one month. A student in one group reads another group's requests, because Base44's permissions cover whole tables. Purchasing asks whether it can see the orders without seeing who asked. And the group leader asks what happens if the app loses its data. The answer is the weekly CSV export from the operating note, and Jonas, the deputy, missed it for three weeks while he was at a conference.

In a company it's the demo-kit bookings. The bookings now carry customer names and visit addresses. The sales lead wants each booking to create a shipping order in the ERP, the company's system for orders and stock. And a rep who left in June could still sign in to the app in August.

You are at Level 4 Integrator. The app is now connected to the organisation's people, data and systems. This book moves its data to where the organisation can protect it, and proves you can get it back.

After this you can

• Move an app's data into a database your organisation runs, behind an API, with IT and the data owner agreeing where it lives.
• Enforce who sees and changes which rows and fields in the data layer, and prove it with requests from each role's own account.
• Give every person, program and AI assistant its own login with the smallest access that does its job.
• Back up the data away from the server and restore it into an empty database, timed, before anyone depends on it.
• Change the data model on a test copy first, never on the live data.

Before you start

• Books: T06-L03 for the app, its roles and its test sheet. T12-L04 for securing a server your organisation runs. The four quadrants come from T01-L01.
• A server that IT runs, with Docker, the tool that runs programs in containers (sealed packages with everything they need), and a place for backups on a different machine.
• Supabase, the open-source platform this book teaches. It wraps an ordinary PostgreSQL database (the widely used open-source database) with what an app needs around it: sign-in, an API (the interface other programs use to read and write) and Studio, a web interface for tables and rules. IT runs it on the organisation's own server.
• Lovable, an AI app builder that produces an ordinary web app whose code you can keep and host elsewhere, and that is built to work with Supabase. You'll rebuild the app's screens with it, and you need a GitHub account or organisation for its code.
• The people who can say yes: IT for the server, network and backups; the data owner (the group leaders, or the head of sales); the data protection officer if names or customer data go in.
• About 6⅓ hours: 20 minutes to read, about 6 hours to do, over two weeks.

Last verified 23 September 2026. Supabase is free to self-host; you pay only for the server. The self-hosted version has no managed backups, so IT runs them. If you'd rather not host it, Supabase's cloud Pro plan costs $25 a month. Details for experts at the end of this book.

The idea in one page

In T06-L03 the app builder held everything: screens, data, logins and rules. That was right for one team. Once other groups, other systems and personal data come in, you separate the parts, so each can be protected and replaced on its own.

people ──> your app (screens) ──> Supabase API ──> PostgreSQL database
                                    │  sign-in     │  row-level security policies
programs, assistants ───────────────┘              nightly backup, off the server

The database decides, not the screens. Anyone who can sign in can also send requests to the API directly, without your screens. So every rule lives in the database, as row-level security. You switch it on for a table, and from then on nobody reads or writes a row unless a policy allows it. A policy names an action (read, create, update, delete) and a condition, such as "rows where the group is my group". Supabase knows who is asking from their sign-in, so the condition can use it. That's finer than whole tables, which is what T06-L03's app couldn't do.

Rows, not fields. Row-level security decides which rows someone gets, always with all their fields. So fields that a role mustn't see go into a table of their own, with their own policies.

Policies add up. If a table has two ordinary policies for the same action, a request goes through when either one allows it. A broad policy can't be cancelled by writing a stricter one next to it. So you keep each role to the policies it needs, and you test what it can actually do.

Two keys, and one never leaves IT. Supabase gives the app a publishable key, which is safe in the browser because the policies still apply to every request made with it. It also has a secret key, which skips every policy. The secret key stays with IT and never goes into the app's code, where anyone could read it in their browser.

Every person and program gets its own login. Colleagues sign in as themselves. A program, such as an automation or an AI assistant, gets an account of its own with only the access it needs.

A backup counts only once you've restored it. Three things make up the app's data: the database, any uploaded files, and the configuration that runs it. On your own server, backups are your job, not Supabase's. The backup goes to a different machine every night. Before anyone depends on the app, you restore it into an empty database, time it, and run the role tests against the restored copy.

Changes go through a test copy. A second Supabase with test data gets every change to tables and policies first. Each change is a short file of SQL, the database's own language, kept with the app's code, so the same file runs on the test copy and then on the live database.

Worked example: two apps, four quadrants

Both follow the same path. Follow the one closer to your work. The names, numbers and incidents are typical examples written for this book.

Lab: reorder requests for three groups

Structure it (20 min). Aylin, who built the app, writes: "Three groups request antibody reorders. Each group sees only its own requests. Each group's lab manager changes status. Purchasing sees what was ordered, with the cost centre, not who asked. We can lose at most one day of requests and must be running again within two hours."

Understand it (90 min). She lists what the data holds: antibody names, catalogue numbers, suppliers, quantities, cost centres and the names of the people who asked. Names are personal data, even if ordinary. She asks IT three questions. Does the institute already run something for this? The electronic lab notebook has an inventory, but no request process and no way to give purchasing its own view. Can IT host a small data layer? Yes, on the server it runs for group tools, with nightly backups to the backup server in the other building. And what does IT need from her? A named maintainer and deputy, a data owner who signs, and a restore test before go-live.

Then she sits with purchasing. The person who orders needs the supplier, catalogue number, quantity and cost centre. She doesn't need the requester's name and would rather not have it.

That changes her plan. Back to Structure it: "Purchasing reads ordered items only, without names. Members may fix the reason on their own request while it's still requested. Everything else stays as in T06-L03." The field rule is new: it's what the old app couldn't enforce.

Choose tools (30 min). For the data layer she picks Supabase, run by IT on the institute's server. Underneath it is an ordinary PostgreSQL database that IT already knows how to back up. Row-level security gives her rules per row, it's open source and free to self-host, and Lovable is built to work with it. Studio, the web interface, has one shared password on a self-hosted server. IT keeps it, and only Aylin, Jonas and an IT administrator use Studio. Everyone else uses the app.

The institute's central sign-in isn't open to group tools, so Aylin invites people by email and removes leavers every month from the institute's leaver list. IT accepts that for this data.

For the screens, she rebuilds the app in Lovable from her T06-L03 test sheet. Lovable's one-click Supabase button signs in to Supabase's cloud, so it can't reach a Supabase on the institute's own server. Lovable's hosting guide covers this case: the app points at a self-hosted Supabase through two settings. Her setup has four steps:

1. Keep Lovable's own backend off. In Lovable, under Settings → Your account → Preferences → Agent permissions, she sets Enable Cloud to Never allow. The setting starts at Always allow: Lovable would then switch on its own built-in backend, Lovable Cloud, the first time she asks for a sign-in, and the data would land there.
2. Connect GitHub. Under Project settings → Git → GitHub she clicks Connect and picks the account or organisation. From then on Lovable saves every change to that GitHub repository, where IT can fetch the code.
3. Ask for the right connection. In the chat she describes the screens and asks Lovable to talk to Supabase through Supabase's JavaScript library, reading the address and the publishable key from two settings, VITE_SUPABASE_URL and VITE_SUPABASE_PUBLISHABLE_KEY, in the project's .env file.
4. Let IT fill in the values. IT builds the app from the repository with the institute's Supabase address and publishable key in those two settings, and serves it from the same server. Both values are safe in a browser; the secret key never goes here.

So the app and the data stay on the institute's machines. Without the one-click connection, Lovable's chat can't change tables or policies; those stay in Aylin's SQL files. She tests the screens on IT's build, which reaches the institute's Supabase.

The alternative for the data layer would be a plain PostgreSQL database with a small API written for it. That means writing the sign-in, the API and the permission checks yourself, and keeping all three safe. Supabase gives her those parts ready-made, on the same database.

Build it: the server (60 min with IT). IT starts Supabase with the Docker setup that Supabase publishes: a Compose file, a text file that tells Docker which containers to run and how they connect. IT fetches it at one pinned release, so a restart never brings a surprise upgrade. Before the first start, IT replaces every default password and key in the .env file, the settings file that holds them. Only IT can read it, and it never goes into Git; Supabase's guide says never to start with the defaults. Supabase's two key scripts create the new values, including the publishable and secret keys. IT also enters the institute's mail server there, so Supabase can send invitations. Supabase and Studio are reachable only through the institute's web gateway, with HTTPS and only from the institute network.

Build it: the data model and rules (90 min). She writes the tables as SQL files and runs them in Studio's SQL editor: antibodies, groups, order_requests (antibody, supplier, catalogue number, quantity, cost centre, status and group) and request_details (who asked and why). The requester's name sits in its own table because purchasing mustn't see it. A fifth table, members, says which group each person belongs to and in which role: member, lab manager or purchasing. The policies look people up there:

Nobody may delete. Each rule is a policy in SQL. The read rule for members and lab managers looks like this, where authenticated means anyone signed in and auth.uid() is the signed-in person as Supabase knows them:

-- Members and lab managers read their own group's requests, nothing else.
alter table order_requests enable row level security;
create policy "read own group" on order_requests for select to authenticated
  using (group_id in (select group_id from members where user_id = (select auth.uid())));

The create rule matters as much as the read rules. Its check refuses any new request whose group isn't the member's own or whose status isn't requested, whatever the screen sends.

Build it: prove the rules (45 min). She extends the T06-L03 test sheet with lines the screens can't show, because they test the API directly. First she creates one test account per role in Studio, under Authentication → Users → Add user, and gives each a row in members. Then she uses curl, a command-line tool that sends a single web request. She signs in as the test member of group B, keeps the token Supabase hands back (a temporary key that stands for that login), and asks for a request of group A by its number. Each request carries the publishable key in the apikey line and the token in the Authorization line:

# Sign in as the group-B test member; the answer contains "access_token".
curl -s -X POST "https://reorder.example-institute.de/auth/v1/token?grant_type=password" \
  -H "apikey: $PUBLISHABLE_KEY" -H "Content-Type: application/json" \
  -d '{"email":"test-member-b@example-institute.de","password":"'"$PASSWORD_B"'"}'
# As that member (token saved in TOKEN_B_MEMBER): try to read request 17, which belongs to group A.
curl -s "https://reorder.example-institute.de/rest/v1/order_requests?id=eq.17" \
  -H "apikey: $PUBLISHABLE_KEY" -H "Authorization: Bearer $TOKEN_B_MEMBER"

The answer must be an empty list, []. Row-level security doesn't say "refused" on a read; it simply hands back no rows. A refused change also changes nothing without an error, so those lines read the row again afterwards. The sheet has twelve lines: each role reads, creates and updates what it may and what it may not, and purchasing asks for request_details and gets nothing. Line 9 fails on the first run. A member can change status on their own request. Aylin had written a second update policy for lab managers and forgotten the check on the role. Policies add up, so that one let every member through. She adds the role check and all twelve lines pass.

Build it: move the data (30 min). She exports both tables from Base44 as CSV files and imports them with Studio's table editor, moving the requesters' names into request_details. Then she counts: 214 antibodies and 388 requests in both places. Three requests have no group; she asks the lab managers and fixes them by hand.

Build it: an assistant with its own login (20 min). Petra, the lab manager, wants to ask her assistant which antibodies her group reorders most. Aylin creates a sign-in account assistant-petra and gives it a role of its own, reader, for group A in the members table. Its policies allow reading group A's order_requests and antibodies, and nothing in request_details. Petra connects the assistant your organisation has approved through the connector from T14-L04, signed in as that account, never as herself and never with the secret key. Supabase also has an MCP server, a standard connection for AI assistants, but on a self-hosted install it's meant for administrators over a secure tunnel, not for colleagues' assistants. The data protection officer confirms the assistant is cleared for internal data.

Build it: backup and the restore (90 min). IT's nightly job saves three things to the backup server. First, a dump of the database, a copy written out as SQL that rebuilds it, in three files, the way Supabase's own guide makes it: the database roles, the table structure with its policies, and the data. Second, the storage folder with uploaded files. Third, the Compose and .env files, kept in IT's password vault. Because the policies live in the database, the dump carries them.

Then the rehearsal. On a second test server, Jonas, not Aylin, fetches last night's backup, starts an empty Supabase from the same pinned release with the .env from the vault, loads the three files with psql, PostgreSQL's command-line tool, and runs the twelve-line test sheet against the restored copy. It takes 52 minutes, well inside the two hours. His notes become the restore page in the operating note, with one addition: "The .env from the vault must belong to the release in the Compose file."

Build it: the test copy for changes (30 min). IT sets up a second, small Supabase with test data. When purchasing later asks for a delivery date field, Aylin writes the change as a short SQL file in the repository, runs it on the test copy and reruns the sheet. Then IT runs the same file on the live database.

Sign-off. IT signs the server, network, backup and the restore record. The three group leaders sign the rules as data owners. The data protection officer adds the app to the institute's register of processing activities, the list of every process that handles personal data, with requester names kept for two years, the same as purchasing records.

Company: demo-kit bookings connected to the ERP

Structure it (20 min). Marco writes: "Reps book kits for customer visits; logistics ships them. Bookings now hold customer names and visit addresses. Every person signs in with their company account and loses access the day they leave. Each confirmed booking creates exactly one shipping order in the ERP."

Understand it (60 min). With Tobias from IT he finds that the ERP can't hold bookings of the kind the team needs, but its API can take shipping orders. The rep who kept access after leaving is the key finding: app accounts that don't follow the company directory stay open until someone remembers them. The data protection officer asks how long customer names are kept and who can see them.

Back to Structure it: "Company sign-in is a must. Reps see their own bookings and kit availability, not other reps' customers. Logistics sees everything it ships. Customer details are deleted twelve months after the visit."

Choose tools (30 min). The same self-hosted Supabase, on the company's server. This time company sign-in is required. Supabase can sign people in through Microsoft Entra ID, the company's sign-in service; Tobias switches it on in the .env file, at no extra cost. A leaver can't sign in once IT disables the account. IT's leaver step also deletes them in Supabase, so a session still open ends within the hour. Two more team apps are planned on the same server, so the head of sales signs off the server and IT's time for all three. The shipping order goes through the n8n automation from T05-L04, the workflow tool the team already uses, with its duplicate guard (the check that stops one booking creating two orders) and its own account in Supabase: read confirmed bookings, write back the shipping number, nothing else. Never the secret key.

Build it. The rules follow the Lab pattern. Customer names and addresses sit in a table of their own. Reps read their own bookings in full and other bookings only as kit, dates, booked. The twelve-line test sheet includes a rep asking for another rep's customer name by booking number. Tobias adds a change history: a table the database fills itself on every change to a booking, with who changed what and when. Because that history could show how each rep works, the head of sales tells the works council, the elected staff representatives, what it's for: fixing errors, not assessing reps. A monthly job deletes customer details from bookings older than twelve months. The restore rehearsal, run by Svenja from logistics with Tobias, takes 41 minutes and ends with the full test sheet passing on the restored copy.

In both cases the move itself took an afternoon. The work that made the data safe was deciding who may see which field, proving it from each role's own account, and having someone other than the builder bring the data back.

What goes wrong

Rules only in the screens. Symptom: a colleague can't see the button, but a direct API request returns the data. Fix: switch on row-level security for every table, put every rule in a policy, and keep API lines in the test sheet that read and write what each role may not.

A second policy opens the door. Symptom: the rule looks right, yet a role sees or changes more rows than it should. Fix: policies add up, so list every policy on each table and check each one's role condition. Remove or fix the broad one; don't write a stricter one next to it.

The secret key in the app. Symptom: the app works for everyone because it calls Supabase with the secret key, visible in the browser, and no policy applies. Fix: the app carries only the publishable key, each person signs in as themselves, each program has its own account, and the secret key stays with IT.

An assistant with too much access. Symptom: an AI assistant connected with a manager's login or the secret key can read and change everything they can. Fix: an account per assistant, with a read-only role on the tables it needs.

A backup on the same server. Symptom: the disk fails and takes the database and its backup with it. Fix: nightly backups to another machine, with the configuration kept in IT's vault.

A backup nobody has restored. Symptom: the dump exists, but nobody knows whether it loads or how long it takes. Fix: a timed restore into an empty database, run by someone other than the builder, ending with the role test sheet.

Model changes on the live system. Symptom: a field is renamed in the live Studio and the app breaks for everyone. Fix: every change is an SQL file that runs on the test copy first, through the test sheet, then on live.

Do it yourself (about 6 hours, over two weeks)

1. Structure it (20 min). Write who sees and changes what, how much data you can lose, and how fast you must be back.
2. Understand it (45 min). List every field and whether it's personal or confidential. Ask IT what already exists, where it could run and how it's backed up. Ask each group of users what they need to see. Rewrite your sentence.
3. Choose tools (20 min). Decide where Supabase runs, how people sign in, and which fields need a table of their own. Decide how the screens will be built and hosted.
4. Build it: server and rules (2 hours). With IT, start Supabase at a pinned release with new passwords and keys. Create the tables, the members table and the policies. Create one test account per role, write the API lines of the test sheet and run them from each role's own account.
5. Build it: screens, data and logins (60 min). In Lovable, set Enable Cloud to Never allow, connect GitHub, and ask for screens that read the Supabase address and publishable key from the two settings; IT builds and serves the app. Import the data and check the counts. Create separate accounts for each program or assistant, with read-only or narrow roles.
6. Build it: restore (60 min). Have your deputy restore last night's backup into an empty Supabase on a test server, timed, and run the whole test sheet against it.
7. Sign-off (15 min, plus waiting). Get IT's, the data owner's and, if needed, the data protection officer's signature.

Exit check

What you hand in: for one team app whose data lives in a Supabase that your organisation runs:

• a role test sheet, with each role's allowed and refused requests, including API requests the screens can't show;
• a restore record: which backup, from where, who restored it, into what, how long it took, and the test sheet's result on the restored copy;
• a list of accounts for people, programs and assistants, each with its role;
• sign-offs from IT and the data owner, and the data protection officer's answer if personal data is involved.

It passes when the restored copy, built from the off-server backup into an empty database by someone other than you, passes every line of the role test sheet, no secret key appears in the app's code or browser traffic, and every program and assistant has its own account.

Rule to remember

Put the rules where the data is, and trust a backup only after someone else has restored it.

Details for experts

Checked on 23 September 2026. You don't need this to follow the book; it's for setting things up or answering a colleague.

Further reading and tools

Next books

• T06-L05 · Run the App Others Depend On, and Hand It Over: updates, monitoring, incidents and handover, over years.
• T06-L03 · Turn Your Team App Into One Colleagues Can Depend On: the roles and test sheet this book builds on.
• T12-L04 · Lock Down a Self-Hosted AI Service Before Real Data Goes In: the server around the app.
• T05-L04 · Make an Automation Safe to Fail Before It Touches Real Systems: the automation that writes into the ERP.
• T14-L04 · Build a Read-Only Connector for a System Your Assistant Can't Reach Yet: connecting assistants to your organisation's systems.

Tools

• Supabase: Taught. Self-hosting, row-level security, keys, sign-in, backup and restore.
• Lovable: Taught. Rebuilding the screens as a web app that talks to Supabase.
• Docker: Taught. Running Supabase in pinned containers.
• n8n: Catalogued. The automation that creates shipping orders.
• Base44: Catalogued. Where the app started, and still right for one team without outside systems.