Data and security basics
An AI tool can build a working reorder app in an afternoon, and that same app can show every shop's orders to anyone who knows where to look. This lesson shows you where your app's data lives, how sign-in and database access rules keep each shop inside its own rows, and how to keep secret keys out of your code, so Ravi's orders stay Ravi's.
By the end you can
- Read a small set of tables: rows, columns, primary keys and foreign keys, with money as integer cents
- Explain why an app whose browser code talks straight to the database needs its access rules inside the database
- Turn on row-level security and write policies so a signed-in user sees and creates only their own shop's orders
- Keep secret keys out of browser code and Git, and know what to do when one leaks
- Test access yourself as two different users, and ask the AI the questions that find the gaps
The everyday picture
Picture an apartment building with a row of mailboxes in the lobby. Every tenant has a key to the front door, and copies of that key are everywhere: with family, with the cleaner, under a plant pot. That is fine, because the front door only gets you into the lobby. Each mailbox has its own lock, and your mailbox key opens only your box.
Now imagine the builder forgot to put locks on the mailboxes. Nothing looks wrong. Every tenant still goes to their own box. But anyone who gets through the front door can open all of them. And somewhere in the building there is a manager's master key that opens every box at once. If that key hangs on a hook in the lobby, the locks no longer matter.
Your app works the same way. The public key in the browser is the front door key. Sign-in tells the building who you are. Row-level security is the lock on each mailbox. The secret key is the manager's master key, and it must never hang in the lobby.
Tables: where Ravi's orders live
Your app keeps its data in a database. The database keeps data in tables. A table is like one sheet in a spreadsheet: each row is one thing, and each column is one fact about it. In the spec you wrote in A spec the AI can follow, the reorder app needed shops, products, a usual order, and sent orders. That becomes five tables:
partner_shop: one row per shop. Ravi's corner shop is one row.article: one row per product, with its price. (articleis the usual word for a product in retail data.)usual_order_line: one row per product in a shop's usual order, with the quantity."order": one row each time a shop sends an order. The name needs double quotes in SQL, becauseORDERis already an SQL word.order_line: one row per product inside a sent order.
Every table has a primary key, usually a column called id. It is a number the database hands out, and it never changes, even if the shop changes its name. Other tables point at a row by storing its id. That stored id is a foreign key. The "order" table has a shop_id column that holds a partner_shop.id, and the database refuses an order for a shop that does not exist.
Money goes in whole numbers of cents. A pack of rice at $12.50 is stored as price_cents = 1250. Decimal numbers in computers can turn 0.1 + 0.2 into 0.30000000000000004, and nobody wants that on an invoice. You will learn the full method, including how to split data so each fact lives in one place, in Modeling data in tables, and how to ask questions of tables in SQL you'll actually use. For this lesson you only need to read tables and see the arrows between them.
shop_id, which is exactly the column the access rules will check later.Quick check
How should the "order" table record which shop sent the order?
The id never changes and the database checks that it points at a real shop. A typed name breaks when the shop is renamed or someone types it differently, and copying every column means the same fact lives in many places.