Chapter 2: The Brain's Memory Center - Perfectly Connecting FastAPI with Supabase Database
In previous Course 1 and 2, we learned how to use JavaScript (in React or Astro) on the frontend to connect with Supabase and display data on web pages.
Now we're entering backend territory! Our Line bot can't just have a "goldfish memory" - it needs to remember employees' daily check-in times.
This chapter will teach you how to use Python to connect with the Supabase cloud database and securely/stably write the check-in records received from Line Bot!
🎯 Chapter Goals
- Learn how to integrate Supabase SDK in FastAPI (Python environment).
- Extract database connections into an independent module (database.py) - the standard architecture for enterprise-level projects.
- Implement real "check-in for work" logic.
- Successfully INSERT check-in data into the Supabase database.
🔗 Step 1: Initialize Supabase Client
In backend projects, we must never hardcode database connection passwords directly in main.py. Otherwise, if uploaded to GitHub, your database will be exposed to the world and could even be maliciously deleted.
We need to write the connection logic in a separate file and read sensitive information from .env.
🔥【Vibe Prompt Practical Spell】
I'm developing a backend with Python FastAPI. Please teach me how to use the supabase-py package to connect to my cloud database.
1. Help me create a standalone file called database.py.
2. It must use the dotenv package to read SUPABASE_URL and SUPABASE_KEY from .env file.
3. Create a global variable named supabase_client (generated via create_client) so other files can directly import it to operate the database.
4. Include the required pip package installation commands.
The AI will tell you to first install packages:
pip install supabase
Then write this extremely clean database.py:
import os
from supabase import create_client, Client
from dotenv import load_dotenv
# Load environment variables from .env file
load_dotenv()
# Read sensitive URL and key (never write real strings here!)
url: str = os.environ.get("SUPABASE_URL")
key: str = os.environ.get("SUPABASE_KEY")
# Create single connection instance
supabase_client: Client = create_client(url, key)
With this file, anywhere in your project you can simply write from database import supabase_client to directly issue commands to the database!
⏱️ Step 2: Implement Check-in Writing Logic
Assume we've already created a table named punch_records in Supabase.
This table contains three fields: user_id (string), punch_type (string, 'in' or 'out'), created_at (auto-generated timestamp).
We need to write data when Line sends the text "上班" (start work).
🔥【Vibe Prompt Practical Spell】
In my main.py, I've already written a Webhook (handle_message) to process Line text messages.
Please help me add database writing logic:
1. Check if user input text (event.message.text) is "上班".
2. Import supabase_client from database.py.
3. INSERT a record into punch_records table. Fill user_id with event.source.user_id from Line, punch_type with 'in'.
4. If an Exception occurs during writing (e.g. network disconnection), catch the error and return "System busy, check-in failed" to user.
5. If writing succeeds, use line-bot-sdk to return "Check-in successful! Boss loves you!" to user.
The AI will perfectly integrate this writing logic into your original Webhook code:
from database import supabase_client
from linebot.v3.messaging import TextMessage, ReplyMessageRequest
# Text message handler
@handler.add(MessageEvent, message=TextMessageContent)
def handle_message(event):
user_id = event.source.user_id
text = event.message.text
if text == "上班":
try:
# 💡 Call Supabase for remote writing
data = supabase_client.table("punch_records").insert({
"user_id": user_id,
"punch_type": "in"
}).execute()
# If execution reaches here, writing succeeded!
reply_text = "上班打卡成功！老闆愛你！"
print(f"✅ User {user_id} check-in data written successfully")
except Exception as e:
# Catch any possible system errors (prevent server crash)
reply_text = "系統忙線中，請稍後再試！"
print(f"❌ Writing failed: {e}")
else:
reply_text = "我聽不懂你在說什麼，請輸入「上班」或「下班」。"
# Send assembled reply_text back to Line server
with ApiClient(configuration) as api_client:
line_bot_api = MessagingApi(api_client)
line_bot_api.reply_message_with_http_info(
ReplyMessageRequest(
reply_token=event.reply_token,
messages=[TextMessage(text=reply_text)]
)
)
⚠️ [Common Minefield] Forgetting to Set Row Level Security (RLS)
When you excitedly type "上班" on your phone, FastAPI throws an error:
postgrest.exceptions.APIError: {'code': '401', 'message': 'new row violates row-level security policy'}
This isn't a Python error! It's because Supabase is an extremely security-conscious system.
By default, newly created tables are "locked". If you don't set RLS policies to allow INSERT operations, any external write attempts will be mercilessly rejected.
Solution: Go to Supabase web admin, navigate to Authentication -> Policies, and add a Policy for your punch_records table to "Allow all Insert permissions", then your code can write successfully!
💼 [Business Application Scenario] Data is the Most Valuable Asset
Now your Line bot is no longer just a "mindless parrot" that repeats what you say - it has evolved into a business system that can store real-world data (check-in records) long-term in the cloud!
When these records accumulate for a month, you can use them to calculate salaries, analyze employee tardiness rates, or even serve as court evidence for attendance.
But just writing data isn't enough - plain text replies of "check-in successful" are too boring to convince bosses this system is worth tens of thousands.
Next chapter, we'll teach you how to use Line's exclusive JSON syntax to send awesome Flex Messages (bubble cards), which will be the key trick to increasing project valuation!