Create a schema file (.sfn)

The fun of WebSpace starts with one small file. When you define the data to store and the permissions in a schema file (.sfn) and deploy it, the database and the APIs are prepared together. You can go on to build a service in which the content entered on a screen is actually stored and appears on other users' screens in real time.

A schema file is the blueprint of a service. Saving the file does not change the actual database. It is applied to the runtime environment only when you deploy it in API Build.

How it is built

1. Create a schema file.
2. Set up the tables and columns.
3. Check the access permissions for each API.
4. Select a storage engine and deploy.
5. Call the generated APIs from HTML or an app.

You do not need to complete every item from the beginning. The easiest way to understand it is to start with one small table, enter data from an actual screen, and then add the structure and permissions you need.

Create a new schema file

In the workspace, select Create > Template Files > New Schema DB.

Enter a file name and save to create the schema file.

You do not choose a database engine at this step. Create the data structure first, and then select the actual storage engine from SQLite3, PostgreSQL, MySQL, or MariaDB in the deployment step.

Create the data structure

You can create it yourself from scratch, or draft it with AI and refine it. If you already have SQL that you used, you can import it and start from there.

1. Create a draft with AI

You do not need to know every table term. Describe first what information you want to manage and with whom, and the AI creates a structure to start from.

1. Click the Copy AI prompt button.
2. Paste the copied prompt into the chat window of the AI service you use.
3. Under ## Request at the end of the instructions, write the service you want to build.

   Example: Manage the patent name, application number, person in charge, and progress status, and allow only the person in charge and administrators to edit.

4. Copy the JSON created by the AI and paste it into the import screen.
5. Select Import to create the table structure in the schema, as shown in the figure below.

The copied instructions include the format and writing rules of the schema file. They prefer a structure that can be used with multiple storage engines over SQL tailored to one specific database.

The Copy AI prompt button copies the following instructions. This example was copied from an empty schema with the SQLite engine and the English UI; the content depends on the current schema, the database engine, and the UI language. Start from this prompt instead of writing the schema file by hand.

You are a database schema assistant.
Return only valid JSON.
Do not add explanations.
Do not wrap the answer in markdown fences.
Return only the data array for a SetFN schema file.
Do not include content-type, dbType, or apiBuild.
Current deployment engine: SQLite.
The storage engine is deployment configuration, not schema syntax. Keep the schema portable across SQLite, PostgreSQL, MySQL, and MariaDB.

