> For the complete documentation index, see [llms.txt](https://docs.lingoql.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.lingoql.com/sub0/creating-database-models/creating-models.md).

# Creating Models

Sub0 database models define tables or collections with field types, relationships, and indexing.

Use these database model examples as templates in the Sub0 editor.

Create one file per table or collection, such as `_user.json`, then paste the JSON for that model.

### 1. Blog Platform

#### `_post`

```json
{
  // The id property can be omitted when working with MongoDB since an _id is generated automatically.
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "title": {
    "type": "String",
    "fts": true,
    "indexable": true
  },
  "content": {
    "type": "String",
    "fts": true
  },
  "published": {
    "type": "Boolean"
  },
  "created_at": {
    "type": "DateTime"
  },
  "updated_at": {
    "type": "DateTime"
  }
}
```

#### `_comment`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "post_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_post",
      "key": "id"
    }
  },
  "content": {
    "type": "String"
  },
  "created_at": {
    "type": "DateTime"
  }
}
```

### 2. Newsletter Platform

#### `_subscriber`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "email": {
    "type": "String",
    "indexable": true
  },
  "active": {
    "type": "Boolean"
  },
  "created_at": {
    "type": "DateTime"
  }
}
```

#### `_campaign`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "subject": {
    "type": "String"
  },
  "content": {
    "type": "String"
  },
  "sent_at": {
    "type": "DateTime",
    "optional": true
  }
}
```

### 3. Fintech Wallet

#### `_user`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "email": {
    "type": "String",
    "indexable": true
  },
  "verified": {
    "type": "Boolean"
  },
  "created_at": {
    "type": "DateTime"
  }
}
```

#### `_wallet`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "user_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_user",
      "key": "id"
    }
  },
  "balance": {
    "type": "Float"
  },
  "currency": {
    "type": "String"
  }
}
```

***

### 4. E-commerce Store

#### `_product`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "name": {
    "type": "String",
    "fts": true
  },
  "price": {
    "type": "Float"
  },
  "in_stock": {
    "type": "Boolean"
  }
}
```

#### `_order`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "total": {
    "type": "Float"
  },
  "created_at": {
    "type": "DateTime"
  }
}
```

***

### 5. SaaS Application

#### `_account`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "name": {
    "type": "String"
  },
  "plan": {
    "type": "String"
  }
}
```

#### `_member`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "account_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_account",
      "key": "id"
    }
  },
  "role": {
    "type": "String"
  }
}
```

***

### 6. Event Management

#### `_event`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "title": {
    "type": "String"
  },
  "date": {
    "type": "DateTime"
  }
}
```

#### `_ticket`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "event_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_event",
      "key": "id"
    }
  },
  "price": {
    "type": "Float"
  }
}
```

***

### 7. Learning Platform

#### `_course`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "title": {
    "type": "String"
  },
  "published": {
    "type": "Boolean"
  }
}
```

#### `_enrollment`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "course_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_course",
      "key": "id"
    }
  },
  "created_at": {
    "type": "DateTime"
  }
}
```

***

### 8. Chat Application

#### `_room`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "name": {
    "type": "String"
  }
}
```

#### `_message`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "room_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_room",
      "key": "id"
    }
  },
  "content": {
    "type": "String"
  },
  "created_at": {
    "type": "DateTime"
  }
}
```

***

### 9. CRM System

#### `_lead`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "email": {
    "type": "String",
    "indexable": true
  },
  "status": {
    "type": "String"
  }
}
```

#### `_activity`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "lead_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_lead",
      "key": "id"
    }
  },
  "note": {
    "type": "String"
  }
}
```

***

### 10. Issue Tracker

#### `_issue`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "title": {
    "type": "String"
  },
  "status": {
    "type": "String"
  }
}
```

#### `_comment`

```json
{
  "id": {
    "type": "Number",
    "primary_key": true,
    "auto_increment": true
  },
  "issue_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_issue",
      "key": "id"
    }
  },
  "content": {
    "type": "String"
  }
}
```

***

### Advanced example

```json
// "_user" table/collection
{
  "id": {
    "type": "Number", // or "String" if your id is a string
    "auto_increment": true, // required only when type is "Number"
    "primary_key": true
  },
  "name": {
    "type": "String",
    "fts": true,
    "indexable": true
  },
  "email": {
    "type": "String",
    "max_length": 255,
    "fts": true,
    "indexable": true
  },
  "password": {
    "type": "String",
    "max_length": 255
  },
  "age": {
    "type": "Number",
    "optional": true
  },
  "balance": {
    "type": "JSON_OBJECT",
    "optional": true,
    "properties": {
      "currency": {
        "type": "String",
        "max_length": 255
      },
      "amount": {
        "type": "Float"
      }
    }
  },
  "active": {
    "type": "Boolean",
    "optional": true
  },
  "hobbies": {
    "type": "JSON_ARRAY",
    "property_type": "String",
    "optional": true
  },
  "settings": {
    "type": "JSON_OBJECT",
    "optional": true,
    "properties": {
      "notifications_enabled": {
        "type": "Boolean"
      },
      "referral_code": {
        "type": "String",
        "optional": true,
        "max_length": 255
      },
      "preferences": {
        "type": "JSON_ARRAY",
        "property_type": "String"
      }
    }
  },
  "created_at": {
    "type": "DateTime"
  },
  "updated_at": {
    "type": "DateTime"
  },
  "deleted_at": {
    "type": "DateTime",
    "optional": true
  }
}
```

```json
// "_transaction" table/collection
{
  "id": {
    "type": "Number",
    "auto_increment": true,
    "primary_key": true
  },
  "user_id": {
    "type": "Number",
    "foreign_key": {
      "of": "_user",
      "key": "id"
    }
  },
  "active": {
    "type": "Boolean"
  },
  "created_at": {
    "type": "DateTime"
  },
  "updated_at": {
    "type": "DateTime"
  }
}
```

### Why these examples work

All models above are:

* Database-agnostic
* Valid across **MongoDB, MySQL/MariaDB, and PostgreSQL**
* Ready to paste directly into your Sub0 editor

Sub0 keeps database schema design consistent across supported databases.

### Generate models with Sub0 AI

You can generate Sub0 database models in the editor with plain-English prompts.

***

### Sample prompts

#### Single database model prompt

> **Prompt:**\
> Generate a database model for my `user` table with columns:\
> `id`, `name`, `email`, `password`, `hobbies` (a JSON array of strings),\
> `created_at`, `updated_at`, and `deleted_at` timestamps.

***

#### Multiple database model prompt

> **Prompt:**\
> Generate a database model for my `user` table with columns:\
> `id`, `name`, `email`, `password`, `hobbies` (a JSON array of strings),\
> `created_at`, `updated_at`, and `deleted_at` timestamps.
>
> Also create another model for `transaction` with columns:\
> `id`, `user_id` (related to `user`), `transaction_type`,\
> and the same timestamps as the `user` table.

***

#### What Sub0 AI handles

* Infers field types
* Adds relationships and foreign keys
* Produces production-ready database models

Next, see [Table Relationships](/sub0/creating-database-models/table-relationships.md) for `foreign_key` examples.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.lingoql.com/sub0/creating-database-models/creating-models.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.