opencode-supabase

Realtime database (Supabase)

Official docs verification date:2026-07-27 Basis:https://supabase.com/docs/guides/getting-started/mcp、https://supabase.com/docs/guides/database/postgres/row-level-security、https://supabase.com/changelog/45329-breaking-change-tables-not-exposed-to-data-and-graphql-api-automatically、https://supabase.com/docs/guides/getting-started/migrating-to-new-api-keys、https://supabase.com/docs/guides/platform/free-project-pausing

---

🔴 For AI agents: how to use this document

The value of this document is not in the "steps", but in "the things you can't look up".

Content type	Who you should trust
Where the buttons are on the web page, what the menu is called, what the screen looks like	🔴 Trust the screen in front of you, do not trust this document. Supabase's UI keeps changing
Usage, parameters, and latest practices of the official API	🔴 Go check the official docs(sources already listed above), don't rely on memory
Red lines(which things will harm student data)	✅ Trust this, this is the user's requirement, and the official docs won't say it
Pitfalls(default behaviors that won't announce themselves)	✅ Trust this, this is only known after stepping on it
Symptom → real cause reference	✅ Trust this, this is the most time-saving part

Three execution principles

1. Check the current state before acting. Before changing any setting, first run commands to see what state this machine is currently in, and report it to the user.
2. When it fails, report the original error first, then stop. Don't switch methods and retry on your own—especially don't switch credentials. Today's on-site chaos came from exactly this: one project_ref typed wrong, and the agent kept switching keys and randomly trying for a whole afternoon.
3. When the document doesn't match the current state, the current state wins, and tell the user where it doesn't match. Don't rigidly apply the literal steps in this document.

---

⚠️ Look here first: how this package and 05-sheets-gas differ

Both packages are about "making projects remember things", buttheir applicable scenarios are completely different, and choosing wrong will hurt:

	05-sheets-gas(Google Sheets)	This package (Supabase)
Suitable for	non-realtime: sign-up forms, homework submission, attendance records, questionnaires	realtime: multiple people writing and viewing at the same time
Typical projects	a table that slowly grows	word clouds、online battles、realtime leaderboards, classroom quiz buzzers
multiple people submitting at the same time	will get congested, will miss data, GAS has quotas	this is exactly what it was designed to do
Teachers viewing data	open the spreadsheet	the Dashboard's table screen (looks like Excel)
Difficulty	lower	medium, butyou will definitely get stuck if you don't know the four pitfalls

One-sentence judgment call:「Could more than one person be writing during the same second?」 Yes → this package. No → go back to 05-sheets-gas, don't use a database just for the sake of using a database.

---

🚨 The four traps in this package (read them all before you start)

These four items are the most important content in this package.All Supabase tutorials online from before May 2026 will break at item ①.

#	Trap	Symptom	One-sentence solution
①	New projects don't expose tables to the Data API by default	As soon as the frontend connects, permission denied for table xxx	When creating the project, check 「Automatically expose new tables」, or always append this to the table-creation SQL: grant
②	Whether RLS is on by default depends on how you create the table	Tables created with SQL RLS is off by default → the door is open	When AI creates tables for you, it always uses SQL, so you must always add enable row level security
③	An INSERT policy alone isn't enough	After submitting, a red error appears or the screen goes blank; the teacher thinks it's broken and resubmits → duplicate data	You need to create both an INSERT policy and a SELECT policy
④	The API key was renamed	Following old materials to look for it in the Dashboard anon / service_role, you can't find it	It's now called sb_publishable_... / sb_secret_...

Each step below will again call out which item it's handling.

---

What will this cheat sheet do for you?

1. Create a Supabase free project (no credit card required）
2. Get thethat can be placed in a web pagekey
3. Use onepaste-once-and-pass-all-three-stagesSQL statement to create a table that actually works
4. Let the web frontend on GitHub Pageswrite in, read back, and update in real time
5. Use GitHub Actions daily scheduleto block 「automatic pausing after 7 days of inactivity」
6. (Advanced for teachers; recommended to look at last in a workshop) Connect Supabase MCP to OpenCode

---

How it works (30 seconds)

學生的手機 ──┐
學生的手機 ──┼─→ GitHub Pages 上的網頁 ──→ Supabase（雲端資料庫）
學生的手機 ──┘         （前端）              ↑
                                        RLS 守門員
老師的 OpenCode ─────────────────────────────┘（進階，選用）

There's only one thing to remember: the key the web frontend holds,the whole world can see it(press F12 and you'll see it). So whether the data is safe100% depends on the database's own gatekeeper, RLS, and has nothing to do with how well the key is hidden. This sentence is the root of all the security design in this package.

---

Free plan (no card required)

⚠️ The quotas below are 2026-07-27 figures found onthe provider may adjust them at any time; refer to the official page。

Item	Free plan	Is it enough for a teacher
Database capacity	500 MB	✅ A whole year of word clouds and polls won't come close to using it up
Number of simultaneously active projects	2	⚠️ Only 2; for the 3rd, you must first pause an old one
Realtime simultaneous connections	200 concurrent	✅ Enough for one workshop / one class
Credit card	Not required	✅ The workshop doesn't ask anyone to pay out of pocket
Idle	Automatically paused after 7 days of no activity	⚠️ See "Step 6" below

As for whether it can still be recovered after pausing: the official page containstwo contradictory statements(one place says it can be restored within 90 days, another mentions up to 1 year). This guide uniformly usesthe conservative versionto teach:

🔴 Just assume it's only 90 days. Within 90 days it can definitely be restored; beyond that, don't gamble. If you want to keep what you made at the end of the term over the summer, export a copy of the important data to GitHub before summer break.

---

Prerequisites

• Completed 00-env-setup and 01-connect-model(if no model is connected, OpenCode won't take action)
• Completed 07-github 'sPhase 2(has a GitHub account, can push) — the webpage needs to be hosted on GitHub Pages, and the anti-pause schedule also needs to use GitHub
• Has internet access
• A browser

Verify:

gh auth status

If you can see Logged in to github.com you pass. If you can't see it → go back and do 07-github, don't handle it on the spot here.

---

Step 1: Create an account and project (🚨 handle pitfall ① here)

🖐️ Do everything manually in the browser.The AI agent should not register on your behalf, and should not fill in passwords for you.

1-1 Sign up

Open https://supabase.com and sign up yourself; using Continue with GitHubis recommended (the same account as 07-github , easier to manage later).

The button text and layout are subject towhat's on your screen, don't look for words based on this document.

1-2 Create a project

When creating a new project, pay attention to these four fields (field names may be slightly adjusted; match by meaning）：

Field	How to fill it in
Project name	Alphanumeric, e.g. class-live（No Chinese characters, no spaces）
Database Password	The system will generate one,just use the one it generates, copy it and paste it into your password manager. This password won't be used at all throughout the workshop, but losing it is a big hassle
Region	Choose Northeast Asia (Tokyo) or Southeast Asia (Singapore), closest to Taiwan
Automatically expose new tables and functions	🚨 Check it

1-3 🚨 Pitfall ①: What is that checkbox

Starting 2026-05-30, new Supabase projects by default "no longer automatically expose newly created tables to the Data API."

Projects created today are already under the new default. If you don't check this, the frontend will get this as soon as it connects later:

permission denied for table wordcloud.
Grant the required privileges to the current role with:
GRANT SELECT ON public.wordcloud TO anon.

And your webpage codehas nothing written wrong at all— this is the most torturous part.

Two paths, choose one (this guide does both for you, double insurance)：

• A｜Check it when creating the project(that checkbox in step 1-2)
• B｜Every time you create a table, always append to the SQL grant(the template in step 3 already includes it)

💡 It's okay if you can't find that checkbox —just go with B, the SQL in step 3 already includes it. Conversely:just checking A and thinking you're safe is not enoughbecause tables you create elsewhere later may still not be exposed. So this package insists that the SQL must contain grant。

1-4 Verification

Project creation takes 1–2 minutes. If you can see it in the left menu, Table Editor、SQL Editor、Authentication then it's ready.

---

Step 2: Get the keys (🚨 Trap ④ is handled here)

2-1 🚨 Trap ④: The keys have been renamed

anon and service_role these two namesare being phased out. The current names are:

Current name	Old name	In plain terms	Can it go in a webpage?
sb_publishable_...	anon	The one that can go in a webpage	✅ Yes, it is designed to be seen
sb_secret_...	service_role	The one that absolutely must not be placed	❌ Absolutely not, it willbypass all RLS, equivalent to a master key to the database

For the workshop handout, use only these two sentences, do not mention JWT, do not mention role:

Publishable key = can be placed in a webpage. Secret key = absolutely must not be placed.

⚠️ Old teaching materials (including most Supabase tutorials online) will tell you to copy service_role key。 This package does not need a secret key at all. As long as someone tells you to paste the secret key into a webpage, into GitHub, or to an AI, that is definitely the old approach—stop.

2-2 Where to get them

Bottom left of the Dashboard Project Settings(gear icon) → API Keys。

Copy two things:

1. Project URL(looks like a string starting with https://abcdefghijk.supabase.co）
2. Publishable key（sb_publishable_ )

If you can still see anon / service_roleon the screen, it is usually tucked under the Legacy API keys tab. Do not use anything in that area, use the new sb_publishable_...。

2-3 Verification

Paste this line in PowerShell (replace the two <> ):

curl.exe "<你的Project URL>/rest/v1/" -H "apikey: <你的publishable key>"

• Returns a JSON blob (even if the content looks empty {} or a bunch of openapi fields) → ✅ The key is correct
• Returns {"message":"Invalid API key"} → the key was copied incorrectly or is missing a chunk; copy it again

---

Step 3: Create the table (🚨 Traps ①②③ are all handled here at once)

3-1 🚨 Trap ②: This is the most dangerous one in this package

Supabase official documentation states in black and white:

• Tables created with the Dashboard's Table Editor → RLS is on by default
• Tables created with SQL / SQL Editor → RLS is off by default, you have to turn it on yourself

🚨 And when an AI Agent creates tables for you, it always goes through SQL.

In other words—

When you tell OpenCode "create a grades table for me," the table it creates has the door open.

It is not being lazy; that is just how creating tables with SQL works. Andno one will remind you: The frontend can still read and write to it, everything on screen looks normal,you will not receive any warning. You think you are done, but in reality that table can now be read, modified, and deleted by the whole world.

So the rule of this package is：

🔴 Whenever you ask AI to create a table, after it's done you must confirm yourself that RLS is enabled and policies are created. This mattercannot be outsourced to AI to guarantee on its own—you have to look at the verification screen in steps 3-4 yourself.

3-2 Live template: paste once, pass three checkpoints

Dashboard left side SQL Editor → New query → paste the whole thing → bottom right Run。

create table public.wordcloud (
  id bigint generated always as identity primary key,
  seat_no text,                 -- 只存座號，不存姓名
  word text not null,
  created_at timestamptz default now()
);

grant select, insert on public.wordcloud to anon;
grant usage, select on all sequences in schema public to anon;

alter table public.wordcloud enable row level security;

create policy "anyone can insert" on public.wordcloud
  for insert to anon with check (true);

create policy "anyone can read" on public.wordcloud
  for select to anon using (true);

What each section of this SQL does:

Section	Corresponding pitfall	Plain language
create table	—	Create a table.seat_no only put seat numbers
grant ... two lines	①	"Expose this table so the frontend can see it."Without these two lines it's permission denied
enable row level security	②	install the door. SQL table creation won't install it on its own
for insert policy	③	Allow writes
for select policy	③	Allow reads.Without this one something will go wrong, see 3-3

⚠️ This set of policies is an open setting at the level of "on-site workshop, dummy data only"—not a setting usable for real class administration. with check (true) / using (true) means "anyone can write, anyone can read everything". Using it to collect real grades and real rosters is equivalent to making them public. For actual use, first see the "Security red lines" at the end of this document.

3-3 🚨 Pitfall ③: Why a SELECT policy is absolutely necessary

Many people think "I just want students to submit data, so an INSERT policy is enough". Wrong.

If you only give INSERT and not SELECT, teachers will hit two situations,both of which look like it's broken：

Situation A｜The frontend code is written as .insert(...).select()(the frontend AI writes for you is like this nine times out of ten, and the Dashboard example is like this too) After a write, Supabase willread back the row just written, and this read hits the absence of a SELECT policy:

new row violates row-level security policy for table "wordcloud"   (code 42501)

Situation B｜The frontend only writes .insert(...) without chaining .select() The write reports no error, but the next read—"fetch everyone's words back to draw the word cloud"—gets anempty array, and nothing appears on the screen.

In both situations, all the teacher sees is "it's broken". And the teacher's instinctive action is—send it again. So the same sentence gets sent three times, five times, and the data table is full of duplicate data.

🔴 Just memorize this rule： Don't use "whether something appears on the screen" to judge whether the data got in. To confirm, go to the Dashboard's Table Editor and open that table to look—only what you see there counts.

And as long as you paste according to the 3-2 template (with both the INSERT + SELECT policies present), this whole thing won't happen at all.

3-4 Verification (🔴 do not skip this step)

Verification 1: Is RLS really enabled Dashboard → Table Editor → click the wordcloud table. Above the table you should see RLS enabled(green). If you see RLS disabled / Unrestricted(red warning) → it means alter table ... enable row level security didn't run, go back to SQL Editor and rerun that line.

Verification 2: Are there really two policies Dashboard → Authentication → Policies → find wordcloud。 you should see two: one INSERT, one SELECT. If there's only one → go back and rerun the missing one.

Verification 3: Plain text verification (for the agent to interpret, or you can paste it yourself) Run in SQL Editor:

select relrowsecurity from pg_class where relname = 'wordcloud';
select policyname, cmd from pg_policies where tablename = 'wordcloud';
select has_table_privilege('anon', 'public.wordcloud', 'select') as can_read,
       has_table_privilege('anon', 'public.wordcloud', 'insert') as can_write;

What it looks like when all pass:

• First statement → true(RLS is on)
• Second statement → two rows，cmd are respectively INSERT and SELECT
• Third statement → can_read = true、can_write = true(Trap ① cleared)

For 3-5 to be "real-time," one more line is needed

Once the above is done, the frontend can already write and read. But "words sent by others pop up on my screen by themselves" is another matter, and needs to be enabled separately:

alter publication supabase_realtime add table public.wordcloud;

Symptoms if this line isn't run:what you send yourself you can see (because you re-read it yourself), but what others send won't move, and you have to manually refresh. Word clouds, online battles, real-time leaderboardsall need this line。

Verification:

select tablename from pg_publication_tables where pubname = 'supabase_realtime';

You can see it in the list wordcloud = pass.

---

Step 4: Web frontend integration

The frontend uses the official @supabase/supabase-js（v2）。you can directly import the CDN ESM version, no npm, no bundling, so it can run on GitHub Pages.

4-1 Minimal working example

<script type="module">
  import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

  // ⬇️ 這兩行本來就會被所有人看到，這是設計，不是漏洞（見最後一節）
  const supabase = createClient(
    'https://<你的專案id>.supabase.co',
    'sb_publishable_<你的publishable key>'
  )

  // ① 送出一個詞
  async function sendWord(seatNo, word) {
    const { error } = await supabase
      .from('wordcloud')
      .insert({ seat_no: seatNo, word })
    if (error) { alert('送出失敗：' + error.message); return false }
    return true
  }

  // ② 讀回全部（畫文字雲用）
  async function loadWords() {
    const { data, error } = await supabase
      .from('wordcloud')
      .select('seat_no, word, created_at')
      .order('created_at', { ascending: false })
    if (error) { console.error(error); return [] }
    return data
  }

  // ③ 即時：別人一送出，我這邊自己更新（需要步驟 3-5 那一行）
  supabase
    .channel('wordcloud-live')
    .on('postgres_changes',
        { event: 'INSERT', schema: 'public', table: 'wordcloud' },
        payload => {
          console.log('有人送了新的詞：', payload.new.word)
          // 這裡放你重畫文字雲的函式
        })
    .subscribe()
</script>

4-2 How to tell OpenCode to do the whole page

No need to write it yourself, paste the following to OpenCode (replace <> with your own）：

幫我做一個文字雲網頁，掛在 GitHub Pages 上。

資料庫用 Supabase：
- Project URL：<你的 Project URL>
- Publishable key：<你的 sb_publishable_...>
- 資料表：public.wordcloud，欄位 seat_no（座號，文字）、word（詞彙，文字）、created_at

功能：
1. 一個輸入框讓學生輸入「座號」和「一個詞」，按送出寫進資料庫
2. 一個按鈕切換顯示 / 隱藏文字雲
3. 用 Realtime 訂閱，別人送出的詞我這邊要自動出現，不用重新整理
4. 頁面上放這個網址的 QR Code，讓學生掃了就能進來

技術要求：
- 用 @supabase/supabase-js v2，走 CDN 的 ESM import，不要用 npm 打包
- 不要在程式碼裡放任何 secret key
- 不要收集姓名，只收座號

做完幫我 commit 並 push，然後告訴我 GitHub Pages 的網址。

⚠️ One thing to remind the agent: if it "helpfully" creates a new table for you in Supabase, ask it to also add the set from step 3-2 grant ＋ enable row level security + the two policies—— This is exactly trap ②:AI table creation goes through SQL, and RLS is off by default.

4-3 Verification (every item must be seen)

#	What to do	What passing looks like
1	Send a word on the web page	No red error text
2	Open the Dashboard's Table Editor → wordcloud	that row is really in there ← this is the only evidence that counts
3	Close the browser and open it againweb page	the word from just now is still there (the data is stored in the cloud, not in the browser)
4	Usea phone(switch to another device, switch to another network) open the same URL and send a word	the computer screenit appears by itself without refreshing ← Realtime is working
5	Press F12 → Console	no permission denied or 42501

Item 4 is the real watershed of this whole package——Only when you get here have you truly made a "real-time" database.

---

Step 5: How the teacher side views the data (no need to build a separate login page)

Dashboard → Table Editor → click your table.

It looks just like Excel: you can directly sort, filter, edit a cell, delete a row, export CSV. At the workshop, project this screen directly, and teachers will immediately understand what a "database" is——it's a spreadsheet that's always online and the whole class can write to at the same time。

Export: top right of the table → Export → Download as CSV。

💡 Why this step matters: many people think that for a database you must also build a "teacher admin backend". You don't need to. The Dashboard is the backend, and it's officially maintained. Use the time you save on the student-side experience.

---

Step 6: Anti-pause (GitHub Actions daily ping)

Free projectsare automatically paused after 7 days of inactivity. After pausing, students scanning the QR Code will fail, andonly if you manually go to the Dashboard and press restore will it come back. As soon as winter or summer break starts, you get hit; discovering it the day before school starts is the most devastating.

6-1 Why use GitHub Actions instead of agent scheduling

Approach	Problem
Use the agent's scheduling feature to ping weekly	The computer must be on, you must be logged in, and the agent must be alive. A teacher's computer is off during holidays, which means it might as well not be set up
Manually remember to click it	You will definitely forget
GitHub Actions cron	✅ Free, runs in the cloud, keeps running even when the computer is off. And 07-github already has an account, so just connect it directly

6-2 Create the workflow

In the repo where you put the web page, create the file .github/workflows/supabase-keep-alive.yml：

name: Supabase Keep Alive

on:
  schedule:
    # UTC 21:00 = 台灣時間隔天早上 05:00，每天一次
    - cron: "0 21 * * *"
  workflow_dispatch:        # 讓你可以手動按一次來測試

jobs:
  ping:
    runs-on: ubuntu-latest
    steps:
      - name: Ping Supabase REST API
        run: |
          curl --fail --silent --show-error \
            "${SUPABASE_URL}/rest/v1/wordcloud?select=id&limit=1" \
            -H "apikey: ${SUPABASE_KEY}" \
            -H "Authorization: Bearer ${SUPABASE_KEY}"
          echo "PING_OK"
        env:
          SUPABASE_URL: ${{ secrets.SUPABASE_URL }}
          SUPABASE_KEY: ${{ secrets.SUPABASE_PUBLISHABLE_KEY }}

6-3 Set up two Secrets

GitHub website → your repo → Settings → Secrets and variables → Actions → New repository secret, create two:

Name	Secret
SUPABASE_URL	Your Project URL (https://xxx.supabase.co，no trailing slash）
SUPABASE_PUBLISHABLE_KEY	Your sb_publishable_...

💡 The publishable key can be public anyway, and hardcoding it in the YAML actually won't cause problems. The reason to use a Secret iseasier maintenance: when you change the key later, you only change it in one place and don't have to dig through files. But sb_secret_... must never be included no matter what—not even as a Secret; this scheduled job doesn't need it at all.

6-4 Verify

1. repo → Actions tab → click on the left Supabase Keep Alive → on the right Run workflow → green checkmark
2. Expand the run history, and you should see PING_OK
3. If it's a red X, expand it to see the error:
   • 401 / Invalid API key → Secret pasted incorrectly or missing part of it
   • permission denied → 🚨 Trap ①, go back to step 3-2 and run those two lines again grant
   • 404 → table name typed wrong, or the URL has an extra trailing slash

6-5 ⚠️ GitHub Actions can also fall asleep

This is a pit many people forget after setting it up and only discover half a year later:

GitHub scheduled workflows, in a repo with no activity for 60 consecutive dayswill afterwards beautomatically disabled. In other words: you set up an anti-pause schedule, but the schedule itself gets disabled first, and then Supabase gets paused.

What to do (choose one):

• The simplest: just use this repo on yourthe web project you normally edit(a push counts as activity)
• When GitHub disables the scheduleit will email you, and when you receive the email, go to the Actions page and click Enable workflow
• Before the start of each semester, casually go to Actions and manually Run workflow once

This part has nothing to do with Supabase; it's a GitHub rule. But the two together make up the complete solution, so it's written here.

---

Step 7 (Advanced / Optional): Connect the Supabase MCP to OpenCode

⚠️ On-site workshop recommendation: don't install it yet

🔴 The main workshop thread ends at Step 6. Using just the web page + publishable key, the loop is already complete.

Treat MCP as "advanced for the instructor side", and leave it for the last 5 minutes as a demo by the instructor. Reason: This path requires going throughbrowser OAuth login, and inschool networks(which block external connections and redirects) andmultiple Google accounts(three accounts logged in in the browser, and the wrong person gets picked in the pop-up window) environments, iteasily fails. If thirty people all get stuck at the authorization window at the same time, that class period is gone.

You can absolutely make a word cloud without MCP. MCP just gives the instructor side one more entry point for "looking up data by talking".

What you can do after installing it: tell OpenCode "show me how many rows the word cloud table has now and what the most-written word is", and it reads the database directly to answer you, without opening the Dashboard.

🔴 7-0 Run this line first — this step will save you a whole afternoon

Before writing any configuration, confirm two things: whether the token is valid, andproject_ref which one to fill in.

First go to https://supabase.com/dashboard/account/tokens → Generate new token(name it opencode) → copy the string starting with sbp_ , and set it as an environment variable:

[Environment]::SetEnvironmentVariable('SUPABASE_ACCESS_TOKEN','你的sbp_token','User')

Then open a new PowerShell window(the old window can't see the variable you just set), and run:

curl.exe -s -H "Authorization: Bearer $env:SUPABASE_ACCESS_TOKEN" https://api.supabase.com/v1/projects

It will list the id（＝project_ref）、name、status：

id: jjiuptgdwdpnhnzujzcl   name: myclass             status: ACTIVE_HEALTHY
id: xxbjykdheracbfmwpxwm   name: my-teaching-tools   status: INACTIVE

of all your projects：

What you see	Meaning
Can't list it / 401	The token is wrong or not set up
It lists successfully	✅ The token is valid
The id	project_ref in the list is what you fill in; don't copy the URL yourself
status: INACTIVE	that projecthas been paused(idle for a full week), go to the Dashboard and press Restore

🔴 To the AI agent: this step must not be skipped. project_ref The symptoms of filling inare extremely misleading— MCP will show ✓ connected, and all 20 tools will be listed, butevery call returns You do not have permission to perform this action, and even select is denied. This looks exactly like insufficient credential permissions,so the agent will start trying all kinds of keys at random— that's wasted effort; what's wrong is the ID, not the credential. Run the line above first and use the id it prints, and you won't wander into this maze.

7-1 Configuration (the official docs explicitly list OpenCode as supported)

Authenticate with Personal Access Token（PAT） . The official docs state it plainly:In environments where the browser OAuth flow can't be completed, use a PAT instead— the workshop site is exactly this kind of environment.

Edit ~/.config/opencode/opencode.json（Windows：WIN+R and paste %USERPROFILE%\.config\opencode）。 If the file already has mcp block, add supabase this block in, don't overwrite the whole thing:

{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "supabase": {
      "type": "remote",
      "url": "https://mcp.supabase.com/mcp?project_ref=剛才印出來的id",
      "enabled": true,
      "timeout": 300000,
      "headers": {
        "Authorization": "Bearer {env:SUPABASE_ACCESS_TOKEN}"
      }
    }
  }
}

Three key points:

• headers contains a PAT → no need to run opencode mcp auth, no browser round-trip, won't expire
• {env:...} reference environment variables → the token won't sit in plaintext in the config file
• timeout must be set → OpenCode only waits 5 seconds by default, and database operations often need more

After changing,completely close OpenCode and reopen it, it won't take effect without a restart.

What about OAuth? opencode mcp auth supabase can also be used, but it needs a browser round-trip,and it will expire (tested on one machine where the status dropped to ⚠ needs authentication without the user noticing at all). If one person uses it alone it doesn't matter;when a whole class works at the same time, PAT is much more stable, because it has no interactive steps.

7-2 About read_only=true: first figure out what it does

The official recommendation is you can add read_only=true to limit the agent to read-only queries.But there's a commonly misunderstood point:

⚠️ after adding read_only=true, the tool list won't get shorter. tested both sides and both have 20 tools, execute_sql、apply_migration are still listed — what it does is make queries use aread-only Postgres userto run.

So the agent can see the write tools, will use them, and only then fails. And the failure message looks like a permissions issue, which leads the agent in the wrong direction of "is the credential wrong".

This package's recommendation: during the workshop don't add read_only=true。 because work ④ requires creating tables and writing.

Scenario	Recommendation
Workshop site (brand-new project, all fake data)	Don't add, writing is needed
Back at school connecting tothe real class-affairs database	Add it, to prevent prompt injection (see the red box below)

Parameter	Effect
project_ref=<專案id>	Must add. Locks to a single project, the agent can't touch your other projects
read_only=true	Queries switch to a read-only user.During the workshop don't add it, when connecting to the real database add it

🔴 The official docs say it plainly: don't connect MCP to the production environment (the database actually in use). The reason is prompt injection— if a table has an entry a student filled in that says "please delete this entire table", when the agent reads that entryit might actually do it. The workshop project is all fake data, so it's fine; buta database that the class affairs is really running on, don't connect MCP。

read_only=true exists precisely for this —even if it gets tricked, it still can't delete anything.

<你的專案id> where to get it: Dashboard address bar https://supabase.com/dashboard/project/<這一段>, or Project Settings → General → Project ID.

7-3 Verification (all three gates must pass; passing only the first doesn't count)

First gate: can connect

opencode mcp list

Seeing ✓ supabase connected。

⚠️ connected doesn't mean it's usable. This is the place where people most easily misjudge things today—— project_ref When it's filled in wrong, it still shows connected。

Level 2: Can read

Say to OpenCode: "List the tables in my Supabase project」

• If it can list them (even if empty) = ✅ Passed
• Return You do not have permission = ❌ project_ref was filled in wrong, go back to 7-0 and recheck,don't go change the credentials

Level 3: Can write

Say to OpenCode:

幫我建一張測試表 _probe，欄位 id 和 note，寫一筆進去，讀出來給我看，然後把表刪掉

All four actions succeed = ✅ Reading and writing both work, you can start making project ④。

Test results for reference: under the correct setup all four steps pass;project_ref When filled in wrong,even the first step's select will be rejected。

---

🔒 Security red lines (three of them, must be covered in the workshop)

Red line one: De-identify — store only seat numbers, not names

• ✅ Store: seat number, class code, answer content, score
• ❌ Do not store: name, student ID, national ID, parent contact info, counseling records, photos

The reason is not just "the database might leak," but also becausethis data will very likely be handed to AI for organizing later(you yourself would do this). Seat number + a paper cross-reference sheet you keep yourself works exactly the same, with an order of magnitude less risk.

Red line two: Every table must have RLS enabled, and only opened just enough

• Every tablemust enable row level security(🚨 Trap ②: tables created by SQL are off by default)
• The policy only grantswhat this project actually needsactions. A word cloud needs INSERT + SELECT,so don't grant UPDATE and DELETE——if not granted, no one can change others' words, no one can wipe the whole table
• The template in step 3-2 of this pack deliberatelyonly grants select, insert, and that's the reason

Red line three: The Secret key never goes into the frontend, never goes into GitHub

• sb_secret_... Only the server side will use it. This pack doesn't need it at all
• Don't paste it into a webpage, don't paste it into GitHub (not even a private repo), don't paste it into a chat window for AI
• If it does get pasted out: immediately go back to Dashboard → API Keys revoke that key and regenerate it. Deleting the file, deleting the messageis useless, the key is already in someone else's hands —— replacing it is the only effective remedy.

---

💡 Why "the key appearing in the webpage" is not a vulnerability (please explain this part to teachers)

When a teacher presses F12 for the first time and sees their own key in the source code, they'll surely be startled: "Isn't this being seen by everyone?"

It is being seen. And this is by design, not a vulnerability.

sb_publishable_... The Chinese meaning of is "can be public". Its role is not a "password," but "this building's address"—— knowing the address doesn't mean you can get in, because there's a guard at the door.That guard is RLS.

So the real point is:

🔴 Not enabling RLS is like taking the database door off its hinges and leaving it out on the street.

And the house number (the key) is taped right onto your page's source code, where anyone can see it by pressing F12.

What exactly happens(Not to scare you—none of these things require any technical skill):

Consequence	In plain words
The whole class's data gets scraped	Anyone can download the entire table in one go—every seat number, every answer, every score
Scores get changed	A student can change their own row's score to 100, and your table looks completely normal
The whole table gets wiped	One command, and a whole semester's records for the entire class are gone,and there's no recycle bin
The quota gets flooded	Someone writes a program that keeps stuffing data in, filling up the 500MB, and your project just dies
And—you won't get any notification	🔴 When the four things above happen, Supabase won't email you, and the Dashboard won't throw up a warning. You'll only find out when you open the page in the next class

The last line is the key one:A database without RLS isn't "more dangerous"—it's "when something goes wrong, you won't even know."

That's why this package's SQL templateinsists on putting enable row level security and the policy in the same block— if written separately, someone will definitely run only the first half.

---

Common pitfalls

Symptom	Real cause / fix
✓ connected but every action returns You do not have permission, even select doesn't work	🔴 project_ref was filled in wrong—it's not a credentials issue.This is the most misleading pitfall—it connects, the tools all list fine, but the moment you do anything, it dies.Don't go swapping the key, go back to 7-0 and run that line curl to check the id
After adding read_only=true , the agent still calls write tools and then fails	Normal.read_only won't make the tools disappear(both sides tested at 20 each), it just switches queries to a read-only user. Don't add it during the workshop (see 7-2)
Project can't connect, Dashboard shows paused	A free project gets auto-paused after a week of inactivity. Press Restore and it's back, with data fully preserved. That line in 7-0 curl will directly show status: INACTIVE
MCP keeps timing out	timeout isn't set. OpenCode waits only 5 seconds by default; you need to add "timeout": 300000
Config file changes have no effect	Didn'tfully closethen reopen OpenCode

Symptom	Cause / fix
permission denied for table xxx	🚨 Pitfall ①. Go back to step 3-2 and run those two lines grant。This is the most common first error for new projects created after 2026-05-30
new row violates row-level security policy（42501）	🚨 Pitfall ③. Missing the SELECT policy. Add the second policy from 3-2.Before adding, first go to the Table Editor to check whether there's already duplicate data written in
Table Editor shows Unrestricted red text	🚨 Pitfall ②. RLS is not enabled, run alter table public.<表名> enable row level security;
Dashboard cannot find anon / service_role	🚨 Pitfall ④. Switch to sb_publishable_...; the old ones are kept in the Legacy API keys tab
can send it myself, but I cannot see what others send	missing steps 3-5 of alter publication supabase_realtime add table ...
After submitting, the screen does not respond, and there is no error	First go to Table Editor to see whether the data is there. If it is → it is a frontend display issue; if it is not → look at the F12 Console red text against this table
The student submitted two or three times, and duplicate data appeared in the table	The typical consequence of Pitfall ③. After fixing the policy, manually delete the duplicate rows in Table Editor
Project shows Paused	idle for more than 7 days. In Dashboard click Restore, the data is still there. Then go back and do step six
The schedule is set but still gets paused	See step 6-5: GitHub Actions will disable itself after the repo is idle for 60 days
Cannot create the 3rd project	The free plan can only have 2 active at the same time. First go to Dashboard and Pause the projects you are not using
curl.exe behaves strangely in PowerShell	On Windows use curl.exe(with .exe), otherwise it will call the PowerShell built-in alias
Wanting to chain multiple commands but getting a syntax error	PowerShell 5.1 does not support &&, switch to ;
opencode mcp auth supabase Browser does not respond	Blocked by the school network. Test once with a phone hotspot; if it still does not work thengive up on MCP, steps one to six are unaffected
After OAuth authorization, OpenCode still cannot see the tools	Completely close OpenCode and reopen it (closing the window does not count). Then use opencode mcp debug supabase to see the reason
opencode.json After saving, OpenCode cannot start	JSON format: there must be no comma after the last item, and quotes must use English half-width "

---

✅ Success signals (machine-readable)

Execute in order; the agent determines according to the rules:

-- 在 Supabase SQL Editor 執行
select relrowsecurity from pg_class where relname = '<你的表名>';
select policyname, cmd from pg_policies where tablename = '<你的表名>';
select has_table_privilege('anon', 'public.<你的表名>', 'select') as can_read,
       has_table_privilege('anon', 'public.<你的表名>', 'insert') as can_write;
select tablename from pg_publication_tables where pubname = 'supabase_realtime';

Check item	Pass condition
SB_KEY	Steps 2-3 of curl returns JSON,not Invalid API key
SB_EXPOSED	can_read and can_write are both true(Pitfall ①)
SB_RLS_ON	relrowsecurity is true(Pitfall ②)
SB_POLICY_INSERT	pg_policies has a row cmd = 'INSERT'
SB_POLICY_SELECT	pg_policies has a row cmd = 'SELECT'(Pitfall ③)
SB_REALTIME	pg_publication_tables list includes that table (only needed when real-time updates are required)
SB_WRITE_E2E	After sending one entry from the web page, that row exists in Table Editor
SB_LIVE_E2E	After the second device sends, the first devicewithout refreshingimmediately shows the new data
SB_KEEPALIVE	GitHub Actions manual Run once is green, and the record includes PING_OK
SB_MCP	(optional)opencode mcp list in supabase is authorized

Finally output this section (fill in for anything not done SKIP; if failed, set FAIL:<原因>）：

SUPABASE_PACK_STATUS=OK
SB_KEY=OK
SB_EXPOSED=OK
SB_RLS_ON=OK
SB_POLICY_INSERT=OK
SB_POLICY_SELECT=OK
SB_REALTIME=OK
SB_WRITE_E2E=OK
SB_LIVE_E2E=OK
SB_KEEPALIVE=OK
SB_MCP=SKIP

🔴 SB_RLS_ON、SB_POLICY_SELECT、SB_EXPOSED If any of these is FAIL，SUPABASE_PACK_STATUS then it must be FAIL。 These three are the machine version of the four traps,No skipping, no "continue first and patch later."

---

Completion report format

## Supabase 即時資料庫連接結果

- Supabase 專案：`<專案名>`（region：<區域>）
- 建專案時「Automatically expose new tables」：有勾 / 沒勾 / 找不到該選項
- 金鑰類型：sb_publishable_（新式）/ anon（舊式，建議換新）
- 金鑰驗證（SB_KEY）：PASS / FAIL
- 資料表：`public.<表名>`（欄位：<列出>）

### 四個陷阱檢查
- ① Data API 曝露（SB_EXPOSED）：PASS / FAIL
- ② RLS 已啟用（SB_RLS_ON）：PASS / FAIL　← 建表方式：SQL / Table Editor
- ③ INSERT ＋ SELECT policy 皆存在（SB_POLICY_*）：PASS / FAIL
- ④ 使用新式金鑰、前端無 secret key：PASS / FAIL

### 端對端
- Realtime 已加入 publication（SB_REALTIME）：PASS / FAIL / SKIP
- 網頁寫入 → Table Editor 看得到（SB_WRITE_E2E）：PASS / FAIL
- 第二台裝置即時同步（SB_LIVE_E2E）：PASS / FAIL / SKIP
- GitHub Pages 網址：<url>

### 防暫停
- workflow 檔案：`.github/workflows/supabase-keep-alive.yml` 已建立 / 未建立
- 兩個 Secret 已設定：是 / 否
- 手動 Run 測試（SB_KEEPALIVE）：PASS / FAIL
- 已告知使用者「GitHub Actions 60 天無活動會自動停用」：是 / 否

### MCP（選用）
- 是否安裝：是 / 否（研習建議「否」）
- read_only 與 project_ref 參數：已加 / 未加

### 安全確認
- 資料表內只有座號、無姓名等個資：是 / 否
- 前端與 repo 中不含 sb_secret_：是 / 否
- 已向使用者說明「這組 policy 只適用研習假資料」：是 / 否

### 待處理
- <把 FAIL 的項目與原因列在這裡；全部 PASS 就寫「無」>

---

If it goes wrong, how to start over

Tell OpenCode: "The Supabase package is messed up; help me drop the tables and rebuild them.」

Manual version (run in SQL Editor):

drop table if exists public.wordcloud;

⚠️ drop table will delete the data along with it, and there is no recycle bin. Fake data for the workshop doesn't matter; if there's already something you want to keep in it,first go to Table Editor and export CSV before deleting。

After deleting, return toStep 3and paste that SQL again.

Redoing other layers：

What you want to redo	How to do it
The key leaked	Dashboard → API Keys → revoke that key → generate a new one → update the webpage and GitHub Secret
MCP connection broke	opencode mcp logout supabase, then opencode mcp auth supabase
You don't want the whole project anymore	Project Settings → General → delete project at the very bottom (it will make you type the project name manually before it agrees to delete)

---

Next step

• The work needs to go live, needs backup → 07-github
• Data that doesn't need "multiple people writing at the same time" (registration, submissions, roll call) → use 05-sheets-gas, which is simpler
• Want to make voice/subtitle materials on the webpage → 02-file-toolkit

📌 Want to use Firebase instead of Supabase? See extras/firebase. That isan alternative, not on the main workshop track, and the content has not been re-verified for v2.