## Language
- Identifiers are English and ASCII: "label", table names and column names are English snake_case; JSON keys, types and defaults stay exactly as shown.
- Human-facing strings are written in English (the user's UI language, locale "en"): every "cmt" (table and column comments, including the allowed values description).
- The user's request below may be written in English or Korean. Read it as requirements; never translate identifiers into that language.
- The current state below is data and may contain text in any language. Keep existing names and keys exactly as they are.

Security and portability rules:
- Never output passwords, tokens, API keys, connection strings, hosts, ports, database users, or other credentials.
- Table names, "label" and column names are ASCII snake_case (never Korean or other non-ASCII letters). Every table has columns, starting with the id column shown below.
- Use only portable SetFN logical types: serial4, serial8, int2, int4, int8, integer, real, float4, float8, numeric, decimal, varchar, char, text, bool, json, timestamp, timestamptz, date, time, bytea, blob, uuid. Write the bare type name and put a length in "len" ("type": "varchar", "len": 20), never "varchar(20)".
- Do not emit database-native type expressions or SQL fragments such as enum(...), SET(...), unsigned inside a type string, arrays, generated columns, engine clauses, or vendor-specific default functions.
- When a field has a fixed set of values, use "text" and document the allowed values in "cmt". Do not invent an enum property.
- Do not make an endpoint public unless the user explicitly requests public access. Never make create, update, or delete public by default.
- Default every table to signed-in access. Use ownerOnly only with a valid owner column such as "_user_id".

Use canonical defaults regardless of deployment engine.
- Auto ID columns: "auto_increment"
- Timestamp defaults: "now()"
The deployment service converts these logical types and defaults to the selected engine.

Recommended system columns:
- "_user_id": owner user id. Prefer this instead of custom fields like "user_id" or "author_id" when the record belongs to the signed-in user.
- "_nick": writer nickname. This can be auto-injected from session data, so do not create separate duplicate nickname fields unless truly needed.
- "_created_at": created timestamp. Prefer this instead of custom fields like "created_at".
- "_updated_at": updated timestamp. Prefer this instead of custom fields like "updated_at".
- "_deleted_at": optional soft-delete timestamp. Include only when soft delete is needed.

System column guidance:
- These system columns are optional, but prefer them when they fit the use case.
- If the table needs writer/owner tracking, prefer "_user_id" and optionally "_nick".
- If the table needs created/updated times, prefer "_created_at" and "_updated_at".
- Avoid generating duplicate custom columns such as "user_id", "nick", "created_at", or "updated_at" when the matching system columns can be used.
- It is okay to omit system columns if the requested schema clearly does not need them.

Turn-based board game contract:
- Apply this only when the user asks for a turn-based board game such as a line-connection game.
- Create three portable tables whose names may match the user's game: a games table, a moves table, and a players table.
- The games table must contain: id, room_code (unique), status, black_user_id, white_user_id, current_turn, winner_user_id, result, move_count, board_size, win_length, black_time_ms, white_time_ms, turn_started_at, finish_reason, finished_at, _created_at, _updated_at.
- The moves table must contain: id, game_id (indexed), move_no, user_id, stone, row, col, result, _created_at. Make (game_id, row, col) unique when the schema format supports it.
- The players table must contain: id, user_id (unique), nickname, wins, losses, draws, score, _updated_at.
- Use portable logical types and defaults. Do not add an eventProcessor field to table data. After schema generation, the user connects these table names to the registered "turn-based-board" processor in API Build > Feature connections.
- Do not invent processor IDs or game-specific server handlers.

Required shape:
[
  {
    "label": "users",
    "table": { "name": "users", "cmt": "Members" },
    "columns": [
      {
        "name": "id",
        "type": "serial4",
        "notnull": true,
        "primary": true,
        "index": false,
        "unique": false,
        "default": "auto_increment",
        "cmt": "PK"
      },
      {
        "name": "_user_id",
        "type": "int4",
        "notnull": true,
        "index": true,
        "unique": false,
        "default": null,
        "cmt": "Owner ID (injected from the session)"
      },
      {
        "name": "_created_at",
        "type": "timestamp",
        "notnull": false,
        "index": false,
        "unique": false,
        "default": "now()",
        "cmt": "Created at"
      }
    ],
    "permissions": {
      "security": { "login": true, "ownerColumn": "_user_id" },
      "ops": {
        "list": { "login": true },
        "view": { "login": true },
        "create": { "login": true },
        "update": { "login": true, "ownerOnly": true },
        "delete": { "login": true, "roles": ["admin"] }
      }
    }
  }
]

Example "cmt" values are English only to show the shape; follow the Language rules for real values.

Current data:
[]

## Deliverable
Reply with only the data array JSON in the format above. Write human-facing text such as descriptions, labels and messages in English.

## Request
[Describe the schema you want to create or change here]

The AI result is only a draft. Before deployment, check for yourself whether the names are easy to understand, whether personal information is included, and who must be able to view and edit the data.

2. Import SQL

In Import > SQL, you can paste existing CREATE TABLE statements. The main types, default values, indexes, and constraints of SQLite, PostgreSQL, and MySQL/MariaDB are read and converted into the schema file structure.

If you opened a SQL file in the workspace, you can also use Convert to SetFN Schema. The same conversion rules apply whichever path you use.

3. Create it directly on the screen

On the table-style editing screen, you can add tables and columns one at a time.

Add a table

Click the Add Table button at the top of the main screen. Enter the table information.

Add a column

Click the Add button and select Add at Top or Add at Bottom to add one new row to the column list. One row corresponds to one column.

You can set the following items.

In the System Columns menu, you can pick and add columns that are often used to record the user and the time of writing, such as _user_id, _nick, and _created_at.

Copy and delete a column

Right-click a column to show the context menu.

• Copy and add: Choose Copy and Add Above, Copy and Add Below, Copy and Add to Top, or Copy and Add to Bottom to add a column with the same properties as the selected column.
• Delete row: Deletes the selected column.

4. Edit as JSON

Select Editor at the upper right of the page to write the whole structure as JSON. The JSON and the table editing screen use the same content, so you can switch between them and edit in the way that is convenient for the work.

The following is a simple user profile example that stores an email address.

{
  "content-type": "application/setfn-schema",
  "dbType": "sqlite3",
  "data": [
    {
      "table": { "name": "user_profile", "schema": "public" },
      "columns": [
        {
          "name": "email",
          "type": "varchar",
          "len": 255,
          "unique": true,
          "notnull": true
        }
      ],
      "label": "User profile"
    }
  ]
}

dbType is information for compatibility with existing files and does not determine the actual runtime engine. The final engine and the connection information are set in the Storage step of API Build.

API permissions are as important as the data

For each API, you can set whether sign-in is required, the minimum level, administrators, teams, and whether to allow only the writer's own data.

• If the list query contains personal information, do not use public permission.
• For per-user data, specify the column that identifies the owner.
• It is safer to start edit and delete with narrower permissions than view.
• Check that all the intended conditions are shown in the permission summary on the screen.

After saving

Save the schema file with the save button at the upper left or with Ctrl+S. You can now check the endpoints and permissions in API Build and deploy to an actual database.

When deployment is finished, register one small record. From the moment the structure you just created comes back as an API response, the schema file is no longer a simple document but a working service.

Good to Know

• Use only lowercase English letters, numbers, and underscores (_) in table names and column names.
• If there are many columns, use JSON editing or SQL import.
• Depending on the administrator policy, overwriting a schema that has existing data may be prohibited or allowed only for administrators and deployment managers.

Next step

Now check the storage engine and the endpoints in Build APIs and deploy a database, and run your first API.

Related Documents

• Build a real-time Gomoku game
• Build APIs and deploy a database
• Import and export SQL
• Checking the Frontend Sample