Collections Model - SnackBase

SnackBase’s Collections are the core abstraction for defining data schemas and generating APIs. This guide explains how collections work, the dynamic table system, and implications for developers.

Overview

In traditional databases, you define tables with schemas. In SnackBase, you define Collections, which:

Store schema metadata in the collections table
Create/update physical tables dynamically
Auto-generate REST API endpoints
Provide validation and type safety
Support relationships between collections
Protect sensitive data with PII masking

What is a Collection?

A Collection is a named data schema with fields, types, and configuration options.

Collection Structure

{
  "id": "col_abc123",
  "name": "posts",
  "description": "Blog posts and articles",
  "account_id": "AB1001",
  "migration_revision": "20250101_create_posts",
  "schema": {
    "fields": [
      { "name": "title", "type": "text", "required": true },
      { "name": "content", "type": "text", "required": false },
      { "name": "status", "type": "text", "default": "draft" },
      { "name": "views", "type": "number", "default": 0 },
      { "name": "published_at", "type": "datetime" },
      { "name": "author_email", "type": "email" },
      { "name": "cover_image", "type": "url" },
      { "name": "metadata", "type": "json" }
    ]
  },
  "created_at": "2025-01-01T00:00:00Z",
  "updated_at": "2025-01-01T00:00:00Z"
}

Components of a Collection

Component	Purpose	Example
name	Unique identifier, becomes table name	`posts` → `col_posts` table
description	Human-readable description	”Blog posts and articles”
schema	Field definitions with types and validation	See field types below
account_id	Owner account (for collection management)	`AB1001`
migration_revision	Tracks which migration created/modified the collection	`20250101_create_posts`

Collection vs Table

Understanding the distinction is critical:

The Confusion

❌ Common Misconception:
"Creating a collection creates a separate table for each account"

✓ Reality:
"Creating a collection creates ONE shared table for ALL accounts"

How It Actually Works

When you create a collection named posts:

Schema Definition: Stored in collections table (metadata)
Table Creation: Physical col_posts table created (if doesn’t exist)
API Generation: /api/v1/records/posts endpoints registered
Usage: All accounts use the same physical table

collections table (metadata):
┌─────────────┬──────────────┬─────────────┬────────────────────────┐
│ id          │ name         │ account_id  │ migration_revision     │
├─────────────┼──────────────┼─────────────┼────────────────────────┤
│ col_abc123  │ posts        │ AB1001      │ 20250101_create_posts  │  ← Who created it
│ col_def456  │ products     │ XY2048      │ 20250102_products      │
└─────────────┴──────────────┴─────────────┴────────────────────────┘

col_posts table (actual data - ONE table for ALL accounts):
┌─────────────┬─────────────┬───────────────┬─────────────┬───────────────┬─────────────┐
│ id          │ title       │ content       │ account_id  │ created_at    │ updated_by  │
├─────────────┼─────────────┼───────────────┼─────────────┼───────────────┼─────────────┤
│ post_001    │ Hello       │ Welcome...    │ AB1001      │ 2025-01-01... │ user_123    │  ← AB1001's data
│ post_002    │ Acme News   │ Latest...     │ AB1001      │ 2025-01-02... │ user_456    │
│ post_003    │ Globex Post │ Update...     │ XY2048      │ 2025-01-03... │ user_789    │  ← XY2048's data
└─────────────┴─────────────┴───────────────┴─────────────┴───────────────┴─────────────┘

Table Naming Convention

Critical: Collection tables are prefixed with col_ to avoid conflicts with system tables.

Collection Name	Physical Table Name
`posts`	`col_posts`
`products`	`col_products`
`users`	`col_users`

Why This Design?

Approach	Pros	Cons	SnackBase
Table per Account	Complete isolation	Thousands of tables, complex migrations	❌
Database per Account	Maximum isolation	Complex operations, resource intensive	❌
Shared Table	Simple, scalable, efficient	Requires account filtering	✅ Chosen

Field Types

Collections support multiple field types with built-in validation.

Available Field Types

