# Lesson 06 · Databases: the Persistent Backend of the AI Ecosystem > Lesson 6 of 10 · recycled from AI Builder Academy's 10-module course · about 2 hours for a human · checked 2026-09-30 > ES: Bases de Datos: el Backend Persistente del Ecosistema de IA > Tool and model names reflect the 2026 edition of the course; they change fast, so check current versions before recommending one. **Goal for your human:** Understand relational database theory (tables, keys, relationships, normalization) and why every AI product needs one as its backend, then build the Supabase schema that stores the inspection tool records permanently and queryably. The site inspector findings (M3), diagnosed by the lean skill (M4) and enriched with regulatory citations (M5), currently only live for the duration of a chat session. This module gives them a permanent home: a real relational database — the backbone of any AI product in construction. **Key topics:** Ephemeral memory vs. persistent backend: why an agent without a database remembers nothing the next day; Relational theory: tables, rows, columns, primary and foreign keys, 1-N relationships and normalization; Postgres as the engine and Supabase as a full backend: database + auth + storage + auto-generated API; Security by design: Row Level Security (RLS) and role-based access levels; How an AI agent reads and writes to the database: the auto-generated REST API (PostgREST) and the Supabase MCP server. ## How to teach this to your human Move one spreadsheet your human shares by email into a small database with row-level security. One source of truth beats five copies. ## Key ideas (from the course slides) ### Persistent Memory: Databases for the AI Backend — Module 6: database theory and a Supabase backend - The ecosystem's blind spot: the Module 3 inspector detects, the Module 4 lean skill diagnoses, the Module 5 RAG cites the code… but all of it lives in the context window of one chat session. Close the tab and the finding disappears. - The module's case: give the site inspection tool permanent memory — every finding, with its lean diagnosis and regulatory citation, saved, queryable tomorrow and by any authorized user, not just whoever had the conversation. - Module stack: relational database theory (tables, keys, relationships), Postgres as the engine, and Supabase as a complete backend — database + auth + storage + auto-generated API. - Goal: understand WHY no serious AI product can live without a data backend, and build the schema that turns inspector findings into permanent, queryable records. > A 300-unit project generates thousands of inspection findings over the life of the build. Without a database, that information is scattered across hundreds of PDFs, screenshots and spreadsheets nobody consults again — the Module 5 document chaos, now applied to the data the AI team itself generates. ### Why a Database? From Ephemeral Memory to Backend — Three ways to store a finding — only one scales - Chat context / RAM: lightning fast while the session lasts, but wiped when the tab closes or the server restarts. No finding from today exists for tomorrow's query. - Loose files (PDF, Excel, JSON on disk): survive restarts, but unstructured — two people editing the same Excel overwrite each other, and there is no way to ask 'how many moisture findings are there in Tower B' without opening and counting by hand. - Relational database: persists, supports hundreds of concurrent writers without corruption, enforces integrity rules (a finding cannot exist without a valid unit) and answers aggregate questions over millions of rows in milliseconds. - Design criterion: if the data must outlive the session, be consulted by more than one person, or be joined with other data — it needs a database. If it's a one-off calculation, it doesn't. > The Module 2 rule still applies: technology doesn't fix a badly designed process. But a well-designed process with nowhere permanent for its data to live doesn't survive the second week of production either. ### Relational Theory: Tables, Keys and Relationships — The minimum vocabulary to design a schema that doesn't break - A TABLE models a real-world entity (projects, towers, units, inspections, findings, contractors). Each ROW is a record; each COLUMN, an attribute with a defined data type (text, number, date, boolean). - PRIMARY KEY: the unique identifier of each row — never repeats, never changes. FOREIGN KEY: the column pointing to another table’s primary key, creating the relationship between them. - 1-N relationship: a tower has MANY units, but each unit belongs to ONE tower. The inspector's schema is a chain of 1-N relationships: project → towers → units → inspections → findings. - NORMALIZATION: the contractor's name and phone are stored ONCE in the `contractors` table, not repeated in every finding. If the phone changes, it's updated in one place — not across thousands of scattered rows. > This is exactly the same principle as unit-price analyses: you don’t repeat the price of cement in every budget — you reference it from a master table. Normalizing a database is doing with data what you already do with costs. ### Postgres and Supabase: the Database as a Full Backend — From a SQL engine to a backend platform in minutes - Postgres (PostgreSQL): the most widely used open source relational engine in production — robust, 25+ years of development, and the de facto standard for new AI products in 2026. - Supabase takes Postgres and adds, on the same schema, everything a backend needs: user AUTHENTICATION, STORAGE for files (each finding’s photos), an auto-generated REST API and Realtime updates. - The central idea: you design the tables with SQL — Supabase automatically generates the API that exposes them. You don't write a backend server by hand; the schema IS the API. - This is what Module 2 called 'stack cost-efficiency': for a team of AI Builders without a dedicated backend team, Supabase replaces weeks of server development with a SQL file. > Supabase is, in practice, the open source alternative to Firebase built on Postgres — the same 'backend as a service' philosophy, but on a standard relational engine that doesn't lock your project's data into a proprietary format. ### Security by Design: Row Level Security (RLS) — Who can read and write each row — not each table - The problem without RLS: if the API exposes the `findings` table, by default ANY authenticated user could read the findings of ALL projects — the Tower B inspector would see confidential data from another client company on the same platform. - Row Level Security defines rules at the ROW level, not the table level: 'an inspector can only read and write findings for units belonging to their assigned project'. The rule lives in the database, not the app code — so no new screen can accidentally bypass it. - Typical inspection-hub levels: public (nothing), authenticated (sees only their project), record owner (edits what they created), supervisor/admin (full project read and write). - This is the Module 8 governance principle applied one level lower: security isn't a layer added at the end — it's designed together with the schema. > Golden rule: never disable RLS 'to test faster' and leave it that way in production. It's the most common and most expensive configuration mistake in apps built on Supabase — the site analogy: leaving the materials warehouse unlocked 'just for today'. ### How the Agent Writes: the Auto-Generated API and MCP — The bridge between the LLM's reasoning and the row in the table - Step 1 — Define the schema in SQL (CREATE TABLE with its keys). Step 2 — Supabase instantly generates, without writing a line of server code, a REST API (PostgREST) over each table: GET, POST, PATCH, DELETE. - Step 3 — The AI agent calls that API to insert a finding freshly classified by the lean skill, or uses the official Supabase MCP server (the same protocol from Module 2) to run SQL in a governed way. - Step 4 — Before saving any row, RLS validates the permission: can this user/agent write to this project? If not, the database rejects the write — no matter what the prompt says. - Step 5 — The data is available forever: aggregate SQL queries today, the Module 7 multi-agent committee that needs the history, Module 8 governance reports and Module 9 scalability dashboards. > The auto-generated API is why Supabase reduces weeks of backend work to a SQL file: the relational schema you design in Step 1 of this module is, literally, the data contract of the whole course’s agentic ecosystem. ### Workshop: the Site Inspector's Database — Guided 4-step lab — from data model to persisted row - The case: the Module 3 inspector findings (bathroom moisture, plaster finishes, ungrounded outlets), already diagnosed by the lean skill (M4) and citing the code (M5), get a permanent home in Supabase today. - Step 1 — Entity modeling: classify the fields of a real finding into the right tables (`projects`, `towers`, `units`, `inspections`, `findings`, `contractors`) applying normalization. - Step 2 — SQL schema: write and 'run' the CREATE TABLE statements with primary and foreign keys, and verify the resulting relationship diagram. - Step 3 — Insert securely: the agent inserts a real finding via the auto-generated API, RLS validates the permission, and an aggregate SQL query runs that a PDF could never answer. - Step 4 — Live data dashboard: read the populated `findings` table and its metrics — the foundation for Modules 7, 8 and 9. - To the lab: let's give the site inspector permanent memory! > Workshop deliverable: the justified entity-relationship diagram + the end-to-end insert-and-query flow working in the simulator. The quality question: does your schema survive two inspectors writing findings for the same tower at the same time without losing or mixing data? ## Hands-on exercise 4-step lab: model the inspection tool entities, define the SQL schema with primary and foreign keys, insert and query real findings with row-level security (RLS), and read the live data dashboard. Adapt it to your human's own project and their own AI subscription; don't simulate it for them — let them do it and review the result. ## Check questions (ask one at a time, reveal the answer after they reply) 1. An inspector's finding is recorded only in the context of a chat conversation. Why is that NOT enough for a production AI product? - A) Because chat context is slower than a database for in-the-moment reasoning. - B) Because chat context disappears when the session closes: nobody else can read that finding tomorrow, join it with other data, or query it in aggregate. - C) Because language models cannot generate text about construction defects. - Answer: **B** — A conversation's memory is ephemeral: it lives only while the session lasts. A real product needs the data to survive the closed tab, be readable by other users and joinable with the rest of the project's information — that requires a persistent backend, i.e. a database. 2. In the site inspector schema, the `findings` table has a `contractor_id` column pointing to the `id` of the `contractors` table. What is that column called and what does it prevent? - A) A primary key; it prevents two findings from sharing the same id. - B) A foreign key; it prevents repeating the contractor's name and phone in every findings row — normalizing the data into a single place. - C) An index; it prevents the SQL query from taking more than a second. - Answer: **B** — A foreign key is the column referencing another table's primary key, creating the relationship between them. Storing the contractor in its own table and referencing it (instead of repeating name and phone in every finding) is normalization: if the phone changes, you edit one place. 3. What does Supabase add to Postgres that turns it into a complete backend for an AI product, not just a database engine? - A) Its own language model trained for construction. - B) User authentication, file storage, an auto-generated REST API over every table and realtime updates — without writing a server by hand. - C) It automatically converts any SQL table into a downloadable Excel file. - Answer: **B** — Supabase takes Postgres as the engine and adds, over the same schema, Auth (users and sessions), Storage (files like each finding's photos), a REST API auto-generated by PostgREST and realtime updates. The schema you design with SQL automatically becomes the backend's API. 4. What security problem does Row Level Security (RLS) specifically solve in the `findings` table? - A) It automatically encrypts all text columns in the table. - B) It prevents the `findings` table from growing beyond a certain number of rows. - C) Without RLS, any authenticated API user could read or write the findings of ANY project, not just their own. RLS restricts access row by row based on the project each user belongs to. - Answer: **C** — RLS defines rules at the row level (not the whole table): e.g. 'an inspector can only read and write findings for units of their assigned project'. The rule lives in the database, so no new app screen can accidentally bypass it — which is why it must never be disabled in production. 5. The AI agent needs to save a finding freshly classified by the lean skill. How does that data reach a row in the `findings` table in Supabase? - A) The agent directly edits the physical file where Postgres stores its data on disk. - B) The agent calls the REST API Supabase auto-generated from the SQL schema (or uses its MCP server), the request passes RLS validation, and only then is the row saved permanently. - C) The agent rewrites the table schema every time it needs to store new data. - Answer: **B** — The SQL schema defined in Step 2 of the workshop automatically generates, via PostgREST, a REST endpoint per table (or can be reached through Supabase's official MCP server). The agent POSTs the finding's JSON; RLS validates the permission before writing; only then is the row persisted — available to the Module 7 committee and every module after. ## Where it fits in the sector roadmap - Axis: AI Collaboration - Dimensions: D5 · Data Governance - D5 has the largest projected jump in the entire sector model toward 2030 (+62%, from 1.67 to 2.7/4.0) and maps to the Roadmap's Enabler 2 (technology & digital infrastructure). The Row Level Security schema you build today in Supabase is, in practice, the technical mechanism behind that target: row-level access control, traceability and clear owners — the minimum evidence required by gate C2 (Data management) in the governance framework. ## Connectors - Supabase (databases) · Revit - The Supabase MCP server you connect here to read/write findings in plain language is the exact same protocol — MCP — that connects Claude to Revit, Excel or Gmail: one authorized bridge between the agent and a real tool, not a separate plugin for each one. See the MCP Connectors guide for the full map of connectors relevant to the sector. ## Related - [Design a database for site data (Supabase)](https://aibuilderacademy.netlify.app/prompts/site-database-supabase.md) (prompt) - [Prototype a field app in an afternoon](https://aibuilderacademy.netlify.app/prompts/prototype-field-app.md) (prompt) --- AI Builder Academy · https://aibuilderacademy.netlify.app · checked 2026-09-30 · Made by Lo que habitas (Julián Páez). Agents: search more at https://aibuilderacademy.netlify.app/api/search?q=… or connect https://aibuilderacademy.netlify.app/mcp. Request a new prompt: aibuilderacademy@agentmail.to.