Baserow:CloneCoding

lastmod 2026-06-12created 2026-06-123 edits

Insights for clone coding Baserow

1. Choosing an architecture approach

These are two core patterns to consider when building a No-code/Low-code DB service like Baserow or Airtable on top of Supabase (PostgreSQL). Considering implementation difficulty and flexibility, this project adopts Approach B (metadata approach).

Approach A: Dynamic DDL approach (Baserow's original approach)

• Concept: Every time a user creates a table, an actual CREATE TABLE user_table_1 ... SQL command is sent to the DB to create a physical table.
• Pros: Postgres indexing, sorting, and type system can be used as-is, giving excellent query performance on large data.
• Cons: It cannot be implemented with the Supabase client SDK (JS/TS) alone and requires a separate backend (Edge Functions, etc.) to run DDL. Schema migration and permission management become extremely complex.

Approach B: Metadata schema approach (recommended)

• Concept: The physical table structure is fixed, and all information on user-created tables and columns is stored as data (Rows) (a variant of the EAV pattern).
• Pros: It is the safest and most intuitive to implement in a Supabase environment. RLS (Row Level Security) is easy to configure, and schema changes are unrestricted.
• Cons: As data grows, join (Join) operations increase, so thorough indexing and optimization strategies are essential.

2. Recommended table schema design (based on Approach B)

These are the DDL structures of the 5 core tables that are smoothest to implement in Supabase. The existing Workspace concept is replaced with Project.

1) projects (projects)

Manages user groups or organizations and integrates with Supabase Auth.

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

2) tables (virtual table definitions)

Stores the names and information of the "virtual tables" users create within a project.

CREATE TABLE tables (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    name TEXT NOT NULL, -- 예: "고객 명단", "할 일 목록"
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

3) columns (virtual column definitions)

Defines the names and data types (text, number, date, checkbox, etc.) of the columns in a table.

CREATE TABLE columns (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_id UUID REFERENCES tables(id) ON DELETE CASCADE,
    name TEXT NOT NULL, -- 예: "이메일", "나이"
    type TEXT NOT NULL, -- 예: 'text', 'number', 'date', 'boolean', 'select'
    options JSONB,      -- 선택지(Select) 타입일 경우의 옵션 목록 {'choices': ['A', 'B']}
    order_index INT NOT NULL -- 컬럼 출력 순서
);

4) rows (row data skeleton)

The horizontal lines (Rows) that hold the actual data. The data itself is not stored in this table; it only manages order and creation date.

CREATE TABLE rows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_id UUID REFERENCES tables(id) ON DELETE CASCADE,
    order_index INT NOT NULL, -- 행 정렬 순서
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

5) cells (actual cell data values)

The most central table, where the actual values are stored. It uses Postgres's JSONB type to flexibly accommodate various data types.

CREATE TABLE cells (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    row_id UUID REFERENCES rows(id) ON DELETE CASCADE,
    column_id UUID REFERENCES columns(id) ON DELETE CASCADE,
    value JSONB, -- 어떤 타입이든 JSON 형태로 저장 (예: "홍길동", 25, true)

    -- 성능 최적화 및 데이터 무결성을 위한 복합 유니크 제약
    CONSTRAINT unique_row_column UNIQUE (row_id, column_id)
);

3. Data manipulation and query examples

Data entry (saving a Cell)

The form loaded into the cells table when a user enters [email protected] in the "Email" column.

{
  "row_id": "행_UUID",
  "column_id": "이메일_컬럼_UUID",
  "value": "\"[email protected]\""
}

Data query (for Grid UI rendering)

An example of using the Supabase JS SDK to fetch data in a single query to draw a spreadsheet-like grid screen.

const { data, error } = await supabase
  .from('rows')
  .select(`
    id,
    order_index,
    cells (
      column_id,
      value
    )
  `)
  .eq('table_id', '조회하려는_테이블_UUID')
  .order('order_index', { ascending: true });

• On the frontend, the received cells array is processed into an object keyed (Key) by column_id and mapped to the UI.

4. Per-Project localization/isolation strategies for large data

If projects and cell data grow exponentially, a single-table structure hits performance limits, so the following isolation strategies are considered.

5. Proposed project development roadmap

Rather than forcing sharding in from the initial implementation, a step-by-step scale-out strategy is recommended.

• Phase 1 (prototype): Start with a single-table structure, but for future isolation, always include a project_id column in every data table (rows, cells, etc.). Design indexes thoroughly as composite indexes (project_id, row_id, column_id).
• Phase 2 (growth): Once data exceeds tens of millions of records, switch to the 1. Table Partitioning (Table Partitioning) structure, which secures performance with almost no changes to existing code.
• Phase 3 (enterprise): If the load of a particular large project affects the whole system, switch to a 3. Sharding (Sharding) scheme that migrates only that project to a separate physical Supabase instance.

6. Recommended Postgres Extensions for performance optimization

Using the PostgreSQL extensions provided by Supabase lets you build large-data queries and automatic partitioning structures much more efficiently.

1) pg_partman (automatic partition management) ⭐️ Most recommended

Implementing the 'per-project table partitioning (Strategy 1)' mentioned above manually is cumbersome because you have to write a table-creation trigger every time. pg_partman is an extension that automates this.

• Main use: Automatically creates, maintains, and repairs partition tables by ID range (e.g., Project ID range) or by time.
• Project tip: Dramatically reduces backend effort when dynamically partitioning the cells or rows tables, which will grow to tens of millions of records, by project ID.

2) pg_stat_statements (query performance bottleneck analysis)

When the service slows down, you need to find exactly which user-defined table or which cell query is causing the bottleneck.

• Main use: Records statistics such as execution time, call count, and memory usage for every executed SQL query.
• Project tip: This is the extension underlying the dashboard performance analysis tab in the Supabase dashboard. It is essential for monitoring, in real time, the slow queries (Slow Query) that occur when the frontend loads the grid, and for timing when to add indexes.

3) pgcrypto (encryption and high-performance UUID)

By the nature of No-code tools, every virtual table, column, and row (Row) has a unique ID (UUID). UUID generation efficiency affects overall record insertion speed.

• Main use: Provides high-performance UUID generation functions such as gen_random_uuid() and data encryption features.
• Project tip: It is built into Supabase by default, but given the metadata structure it becomes the core backbone that guarantees integrity and performance when generating UUIDs for hundreds of millions of rows/cells.

4) intarray / btree_gin (JSONB and array index optimization)

In the metadata approach, columns options and the actual values in cells are stored as the JSONB type. These are needed when you have to search the data inside this JSON quickly.

• Main use: Helps you put high-performance, large-scale indexes (GIN) on values inside JSONB or complex array data that the default B-Tree index does not support.
• Project tip: For example, when implementing features where users "filter only cells containing specific text" or "filter only rows containing a specific tag (Array)", it can boost regular query performance by tens of times or more.

See also

• Baserow