Type	Description	Database Type	Example
text	Single-line text	`VARCHAR`	Name, title
number	Numeric value	`NUMERIC`	Price, quantity
boolean	True/false	`BOOLEAN`	is_published
datetime	Date/time with validation	`TIMESTAMP`	published_at, created_at
email	Email with validation	`VARCHAR`	[email protected]
url	URL with validation	`VARCHAR`	https://example.com
json	JSON data	`JSONB`	metadata
reference	Reference to another collection	`VARCHAR` (FK)	user_id
file	File upload reference	`VARCHAR`	avatar_url

Field Configuration

Each field type has specific configuration options:

Text Field

{
  "name": "title",
  "type": "text",
  "required": true,
  "default": null,
  "unique": false
}

Number Field

{
  "name": "price",
  "type": "number",
  "required": true,
  "default": 0,
  "min": 0,
  "max": 1000000
}

DateTime Field

{
  "name": "published_at",
  "type": "datetime",
  "required": false,
  "default": null
}

Email Field

{
  "name": "author_email",
  "type": "email",
  "required": true,
  "unique": true
}

URL Field

{
  "name": "website",
  "type": "url",
  "required": false
}

JSON Field

{
  "name": "metadata",
  "type": "json",
  "required": false,
  "default": "{}"
}

Reference Field

{
  "name": "author_id",
  "type": "reference",
  "target_collection": "users",
  "on_delete": "set_null",
  "required": false
}

File Field

{
  "name": "attachment",
  "type": "file",
  "required": false,
  "max_size": 10485760
}

Dynamic Table Generation

SnackBase dynamically creates and modifies database tables based on collection schemas.

Table Creation Flow

1. User creates collection via UI or API
   POST /api/v1/collections
   {
     "name": "posts",
     "schema": { "fields": [...] }
   }

2. System validates collection name
   - Must be alphanumeric with underscores
   - Cannot conflict with system tables
   - Cannot conflict with existing collections

3. System generates SQL
   CREATE TABLE IF NOT EXISTS col_posts (
     id VARCHAR(50) PRIMARY KEY,
     account_id VARCHAR(10) NOT NULL,
     title VARCHAR NOT NULL,
     content TEXT,
     status VARCHAR,
     views NUMERIC DEFAULT 0,
     published_at TIMESTAMP,
     author_email VARCHAR,
     cover_image VARCHAR,
     metadata JSONB,
     created_at TIMESTAMP DEFAULT NOW(),
     created_by VARCHAR(50),
     updated_at TIMESTAMP DEFAULT NOW(),
     updated_by VARCHAR(50),
     FOREIGN KEY (account_id) REFERENCES accounts(id),
     FOREIGN KEY (created_by) REFERENCES users(id),
     FOREIGN KEY (updated_by) REFERENCES users(id)
   );

4. System executes SQL via SQLAlchemy

5. System registers API routes
   GET    /api/v1/records/posts
   POST   /api/v1/records/posts
   GET    /api/v1/records/posts/:id
   PUT    /api/v1/records/posts/:id
   DELETE /api/v1/records/posts/:id

Built-in Fields

Every collection table includes automatic fields you don’t need to define:

Field	Type	Description	Auto-Managed
`id`	VARCHAR	Unique record ID	✅ Auto-generated
`account_id`	VARCHAR(10)	Account isolation	✅ Automatic
`created_at`	TIMESTAMP	Creation timestamp	✅ Auto-set
`created_by`	VARCHAR(50)	Creator user ID	✅ Auto-set
`updated_at`	TIMESTAMP	Last update timestamp	✅ Auto-updated
`updated_by`	VARCHAR(50)	Last updater user ID	✅ Auto-updated

Indexes and Constraints

SnackBase automatically creates:

-- Primary key
PRIMARY KEY (id)

-- Account isolation index
INDEX idx_col_posts_account_id ON col_posts(account_id);

-- Timestamp indexes
INDEX idx_col_posts_created_at ON col_posts(created_at);
INDEX idx_col_posts_updated_at ON col_posts(updated_at);

-- Unique constraints (if specified)
UNIQUE (title)  -- if field.unique = true

