Chapter 7: PostgreSQL Minimalist Database Design - The Core Hub of Permissions and Orders

When designing system architecture during early development, many junior engineers (and even some experienced backend engineers) often fall into the trap of "over-engineering" when designing e-commerce or course databases.

They'll open ER diagram software and create an intricate structure: Create a Users table, then a Courses table, followed by an Orders table to record purchases, then need an Order_Items table for details, and finally add a Transactions table for financial reconciliation... ending up with complex Foreign Keys and JOIN queries.

But for a SaaS platform focused purely on "knowledge monetization, selling virtual goods with no inventory or logistics issues," this massive traditional e-commerce design is not only extremely painful to develop and maintain, but also completely unnecessary in 99% of scenarios.

In Vibe Tutor's system architecture, we adhere to the Minimal Viable Product (MVP) philosophy. We've compressed the entire core business permission logic to the extreme - ultimately requiring just one table.

🧊 The One Table Philosophy: `vt_purchases`

In our Supabase PostgreSQL database, we created a table called vt_purchases (purchase records). This table has extremely concise fields, yet perfectly supports all our "permission unlocking logic" and "backend sales analytics."

Deep Dive into Field Design

💡 The Core Soul: The Art of Permission Checking via `item_id`

We've compressed all "permission locking logic" into the plain text item_id field. This eliminates the need for complex "course table" and "order detail table" relationships. Recall how we set item_id during checkout:

Purchasing $99 introductory course: Writes item_id = "coding-101". Unlocks text content for this course.
Purchasing $1999 advanced mapping course: Writes item_id = "car-camping-map". Similarly unlocks that course's content.
Purchasing $3999 source code package: Writes item_id = "car-camping-map-source". Frontend code checks for IDs ending with -source to unlock both text content and display a ZIP download button.
Purchasing $9999 ultimate VIP pass: Writes item_id = "vip-all-access". When the system sees this ID, it's like seeing an ancient "get out of jail free card" - immediately return true, granting unconditional access to all courses and downloads!

This flat design makes database queries lightning fast and keeps code extremely clean:

// 🚀 Single query to get all user purchases - no complex table JOINs needed!
const { data: purchases } = await supabase
  .from("vt_purchases")
  .select("item_id")
  .eq("user_id", user.id);

// Extract to string array: ['coding-101', 'car-camping-map']
const purchasedItemIds = purchases.map(p => p.item_id);

// Check course access
const canAccess = purchasedItemIds.includes(currentCourseId) || purchasedItemIds.includes("vip-all-access");

🛡️ RLS (Row Level Security) Defense

Using Backend-as-a-Service (BaaS) like Supabase enables rapid development but introduces a critical security issue beginners often overlook: "If Supabase's API Key is exposed in frontend code, couldn't hackers with basic programming skills use browser Console to call APIs and insert database records, granting themselves free courses?"

To solve this serious vulnerability, PostgreSQL provides industry-leading RLS (Row Level Security) mechanisms. For our revenue-critical vt_purchases table, we've implemented extremely strict RLS Policies:

Select (Read) Policy:
```
-- SQL Policy Rule
(auth.uid() = user_id)
```
Business Protection: Users can only read records where user_id matches their login UID. You'll never see others' purchase history, preventing competitors from scraping customer lists and sales data, ensuring strict compliance with privacy laws.
Insert/Update/Delete (Write) Policies: All Disabled! This means: Frontend JavaScript code has absolutely no permission to write or modify this table, regardless of hacker tampering.

❓ Then how are normal orders recorded?

If frontend can't write data, how do we store records after payment? The answer lies in backend servers using the Service Role Key (Super Admin Key).

Our server-side code (in Next.js /api Route Handlers) uses a Supabase Client with maximum privileges, carrying the .env-stored SERVICE_ROLE_KEY. This key bypasses all RLS policies for direct database writes.

When ECPay's server calls our webhook (payment success notification), this occurs through a secure "server-to-server" channel. After verification, our backend API wields this super key to safely write data into vt_purchases.

This "frontend can only query own data, backend handles core writes" design forms an impregnable final defense for knowledge monetization platforms!

✅ Chapter Summary

The best architecture is often the simplest. Through One Table philosophy and robust RLS defenses, we've built a highly scalable, ultra-secure, zero-maintenance database layer. Ready for the ultimate challenge? Next chapter tackles Taiwan e-commerce's most complex yet profitable hurdle: ECPay payment API integration and verification!

Chapter Summary

Understand core concepts and principles
Master implementation methods and techniques
Familiar with common issues and solutions
Able to apply in real projects

Chapter 7: PostgreSQL Minimalist Database Design - The Core Hub of Permissions and Orders

🧊 The One Table Philosophy: `vt_purchases`

Deep Dive into Field Design

💡 The Core Soul: The Art of Permission Checking via `item_id`

🛡️ RLS (Row Level Security) Defense

❓ Then how are normal orders recorded?

✅ Chapter Summary

Chapter Summary

Further Reading

Unlock Full Tutorial

Chapter 7: PostgreSQL Minimalist Database Design - The Core Hub of Permissions and Orders

🧊 The One Table Philosophy: vt_purchases

Deep Dive into Field Design

💡 The Core Soul: The Art of Permission Checking via item_id

🛡️ RLS (Row Level Security) Defense

❓ Then how are normal orders recorded?

✅ Chapter Summary

Chapter Summary

Further Reading

Unlock Full Tutorial

🧊 The One Table Philosophy: `vt_purchases`

💡 The Core Soul: The Art of Permission Checking via `item_id`