-- Foreign keys
FOREIGN KEY (account_id) REFERENCES accounts(id)
FOREIGN KEY (created_by) REFERENCES users(id)
FOREIGN KEY (updated_by) REFERENCES users(id)

Auto-Generated APIs

Each collection automatically gets a complete REST API.

Generated Endpoints

For a collection named posts:

Method	Endpoint	Description	Permission
GET	`/api/v1/records/posts`	List all records (with filtering)	`posts:read`
POST	`/api/v1/records/posts`	Create a new record	`posts:create`
GET	`/api/v1/records/posts/:id`	Get single record	`posts:read`
PATCH	`/api/v1/records/posts/:id`	Partial update record	`posts:update`
PUT	`/api/v1/records/posts/:id`	Full update record	`posts:update`
DELETE	`/api/v1/records/posts/:id`	Delete record	`posts:delete`
POST	`/api/v1/records/posts/bulk`	Bulk operations	`posts:create`/`update`/`delete`

API Usage Examples

# Create a record
POST /api/v1/records/posts
Authorization: Bearer <token>
Content-Type: application/json

{
  "title": "My First Post",
  "content": "This is the content",
  "status": "published",
  "views": 0,
  "author_email": "[email protected]",
  "cover_image": "https://example.com/image.jpg",
  "metadata": {
    "seo_title": "SEO Title",
    "tags": ["tag1", "tag2"]
  }
}

# Response
{
  "id": "post_abc123",
  "title": "My First Post",
  "content": "This is the content",
  "status": "published",
  "views": 0,
  "author_email": "[email protected]",
  "cover_image": "https://example.com/image.jpg",
  "metadata": {
    "seo_title": "SEO Title",
    "tags": ["tag1", "tag2"]
  },
  "account_id": "AB1001",
  "created_at": "2025-01-01T00:00:00Z",
  "created_by": "user_xyz789",
  "updated_at": "2025-01-01T00:00:00Z",
  "updated_by": "user_xyz789"
}

Query Filtering

List endpoints support powerful filtering:

# Basic filtering
GET /api/v1/records/posts?status=published

# Multiple filters
GET /api/v1/records/posts?status=published&views.gt=100

# Sorting
GET /api/v1/records/posts?sort=-created_at

# Pagination
GET /api/v1/records/posts?page=1&limit=20

# Full text search (if configured)
GET /api/v1/records/posts?q=hello

# Date range filtering
GET /api/v1/records/posts?created_at.gte=2025-01-01T00:00:00Z

PII Masking

SnackBase provides automatic PII (Personally Identifiable Information) masking to protect sensitive user data.

How PII Masking Works

PII fields are automatically masked for users who don’t have the pii_access group membership:

// User without pii_access sees:
{
  "id": "user_123",
  "email": "j***@example.com",      // Masked
  "phone": "***-***-1234",          // Masked
  "ssn": "***-**-****",             // Masked
  "name": "John D."                 // Masked
}

// User with pii_access sees:
{
  "id": "user_123",
  "email": "[email protected]",  // Full value
  "phone": "555-123-4567",          // Full value
  "ssn": "123-45-6789",             // Full value
  "name": "John Doe"                // Full value
}

PII Mask Types

Mask Type	Description	Example
email	Shows first character and domain	`j***@example.com`
ssn	Shows only format	`*--****`
phone	Shows last 4 digits	`*-*-1234`
name	Shows first name initial and last initial	`John D.`
full	Completely hides value	`******`
custom	Custom mask pattern	Configurable

Configuring PII Fields

To enable PII masking on a field, use the pii_mask configuration:

{
  "fields": [
    {
      "name": "email",
      "type": "email",
      "pii_mask": {
        "enabled": true,
        "mask_type": "email"
      }
    },
    {
      "name": "ssn",
      "type": "text",
      "pii_mask": {
        "enabled": true,
        "mask_type": "ssn"
      }
    },
    {
      "name": "phone",
      "type": "text",
      "pii_mask": {
        "enabled": true,
        "mask_type": "phone"
      }
    }
  ]
}

Reference Fields

Reference fields allow you to create relationships between collections using foreign keys.

Reference Field Configuration

{
  "name": "author_id",
  "type": "reference",
  "target_collection": "users",
  "on_delete": "set_null",
  "required": false
}

on_delete Actions

Action	Description	Use Case
cascade	Delete referenced record when target is deleted	Dependent data (order items → orders)
set_null	Set field to NULL when target is deleted	Optional relationships
restrict	Prevent deletion if referenced	Critical references (users → orders)

Reference Field Example

// Posts collection with author reference
{
  "name": "posts",
  "schema": {
    "fields": [
      { "name": "title", "type": "text" },
      {
        "name": "author_id",
        "type": "reference",
        "target_collection": "users",
        "on_delete": "set_null",
        "required": false
      }
    ]
  }
}

This creates a foreign key constraint:

ALTER TABLE col_posts
ADD CONSTRAINT fk_posts_author
FOREIGN KEY (author_id)
REFERENCES col_users(id)
ON DELETE SET NULL;

Reference Validation

When creating or updating records with reference fields:

# Valid reference
POST /api/v1/records/posts
{
  "title": "My Post",
  "author_id": "user_abc123"  # ✅ Exists in users collection
}

# Invalid reference
POST /api/v1/records/posts
{
  "title": "My Post",
  "author_id": "user_xyz999"  # ❌ Does not exist - 400 Bad Request
}

Schema Evolution

Collections can evolve over time with schema updates.

Supported Changes

Change	Supported	Notes
Add new field	✅ Yes	New field added to table
Remove field	❌ No	Field deletion is NOT allowed
Rename field	❌ No	Must create new field instead
Change type	❌ No	Type changes are NOT allowed
Modify field options	✅ Yes	Adding options is supported

Schema Update Flow

1. User updates collection schema
   PUT /api/v1/collections/:id
   {
     "schema": { "fields": [...] }
   }

2. System validates changes
   - Field names are unique
   - Types are valid
   - No field deletions
   - No type changes

3. System generates ALTER TABLE statements
   ALTER TABLE col_posts ADD COLUMN category TEXT;

4. System executes SQL

5. Updated schema applies immediately
   - New API validation
   - Updated forms in UI

Schema Evolution Rules

Field Addition (Only supported operation):

// Before: Collection with 3 fields
{
  "name": "posts",
  "schema": {
    "fields": [
      { "name": "title", "type": "text" },
      { "name": "content", "type": "text" },
      { "name": "status", "type": "text" }
    ]
  }
}

// After: Add new field "category"
{
  "name": "posts",
  "schema": {
    "fields": [
      { "name": "title", "type": "text" },
      { "name": "content", "type": "text" },
      { "name": "status", "type": "text" },
      { "name": "category", "type": "text" }  // ✅ New field added
    ]
  }
}

Field Deletion (NOT allowed):

// Attempting to remove "status" field
{
  "name": "posts",
  "schema": {
    "fields": [
      { "name": "title", "type": "text" },
      { "name": "content", "type": "text" }
      // "status" removed - ❌ NOT ALLOWED
    ]
  }
}

// System response: 400 Bad Request
{
  "error": "Cannot remove fields from schema",
  "detail": "Field removal is not supported. Fields can only be added."
}

Type Changes (NOT allowed):

// Attempting to change field type
{
  "fields": [
    { "name": "views", "type": "text" }  // ❌ Was "number", now "text"
  ]
}

// System response: 400 Bad Request
{
  "error": "Cannot change field types",
  "detail": "Type changes are not supported. Create a new field instead."
}

Best Practices

1. Naming Conventions

Use lowercase, plural names for collections:

Good	Bad
`posts`	`Posts`
`users`	`user`
`blog_posts`	`BlogPosts`
`order_items`	`order-items`

2. Field Naming

Use snake_case for field names:

{
  "fields": [
    { "name": "first_name", "type": "text" },   // ✅ Good
    { "name": "lastName", "type": "text" },     // ❌ Bad
    { "name": "Email-Address", "type": "email" } // ❌ Bad
  ]
}

3. Use Appropriate Field Types

Choose the most specific type for your data:

{
  "fields": [
    { "name": "email", "type": "email" },      // ✅ Specific type
    { "name": "published_at", "type": "datetime" },  // ✅ With validation
    { "name": "website", "type": "url" },      // ✅ URL validation
    { "name": "metadata", "type": "json" }     // ✅ Flexible data
  ]
}

4. Use JSON for Flexible Data

For metadata or varying structures:

{
  "name": "metadata",
  "type": "json"
}

Store arbitrary data:

{
  "metadata": {
    "seo_title": "SEO Title",
    "seo_description": "Description",
    "tags": ["tag1", "tag2"],
    "custom_field": "any value"
  }
}

5. Plan Schema Evolution

Design schemas with evolution in mind:

Only add new fields (deletion and type changes are not allowed)
Use required: false for fields that might be optional later
Document schema changes in migration revisions
Test schema updates in development first

6. Use PII Masking for Sensitive Data

Protect user privacy with automatic PII masking:

{
  "name": "email",
  "type": "email",
  "pii_mask": {
    "enabled": true,
    "mask_type": "email"
  }
}

This ensures compliance with privacy regulations by default.

7. Choose Appropriate on_delete Actions

Select the right action for reference fields:

Scenario	Action
Optional relationships (posts → authors)	`set_null`
Dependent data (order items → orders)	`cascade`
Critical references (prevent deletion)	`restrict`

8. Avoid Too Many Collections

Each collection creates a table. Consider:

Can related data be in the same collection?
Would JSON fields work better for varying schemas?
Do you really need separate tables?

Example: Instead of blog_posts and news_posts, use posts with a category field.

Aggregation Queries

Collections support server-side aggregation via the /{collection}/aggregate endpoint:

Function	Description
`count`	Count matching records
`sum`	Sum a numeric field
`avg`	Average of a numeric field
`min`	Minimum value
`max`	Maximum value

Aggregations support filter, group_by, and having clauses for powerful analytics without fetching raw records. See the API Reference for full details.

Public Collections

Collections can be configured for anonymous read access, allowing unauthenticated users to query records. This is useful for public-facing data like product listings, blog posts, or FAQ content.

Summary

Concept	Key Takeaway
Collection Definition	Named schema with fields, stored in `collections` table
Collection vs Table	Collections are metadata; ONE shared table per collection for ALL accounts
Table Naming	Tables prefixed with `col_` (e.g., `col_posts`)
Field Types	9 types available (text, number, boolean, datetime, email, url, json, reference, file)
Dynamic Tables	Tables created/modified automatically based on schema
Auto-Generated APIs	REST endpoints at `/api/v1/records/{collection}`
PII Masking	Automatic masking for sensitive data (email, ssn, phone, name)
Reference Fields	Foreign key relationships with on_delete actions
Schema Evolution	Only adding fields is allowed; deletion and type changes are NOT supported
Migration Tracking	`migration_revision` field tracks schema changes
Best Practices	Use lowercase plurals, snake_case fields, plan for evolution

​Overview

​What is a Collection?

​Collection Structure

​Components of a Collection

​Collection vs Table

​The Confusion

​How It Actually Works

​Table Naming Convention

​Why This Design?

​Field Types

​Available Field Types

​Field Configuration

​Text Field

​Number Field

​DateTime Field

​Email Field

​URL Field

​JSON Field

​Reference Field

​File Field

​Dynamic Table Generation

​Table Creation Flow

​Built-in Fields

​Indexes and Constraints

​Auto-Generated APIs

​Generated Endpoints

​API Usage Examples

​Query Filtering

​PII Masking

​How PII Masking Works

​PII Mask Types

​Configuring PII Fields

​Reference Fields

​Reference Field Configuration

​on_delete Actions

​Reference Field Example

​Reference Validation

​Schema Evolution

​Supported Changes

​Schema Update Flow

​Schema Evolution Rules

​Best Practices

​1. Naming Conventions

​2. Field Naming

​3. Use Appropriate Field Types

​4. Use JSON for Flexible Data

​5. Plan Schema Evolution

​6. Use PII Masking for Sensitive Data

​7. Choose Appropriate on_delete Actions

​8. Avoid Too Many Collections

​Aggregation Queries

​Public Collections

​Summary