# SchemaStack Documentation — full text Every page of https://docs.schemastack.io concatenated in reading order. Generated at build time; the per-page equivalents and an index live at https://docs.schemastack.io/llms.txt. --- # Getting Started Source: https://docs.schemastack.io/guide/getting-started This guide walks you through your first steps with SchemaStack. **Tip: Want to skip straight to playing with the product?** You don't need a database to start. Sign up, click **Create Workspace → Start with sample data**, and you'll be editing a populated **Acme Store** dataset (categories, products, customers, orders) in under a minute. You can always connect your own database later — workspaces are independent. ## 1. Create an Account 1. Navigate to your SchemaStack instance and click **Register** 2. Enter your name, email address, and password 3. Click **Create Account** ## 2. Verify Your Email After registering, you'll receive a verification email. Click the link in the email to activate your account. If you don't receive it, click **Resend Verification Email** on the sign-in page. ## 3. Accept an Invitation If you've been invited to an organization, click the link in the invitation email. If you already have an account, sign in to accept. If not, you'll be guided through registration first. ## 4. Forgot Password If you've forgotten your password: 1. Click **Forgot Password** on the sign-in page 2. Enter your email address 3. Click the reset link in the email you receive 4. Choose a new password ## 5. Sign In Navigate to your SchemaStack instance and sign in with your credentials. If you're new, ask your organization admin for an invitation. ## 6. Select an Organization After signing in, you'll be prompted to select an organization. If you belong to multiple organizations, choose the one you want to work in. ## 7. Open a Workspace Workspaces are where your data lives. Each workspace contains one or more **views** — different ways to look at the same underlying data. From the admin console, navigate to a workspace. From the data platform, you'll see your workspace's views directly. ## 8. Explore a View A view is a configured window into your data. Each view has: - **Columns** — The fields in your data, each with a defined type - **Rows** — Your actual data records - **Filters** — Narrow down what you see - **Sort rules** — Control the order - **Constraints** — Validation rules that ensure data quality ## 9. Edit Data Click on any cell to edit its value. Changes are saved automatically and broadcast to other users in real time. ## 10. Save a Preset Once you've configured filters and sorting to your liking, save them as a **preset** so you can return to that exact view later — or share it with your team. ## Next Steps - [Key Concepts](https://docs.schemastack.io/guide/key-concepts.md) — Understand the building blocks - [Views](https://docs.schemastack.io/app/views.md) — Deep dive into views - [Constraints](https://docs.schemastack.io/app/constraints.md) — Learn about data validation - [Members & Roles](https://docs.schemastack.io/admin/members.md) — Manage your team --- # What is SchemaStack? Source: https://docs.schemastack.io/guide/what-is-schemastack SchemaStack is a data platform where **using the spreadsheet builds the database**: adding a column in the UI adds a real column, linking two tables creates a real foreign key, and every structural change is previewed as a migration before it runs — in a Postgres or MySQL database you own, whether you start empty or import the schema you already run. We only store the metadata; your data lives in your own database. Humans work in it through a spreadsheet-style interface; applications through a generated REST API; AI agents through a governed [MCP connection](https://docs.schemastack.io/guide/ai-integration.md). All three see the same schema, the same permissions, the same data. ## The Problem Teams working with structured data often face a choice: use a spreadsheet (flexible but chaotic) or build a custom app (reliable but expensive) — and increasingly, a third bad option: hand the data to a SaaS vendor who hosts it for you. SchemaStack takes a different path: we manage the platform, you keep your database. ## The Solution SchemaStack gives you: - **A schema layer** — Define your entities, columns, types, and constraints. Your data structure is explicit, versioned, and enforced. - **A data layer** — View, edit, and manage your data through a familiar spreadsheet-like interface with real-time collaboration. - **A validation layer** — Column constraints (min/max, pattern, email, etc.) and entity constraints (cross-field rules like "start date before end date") catch problems before they enter your system. - **An API layer** — Every workspace automatically gets a full REST API with CRUD operations, filtering, sorting, pagination, and OpenAPI/Swagger documentation. No code generation — the API is live as soon as your schema is defined. - **An admin layer** — Manage organizations, workspaces, members, and roles with fine-grained permissions. ## Who is it for? SchemaStack serves four kinds of users: - **Developers building collaborative tools for clients.** You know ORM principles — entities, relationships, constraints. You've been asked to ship a collaborative data tool for a client, and building from scratch would take months. SchemaStack gives you and your client a spreadsheet UI, guest access for non-technical end users, and an instant REST API — with the client's data staying in their own database. - **Teams escaping Airtable because they hit the relationship wall.** You need real foreign keys, many-to-many relationships, and cross-field validation — the things linked records can't do. - **Administrative and operations teams** managing structured data like product catalogs, inventory, vendor lists, research datasets, or configuration — who need a spreadsheet UX and whose IT won't approve handing data to another SaaS. - **Developers building a headless CMS** who want structured content with a REST API, without locking into a hosted CMS cloud. All four share the same core need: **managed software, self-owned data.** ## Architecture SchemaStack consists of three layers: - **Data Platform** — The main interface where you work with your data: views, columns, constraints, filters, presets, and real-time collaboration. - **Admin Console** — Organization and workspace management: teams, members, roles, and settings. - **Workspace API** — An auto-generated REST API for every workspace. Define your entities in the Admin Console or Data Platform, and a full CRUD API with Swagger documentation is instantly available — no code to write or deploy. The Data Platform and Admin Console are web applications. The Workspace API is accessed programmatically and can be integrated with external services like Zapier, Jotform, or your own applications. ## How it compares Evaluating SchemaStack against something you already use? The website keeps honest, feature-by-feature comparisons: [Airtable](https://schemastack.io/vs-airtable), [Directus](https://schemastack.io/vs-directus), [NocoDB](https://schemastack.io/vs-nocodb), [Baserow](https://schemastack.io/vs-baserow), [Retool](https://schemastack.io/vs-retool) — and [how it pairs with Supabase](https://schemastack.io/with-supabase). --- # Key Concepts Source: https://docs.schemastack.io/guide/key-concepts Understanding these core concepts will help you get the most out of SchemaStack. ## Organization The top-level container. An organization has members, settings, and one or more workspaces. Think of it as your company or team account. ## Workspace A workspace holds your data and its schema. Each workspace can have multiple views, and members can be assigned workspace-specific roles. **Workspace statuses:** - **Active** — Normal operation, all features available - **Read-Only** — Data can be viewed but not modified - **Design** — Schema changes allowed, data writes restricted - **Maintenance** — Temporarily unavailable for modifications ## View A view is a configured perspective on your workspace data. Each view defines which columns are visible, their order, and default filters or sorting. Views can be: - **Addable** — Users can add new rows - **Editable** — Users can modify existing data - **Read-only** — View data without modification ## Column A column defines a single field in your data. Each column has: - **Name** — The display label - **Type** — The data type (text, number, date, boolean, etc.) - **Widget** — How the data is displayed and edited (text input, dropdown, date picker, etc.) - **Widget Options** — Per-widget settings (e.g., DATETIME columns can auto-set timestamps on insert or update) - **Constraints** — Validation rules specific to this column ## Constraint Constraints enforce data quality rules. There are two levels: ### Column Constraints Rules applied to a single column's values: - **Not Blank** — Value cannot be empty - **Min / Max** — Numeric bounds - **Min Length / Max Length** — Text length bounds - **Pattern** — Must match a regular expression - **Email / URL** — Format validation - **Positive / Negative** — Sign constraints - **Past / Future** — Date constraints ### Entity Constraints Rules that span multiple columns: - **Unique Composite** — Combination of fields must be unique - **Field Comparisons** — One field must be less than, greater than, or not equal to another - **Conditional Required** — A field is required when another field has a value - **At Least One Required** — At least one of several fields must have a value - **All or None** — Either all selected fields have values, or none do ## Preset A saved configuration of filters, sorting, and column visibility. Presets let you quickly switch between different views of the same data, and share those configurations with your team. ## Role Permissions are managed through roles at two levels: ### Organization Roles - **Owner** — Full control over the organization - **Admin** — Manage members, workspaces, and settings - **Member** — Access workspaces they're assigned to ### Workspace Roles - **Admin** — Full control over the workspace - **Editor** — Read and write data - **Viewer** — Read-only access - **Member** — Basic access --- # Data Ownership Source: https://docs.schemastack.io/guide/data-ownership **Your Data, Always — No Vendor Lock-In.** SchemaStack is designed so your data never leaves your infrastructure. Unlike platforms that host your data in their cloud, SchemaStack **connects to your existing database** and only stores metadata. ## What We Store vs. What You Keep ### SchemaStack stores metadata only - Entity definitions (table names, column types, relationships) - View configurations (column order, filters, presets) - Permissions and role assignments - User accounts and workspace settings ### Your data stays in your database - All your business data (products, orders, customers, etc.) - Database schema (tables, columns, indexes) - Full infrastructure control - Your existing backups and compliance setup ``` SchemaStack Infrastructure (metadata only) ├── Entity definitions ├── View configurations ├── Permissions └── No customer data │ │ API connection (read/write) ▼ Your Infrastructure (your data) ├── AWS RDS, self-hosted, or any provider ├── All your business data └── Full control, always ``` ## What Happens If You Leave 1. **Your data** — already in your database. Nothing to export or migrate. 2. **Metadata** — can be exported as JSON/YAML (entity definitions, view configs). 3. **Disconnect** — remove the database connection and you're done. There's no data migration, no export fees, no waiting period. Your data was never in our systems to begin with. ## Compliance Because customer data never enters SchemaStack's infrastructure: - **GDPR** — easier to demonstrate data control. Right to deletion is straightforward — your data is already in your hands. - **Data residency** — your data stays in whatever region and provider you choose. No cross-border transfer by SchemaStack. - **Audit** — clear separation between metadata (ours) and data (yours) simplifies compliance audits. ## How This Compares | | Traditional SaaS | SchemaStack | | -------------------- | ------------------------------ | ----------------------------------------------------------------------------- | | Where is your data? | Vendor's cloud | Your database | | What if you leave? | Export required, may take days | Disconnect, data already yours | | Data residency | Vendor controls | You control | | Lock-in risk | High | None | | Infrastructure costs | Included (but data is hostage) | Your existing infra (or [managed hosting](https://docs.schemastack.io/roadmap.md#managed-database-hosting)) | **Tip** SchemaStack also offers [Managed RDS and Enterprise ZFS hosting](https://docs.schemastack.io/roadmap.md#managed-database-hosting) for teams who want SchemaStack to handle the database infrastructure. Even with managed hosting, the database is dedicated to your organization and can be migrated out at any time. --- # File Path Templates Source: https://docs.schemastack.io/guide/file-path-templates File path templates control how files are organized in your S3 bucket. A single template determines the S3 key structure — used for both saving and reading files. ## Where to Configure | Level | Where | Applies to | |-------|-------|------------| | **Workspace** | Admin > Workspace Settings > Storage | All files in the workspace (default) | | **View** | App > View Properties | All files in that view | | **Column** | App > Column Properties | Files in that specific column | ## Resolution Hierarchy The most specific template wins: 1. **Column-level template** (if set) 2. **View-level template** (if set) 3. **Workspace-level template** (if set) 4. **Default** — `files/${fileId}.${ext}` ## Available Variables | Variable | Description | Example | |----------|-------------|---------| | `${fileId}` | Generated file UUID | `f9e8d7c6-1234-5678-abcd-ef0123456789` | | `${fileName}` | Original file name | `image.png` | | `${baseName}` | File name without extension | `image` | | `${ext}` | File extension (without dot) | `png` | | `${workspace.uuid}` | Workspace UUID | `a1b2c3d4-...` | | `${workspace.slug}` | Workspace slug | `acme-corp` | | `${view.uuid}` | View UUID | `e5f6a7b8-...` | | `${view.slug}` | View slug | `clients` | | `${column.uuid}` | Column UUID | `c9d0e1f2-...` | | `${column.name}` | Column field name | `avatar` | | `${value}` | The current cell's raw value | `invoice.pdf` | | `${row.column_name}` | Value from another column in the same row | `42` | | `${row.column_name:fallback}` | Column value with a default if null | `us-east` | Row variables (`${row.*}` and `${value}`) resolve from the row data when reading files. At upload time, `${fileId}`, `${fileName}`, `${baseName}`, `${ext}`, and the workspace/view/column variables are available. ## Examples ``` ${view.slug}/${column.name}/${fileId}.${ext} → clients/avatar/f9e8d7c6-1234-5678-abcd-ef0123456789.png uploads/${workspace.slug}/${fileName} → uploads/acme-corp/image.png clients/${row.client_id}/${value} → clients/42/headshot.jpg invoices/${row.region:default}/${row.invoice_number}.pdf → invoices/eu-west/INV-2026-001.pdf → invoices/default/INV-2026-001.pdf (when region is null) ``` ## Tips - Include `${fileId}` to guarantee unique file names and avoid overwrites. - Use `${view.slug}` and `${column.name}` to organize files by view and column. - Use `${row.column_name}` to build paths from your existing data — useful when your S3 bucket already has a folder structure based on record values. - The `${row.column_name:fallback}` syntax provides a default value when a column is null, preventing broken paths. - Security: path traversal (`..`) and absolute paths (`/`) are automatically blocked. --- # Formula Columns Source: https://docs.schemastack.io/guide/formula-columns Formula columns are virtual, computed columns that use SQL expressions to derive values from other columns. They work like spreadsheet formulas -- the database evaluates the expression at query time and returns the result alongside regular column values. ## Key Characteristics - **No physical database column** -- nothing is created or altered in your PostgreSQL database - **Always read-only** -- values are calculated, never entered manually - **Evaluated by the database** -- PostgreSQL computes the result, so expressions are fast and accurate - **Visible in the API** -- formula columns appear in query responses just like regular columns - **Per-view** -- different views of the same table can have different formula columns - **No migrations needed** -- adding, updating, or removing a formula column never triggers a schema migration ## Simple Formulas A formula references column names from the same table using standard SQL expression syntax. ### Arithmetic ``` price * quantity unit_price * quantity * (1 - discount) (subtotal + tax) - credit ``` ### String Functions ``` CONCAT(first_name, ' ', last_name) UPPER(country_code) TRIM(description) ``` ### Null Handling ``` COALESCE(nickname, first_name, 'Anonymous') COALESCE(phone, 'Not provided') ``` ### Rounding and Math ``` ROUND(price * 1.15, 2) ABS(balance) CEIL(score / 10) ``` ### Conditional Logic ``` CASE WHEN is_active = true THEN 'Active' ELSE 'Inactive' END CASE WHEN quantity > 100 THEN price * 0.9 ELSE price END ``` ## Cross-Entity Formulas Formula columns can reference columns from related entities using `relationship.column` syntax. The relationship name must match a relationship defined on the entity. ``` customer.discount * price COALESCE(customer.name, 'Unknown') CONCAT(customer.company, ' - ', customer.city) ``` For example, if your `orders` table has a relationship named `customer` pointing to a `customers` table, you can reference `customer.discount` to pull the discount value from the related customer record. **Tip** Cross-entity formulas use Hibernate relationship navigation (JOINs), which is more efficient than subqueries. Define a [relationship](https://docs.schemastack.io/app/relationships.md) on your entity first, then reference it in the formula. ## Available SQL Functions Formulas support standard SQL functions that work in PostgreSQL: | Category | Functions | |----------|-----------| | **String** | `CONCAT`, `UPPER`, `LOWER`, `TRIM`, `LENGTH`, `SUBSTRING`, `REPLACE` | | **Numeric** | `ABS`, `ROUND`, `CEIL`, `FLOOR`, `MOD`, `SQRT`, `POWER`, `GREATEST`, `LEAST` | | **Null handling** | `COALESCE`, `NULLIF` | | **Conditional** | `CASE WHEN ... THEN ... ELSE ... END` | | **Type conversion** | `CAST(column AS type)` | | **Date** | `CURRENT_DATE`, `CURRENT_TIME`, `CURRENT_TIMESTAMP`, `EXTRACT(YEAR FROM ...)` | ## Creating a Formula Column Use the standard column creation endpoint. The presence of a `formula` field makes it a computed column: ```http POST /api/columns Content-Type: application/json { "viewUuid": "your-view-uuid", "displayName": "Line Total", "formula": "price * quantity", "widgetType": "DECIMAL" } ``` No special endpoint is needed -- the same `POST /api/columns` endpoint handles regular columns, relationship columns, and formula columns. ## Updating a Formula ```http PATCH /api/columns/item/{columnUuid} Content-Type: application/json { "formula": "price * quantity * (1 - discount)", "displayName": "Net Total" } ``` Changes take effect on the next query. No migration is triggered. ## Deleting a Formula Column ```http DELETE /api/columns/item/{columnUuid} ``` Removes the formula column from the view. No database changes are made. ## Security Formula expressions are validated before saving. The following are blocked to prevent SQL injection and data exfiltration: **Blocked SQL statements:** `SELECT`, `FROM`, `WHERE`, `INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `CREATE`, `TRUNCATE`, `GRANT`, `REVOKE`, `UNION`, `EXEC`, `EXECUTE` **Blocked syntax:** - Line comments (`--`) - Block comments (`/* */`) - Statement separators (`;`) Only column references, arithmetic operators (`+`, `-`, `*`, `/`), and the whitelisted SQL functions listed above are permitted. **Warning** Formulas are case-insensitive for security validation. `select`, `SELECT`, and `Select` are all blocked. ## Limitations ### Read-Only Formula column values cannot be edited. They are always calculated from the expression. ### No Aggregate Functions Formulas operate on a single row. Aggregate functions like `SUM`, `COUNT`, and `AVG` across multiple rows are not supported in formula expressions. Each formula evaluates independently per row. ### Valid SQL Required The formula is embedded as a SQL expression in the database query. If the expression references a column that does not exist or contains a syntax error, queries to the view will return an error. Test formulas with simple expressions first, then build up complexity. ### Filtering and Sorting Formula columns support filtering and sorting through the API: ``` GET /api/data/{viewSlug}?filter[lineTotal.gte]=100&sort=lineTotal,desc ``` **Note: Performance** Filtering and sorting on formula columns may be slower than on indexed physical columns because the database evaluates the expression for every row. For frequently filtered values, consider whether a physical column with a database-level default or trigger would be more appropriate. ## Schema Sync Behavior Formula columns are preserved during schema sync and import operations: - They are not deleted during re-sync (there is no database column to compare against) - They are excluded from drift detection - They are excluded from the schema hash, so adding or removing them does not trigger a false drift warning ## Cross-Entity References A formula can reference a directly related record's fields with dot notation: ```sql COALESCE(category.name, 'no category') price * customer.discount CASE WHEN category.name LIKE 'Repeat%' THEN 'R' ELSE 'F' END ``` The related record is joined in server-side. Boundaries: **one relationship hop**, and only to-one relationships (a collection would multiply rows — use an [aggregate column](https://docs.schemastack.io/app/columns.md#aggregate-columns) for those). ## String Literals Anything inside single quotes is treated as data — including words that look like SQL (`'set menu'`, `'price with surcharge'`). Escape a quote by doubling it (`'it''s'`). Unterminated quotes are rejected with a clear error. Every formula is additionally validated against your own database before saving, so vendor-specific functions (`DATE_FORMAT`, `LPAD`, …) are checked for real. --- # Connecting Your Database Source: https://docs.schemastack.io/guide/connecting-your-database SchemaStack writes to a PostgreSQL or MySQL database that you own. You connect it once, either when creating a workspace or later from **Workspace → Database**. The fastest way is to paste the connection string your database provider gave you. ## Paste a connection string Every managed database provider hands you a string that already contains the host, port, database name and credentials. Paste it into the **Paste a connection string** field and SchemaStack fills in the fields below it. ``` postgresql://alice:s3cret@db.example.com:5432/appdb?sslmode=require ``` That single line fills in the database type, host, port, database, username, password and SSL mode. Anything the string doesn't carry is left alone for you to complete. **Tip: Check before you save** The parsed values appear in the normal fields, so you can see exactly what was understood and correct it. Nothing is sent anywhere until you press Test connection or Save. ## Accepted formats Three shapes are recognised, because providers disagree about which to hand out. ### Connection URI The most common form, used by most managed Postgres and MySQL providers: ``` postgresql://user:password@host:5432/database?sslmode=require postgres://user:password@host/database mysql://user:password@host:3306/database ``` `postgres://` and `postgresql://` are equivalent. If the port is missing, SchemaStack uses the default for the vendor — `5432` for PostgreSQL, `3306` for MySQL. Characters that are special in a URL must be percent-encoded, which providers normally do for you. A password of `p@ss/word` appears in the string as `p%40ss%2Fword`, and SchemaStack decodes it back. ### JDBC URL A JDBC URL is the same thing with a `jdbc:` prefix, and often carries credentials as query parameters instead of in front of the host. Both are handled: ``` jdbc:postgresql://db.example.com:5432/appdb?user=alice&password=s3cret ``` ### Keyword/value form The `libpq` style that `psql` documents and some providers print: ``` host=db.example.com port=5432 dbname=appdb user=alice password=s3cret sslmode=require ``` Quoted values are supported, so a password containing spaces can be written `password='a b c'`. ## What gets filled in | Field | Comes from | |-------|-----------| | Database Vendor | The scheme — `postgresql`/`postgres` or `mysql` | | Host | The host portion, or `host=` | | Port | The port, or the vendor default when absent | | Database | The path after the host, or `dbname=` / `database=` | | Username | Before the `:` in the credentials, or `user=` | | Password | After the `:` in the credentials, or `password=` | | SSL Mode | `sslmode=`, or MySQL's `useSSL=` / `requireSSL=` | | Additional Parameters | Every other query parameter, preserved as-is | Parameters SchemaStack doesn't recognise are never discarded — they are collected into **Additional Parameters** so driver-specific options like `application_name` survive. ## SSL modes `sslmode` in the connection string maps onto SchemaStack's SSL setting: | In the string | In SchemaStack | Meaning | |---------------|----------------|---------| | `disable` | Disable | No encryption | | `allow`, `prefer` | Prefer | Encrypt when the server supports it | | `require` | Require | Encryption required, certificate not verified | | `verify-ca` | Verify CA | Certificate must be signed by a trusted CA | | `verify-full` | Verify Full | As above, and the hostname must match | Most managed providers require SSL, so their strings usually end with `?sslmode=require`. **Verify CA** and **Verify Full** need a CA certificate, which no connection string carries — paste the PEM into the certificate field after connecting. The same applies to client certificates, client keys and the connection timeout: those are never read from a connection string. ## Where to find your connection string | Provider | Where to look | |----------|---------------| | Neon | Project dashboard → Connection Details | | Supabase | Project Settings → Database → Connection string | | Railway | The Postgres service → Connect | | Render | Database page → External Connection String | | Amazon RDS | Build it from the endpoint, port and database name shown in the console | | Self-hosted | Whatever you already put in your application's config | If your provider offers both a *pooled* and a *direct* connection string, prefer the direct one. Connection poolers sometimes restrict the statements SchemaStack needs to inspect the schema and run migrations. ## Credentials are encrypted The username and password are encrypted before being stored, and the password is never returned by the API once saved — editing the connection later shows every other field but requires re-entering the password only if you want to change it. ## No database yet? You don't need one to start. Creating your first workspace offers a **managed database** option: SchemaStack provisions a PostgreSQL database preloaded with sample data, so you can look around before connecting anything of your own. See [Getting Started](https://docs.schemastack.io/guide/getting-started.md). ## Troubleshooting **"That doesn't look like a connection string."** The text didn't match any of the three formats above. The most common cause is pasting a dashboard URL rather than the connection string, or a fragment that's missing the scheme. Fill the fields in by hand instead — the paste field is only a shortcut. **The port is wrong after pasting.** A string without an explicit port gets the vendor default. If your provider listens elsewhere, correct the port field after pasting. **Connection test fails with an SSL error.** Managed providers usually require SSL. Set SSL Mode to Require, or to Verify Full with the provider's CA certificate if you want the hostname checked. **Connection test times out.** The database must be reachable from SchemaStack's servers. Databases behind a private network or a firewall that only allows specific source addresses need that allowance adding first. See also: [Database Compatibility](https://docs.schemastack.io/guide/database-compatibility.md) for the feature and type support matrix. --- # Database Compatibility Source: https://docs.schemastack.io/guide/database-compatibility SchemaStack connects to your existing PostgreSQL or MySQL database and imports your schema automatically. This page documents which database features are fully supported, partially supported, or not yet supported. ## Supported Databases | Database | Versions | Status | |----------|----------|--------| | PostgreSQL | 12+ | Fully supported | | MySQL | 8.0+ | Fully supported | ## Fully Supported Features These features are automatically detected during schema sync and work end-to-end in the data platform: | Feature | PostgreSQL | MySQL | Notes | |---------|:----------:|:-----:|-------| | Tables | ✅ | ✅ | All table types | | Columns (all common types) | ✅ | ✅ | VARCHAR, TEXT, INTEGER, BIGINT, NUMERIC, BOOLEAN, DATE, TIMESTAMP, UUID, etc. | | Primary keys | ✅ | ✅ | Single and composite | | Foreign keys | ✅ | ✅ | Including cascade rules (CASCADE, SET NULL, RESTRICT, SET DEFAULT) | | Indexes | ✅ | ✅ | Single-column, composite, unique | | Auto-increment / SERIAL | ✅ | ✅ | Detected as auto-generated | | NULL / NOT NULL | ✅ | ✅ | | | UNIQUE constraints | ✅ | ✅ | Single-column | | Default values | ✅ | ✅ | | | JSON / JSONB | ✅ | ✅ | Displayed as text | | UUID columns | ✅ | — | Auto-generation detected | | TIMESTAMPTZ | ✅ | — | Mapped to DateTime widget | | ENUM type | — | ✅ | Displayed as text (SELECT widget planned) | | TINYINT(1) as BOOLEAN | — | ✅ | Automatically mapped | | Multiple schemas | ✅ | ✅ | Select which schema to import | | Schema sync (re-import) | ✅ | ✅ | Detects added/removed/modified columns, indexes, and foreign keys | | Drift detection | ✅ | ✅ | Compares your metadata against the live database | ## Partially Supported These features work but with some limitations: | Feature | Limitation | |---------|-----------| | Composite unique constraints | Detected as an index, not as column-level unique | | Partial indexes (`WHERE` clause) | Index is imported but the filter predicate is not preserved | | PostgreSQL array types (`TEXT[]`) | Imported as a text column — array syntax not parsed | | Expression indexes | Index is imported but the expression is not preserved | | Interval type (PostgreSQL) | Imported and displayed as text | | Database views (`CREATE VIEW`) | Imported and readable, but not editable — a view is not writable, so the grid and API expose it read-only. Drift checking treats an imported view as a table, so a change to the view's definition is reported in the same terms as a change to a table. | ## Not Supported These database features are **not imported** during schema sync. They do not affect the tables and columns that SchemaStack does import — your data remains fully accessible. | Feature | Why | |---------|-----| | Materialized views | Not imported. Unlike a plain view, these are not read during import at all. | | Triggers | Triggers execute automatically in your database and are not affected by SchemaStack | | Stored procedures & functions | Server-side logic that runs independently of SchemaStack | | Partitioned tables | The partitions are visible as individual tables if needed | | PostGIS / spatial types | Geospatial data types are not mapped to a widget | | Table inheritance (PostgreSQL) | Inherited tables are imported as standalone tables | | Row-level security policies | Security policies are enforced by your database, not by SchemaStack | | Domain types | Custom domain types are mapped to their base type | | Full-text search indexes (GIN/GiST) | Specialized index types are not imported | | Exclusion constraints | PostgreSQL-specific constraint type not imported | | CHECK constraints | Requires `information_schema` access which not all connections have | ## How Schema Sync Works When you connect a workspace to your database, SchemaStack: 1. **Extracts** your table structure via standard JDBC metadata (no special permissions needed beyond `SELECT`) 2. **Creates** a view for each table, with columns matching your database schema 3. **Detects** foreign key relationships and creates navigable links between views 4. **Generates** appropriate UI widgets for each column type (text input, number input, date picker, etc.) On subsequent syncs, SchemaStack detects what changed: - New tables or columns are added - Removed tables or columns are cleaned up - Type changes, nullable changes, and foreign key changes are updated - Your customizations (column display names, positions, widget overrides) are preserved ## Type Mapping SchemaStack maps database column types to UI widget types: | Database Type | Widget | Notes | |---------------|--------|-------| | VARCHAR, CHAR | String | Single-line text input | | TEXT, CLOB | Text | Multi-line text area | | INTEGER, BIGINT, SMALLINT, SERIAL | Integer | Whole number input | | NUMERIC, DECIMAL, FLOAT, DOUBLE | Number | Decimal number input | | BOOLEAN | Boolean | Toggle switch | | DATE | Date | Date picker | | TIMESTAMP, TIMESTAMPTZ, DATETIME | DateTime | Date + time picker | | UUID | UUID | Auto-generated if empty | | JSON, JSONB | Text | Displayed as formatted text | | BYTEA, BLOB | String | Binary data as text | | ENUM (MySQL) | String | Text input (SELECT widget planned) | | INTERVAL (PostgreSQL) | String | Text representation | You can change the widget type for any column after import. --- # Schema Migrations Source: https://docs.schemastack.io/guide/schema-migrations When you modify your database structure in SchemaStack — changing a column type, toggling nullable, adding or removing columns — the platform automatically generates and applies the necessary database migration. This page explains how that works, what to expect, and how the system keeps your data safe throughout. ## How It Works SchemaStack separates changes into two categories: - **Display changes** (rename a column, reorder, hide/show) are applied instantly — no database changes needed. - **Schema changes** (column type, nullable, unique, default value) require an actual `ALTER TABLE` on your database. These are processed asynchronously. When you make a schema change, here's what happens behind the scenes: ``` You make a change The system detects Your database is in the SchemaStack → what needs to change → updated safely interface and queues the work in the background │ │ │ │ │ │ ▼ ▼ ▼ ┌─────────┐ ┌──────────────────┐ ┌──────────────┐ │ Edit a │ │ Analyze impact: │ │ Generate & │ │ column │ │ How long? What │ │ execute the │ │ setting │ │ gets locked? │ │ SQL migration│ └─────────┘ └──────────────────┘ └──────────────┘ │ ▼ ┌──────────────┐ │ Notify you │ │ when done │ └──────────────┘ ``` ## The Migration Lifecycle ### 1. You make a change In the column properties panel, you modify a setting that affects the database — for example, changing a column from **Text** to **Number**, or toggling **Nullable** off. ### 2. Impact analysis Before anything happens, SchemaStack analyzes the impact: - **How many rows** does this table have? - **How long** will this migration take? - **Will it block** other users from reading or writing data? - **Are other tables affected** through foreign key relationships? If the change is significant (large table, blocking operation), you'll see a confirmation dialog with these details before proceeding. ### 3. Migration is queued Once confirmed, the change is queued for processing. You'll see a visual indicator on the affected column: | Impact Level | What You See | What It Means | |---|---|---| | **Transparent** | Nothing changes | Migration is instant (< 100ms) | | **Brief** | Lock icon on column | Editing paused for a few seconds | | **Blocking** | Migration overlay | Table is temporarily unavailable while the change is applied | ### 4. Migration executes The migration processor generates the correct SQL for your database (PostgreSQL or MySQL) and applies it. For longer migrations, you'll see a progress bar with: - Percentage complete - Current phase (e.g., "Rewriting table", "Building index") - Estimated time remaining ### 5. Completion When the migration finishes: - The column unlocks automatically - Your data is updated and ready to use - All connected users see the change in real time via live updates - If something went wrong, the column unlocks with an error notification and your data remains unchanged ## What Happens to Your Data During a Migration ### Can I still read my data? It depends on the operation and your database: | Database | Column type change | Add NOT NULL | Add unique constraint | |---|---|---|---| | **PostgreSQL** | Reads blocked | Reads blocked | Reads allowed | | **MySQL (InnoDB)** | Reads allowed | Reads allowed | Reads allowed | When reads are blocked, SchemaStack shows a migration overlay. When only writes are blocked, you can still browse your data in read-only mode. ### Can other users still work? Other users connected to the same workspace see the migration state in real time. If the migration blocks writes, they'll see a banner explaining what's happening and when it's expected to finish. ### What about my API integrations? If you have external tools connected via the Workspace API or MCP API, they'll receive a `503 Service Unavailable` response with a `Retry-After` header during blocking migrations. Well-behaved HTTP clients will automatically retry after the specified delay. ## Instant vs. Long-Running Changes Not all schema changes are equal. Here's a quick reference for common operations: ### Instant changes (you won't even notice) - Adding a nullable column - Dropping a column - Changing a default value - Renaming a column ### Brief pauses (a few seconds at most) - Adding a unique constraint (index needs to be built) - Adding a foreign key (existing data needs to be validated) ### Longer migrations (depends on table size) - Changing a column's data type (entire table is rewritten) - Adding NOT NULL to an existing column (all rows need to be checked) For tables with under 10,000 rows, even "long" migrations typically complete in under a second. The impact scales with data volume. ## Schema Sync Sometimes your database is modified outside of SchemaStack — perhaps by a DBA running SQL directly, or another tool making changes. Schema sync detects these differences and updates SchemaStack's understanding of your database to match reality. ``` Your actual database SchemaStack's After sync, they may have changed → metadata may be → match again outside SchemaStack out of date ``` ### Quick check vs. full sync - **Quick check**: Compares a fingerprint (hash) of your schema — answers "has anything changed?" in under a second - **Full sync**: Walks through every table and column, reports exactly what's different, and updates the metadata ### What sync does and doesn't do - **Does**: Updates SchemaStack's metadata to match your actual database - **Does NOT**: Modify your database in any way - **Does NOT**: Create or drop tables (only syncs column-level changes) ## Migration History Every migration is tracked and auditable: - **What changed**: The exact SQL that was generated and executed - **When**: Timestamps for start and completion - **Duration**: How long the migration actually took - **Who**: Which user initiated the change - **Status**: Whether it succeeded or failed This history also helps SchemaStack improve its duration estimates over time — the more migrations you run, the more accurate the "estimated time remaining" becomes. ## Multi-Database Support SchemaStack generates the correct migration SQL for your specific database: | Feature | PostgreSQL | MySQL (InnoDB) | |---|---|---| | Column type changes | Full table rewrite | Full table copy | | Nullable changes | Table scan required | Online (concurrent reads/writes) | | Add column | Near-instant | Truly instant (8.0.12+) | | Unique constraints | Blocks writes during index build | Online index build | | Progress tracking | Real-time from PostgreSQL internals | Time-based estimation | The system automatically detects your database type and version to provide accurate impact predictions and generate optimal SQL. ## Tips - **Small tables are fast**: If your table has fewer than 10,000 rows, almost any schema change will be near-instant. - **Check before you change**: Use the impact preview (shown before confirmation) to understand what will happen before committing to a change. - **One at a time**: Only one blocking migration can run per table at a time. If you need multiple changes, they'll be processed sequentially. - **Connected users are informed**: All users viewing the affected data see real-time migration status — no one is left wondering why their edits aren't saving. - **Your data is safe**: If a migration fails, your data and schema remain in their pre-migration state. Nothing is partially applied. --- # AI Integration (MCP) Source: https://docs.schemastack.io/guide/ai-integration SchemaStack supports the [Model Context Protocol (MCP)](https://modelcontextprotocol.io) — an open standard that lets AI assistants like Claude interact directly with your workspace data. Instead of copy-pasting data or writing API calls, your AI assistant can browse schemas, query data, and even create records through a natural conversation. ## What Can AI Do With Your Workspace? Once connected, an AI assistant can: - **Browse your schema** — list workspaces, views (tables), and columns - **Query data** — search and retrieve records with pagination - **Create and edit records** — insert new rows or update existing values - **Manage schema** — create views, add columns, set up validation constraints - **Understand your data model** — read form schemas to understand column types and constraints ## Access Levels Each workspace has its own MCP access level, giving you fine-grained control over what AI clients can do: | Level | Read Schema | Query Data | Create/Edit Records | Modify Schema | |-------|------------|------------|-------------------|--------------| | **Disabled** (default) | No | No | No | No | | **Read-Only** | Yes | Yes | No | No | | **Data-Only** | Yes | Yes | Yes | No | | **Full** | Yes | Yes | Yes | Yes | **Tip** Start with **Read-Only** to let AI assistants explore and query your data safely. Upgrade to **Data-Only** or **Full** when you need AI-assisted data entry or schema management. ## Setting Up MCP Access ### 1. Enable MCP for Your Workspace In the Admin app, open **Workspace → MCP**, set the access level to **Read-Only**, and save. MCP is **Disabled** by default, so this step is required — keys created while the workspace is disabled are rejected until you raise the level. Only workspace administrators (OWNER or ADMIN role) can change MCP settings. **Prefer the API?** ```http PUT /api/workspaces/{workspaceUuid}/mcp-config Authorization: Bearer Content-Type: application/json { "accessMode": "READ_ONLY" } ``` ### 2. Create an MCP API Key On the same page, choose **Create API key**. Copy the key — it starts with `mcp_` and is shown only once. A key carries its own access level, and the effective level is the **lower** of the key's level and the workspace ceiling. You can also scope a key to specific views, so an assistant only ever sees the tables you point it at. ### 3. Connect Claude Desktop Add SchemaStack as an MCP server in your Claude Desktop configuration file: ```json [macOS (~/.claude/claude_desktop_config.json)] { "mcpServers": { "schemastack": { "url": "https://schemastack.io/mcp", "headers": { "Authorization": "Bearer mcp_your_key_here" } } } } ``` ```json [Windows (%APPDATA%/Claude/claude_desktop_config.json)] { "mcpServers": { "schemastack": { "url": "https://schemastack.io/mcp", "headers": { "Authorization": "Bearer mcp_your_key_here" } } } } ``` ### 4. Connect Claude Code Add SchemaStack as an MCP server in your project's `.mcp.json` file: ```json { "mcpServers": { "schemastack": { "type": "url", "url": "https://schemastack.io/mcp", "headers": { "Authorization": "Bearer mcp_your_key_here" } } } } ``` ### 5. Start Using It Once connected, you can ask your AI assistant things like: - *"What workspaces do I have?"* - *"Show me the columns in the Customers view"* - *"Query the first 10 orders"* - *"Add a new column called 'priority' to the Tasks view"* - *"Create a new customer record with name 'Acme Corp' and email 'hello@acme.com'"* ## Available Tools Your AI assistant has access to 56 tools organized by category: ### Workspace Tools | Tool | Description | |------|-------------| | `get_workspace` | Get workspace details including its views | ### View Tools | Tool | Description | |------|-------------| | `list_views` | List all views (tables) in a workspace | | `get_view` | Get view details with column definitions | | `create_view` | Create a new view (database table) | | `update_view` | Update a view's name or slug | | `delete_view` | Delete a view and its data | ### Column Tools | Tool | Description | |------|-------------| | `list_columns` | List columns in a view; supports `nameFilter`, `limit`/`offset` paging, and a `compact` projection for large views | | `add_column` | Add a column with a display name and widget type (STRING, EMAIL, DECIMAL, SELECT, …); also creates computed (formula), relationship, and aggregate columns | | `update_column` | Update display name, widget type, options, form visibility, API key (dataKey), aggregate settings | | `preview_column_change` | Dry-run: answers whether a change would require a database migration, with full impact analysis | | `delete_column` | Remove a column | | `get_relationship_options` | Paginated dropdown options for a foreign-key column | | `cancel_migration` | Force-cancel a stuck migration that has left a table locked | ### Data Tools | Tool | Description | |------|-------------| | `get_form_schema` | Get the full schema for a view (types, constraints, widgets) | | `query_data` | Query records with pagination, filtering, and sorting | | `get_record` | Get a single record by ID | | `create_record` | Create a new data record | | `update_record` | Update a cell value in a record | | `bulk_edit` | Apply the same value(s) to many rows at once (synchronous) | | `fill_column` | Fill a column across many rows, with an optional row filter | | `bulk_update` | Bulk update rows — asynchronous, returns a job ID | | `bulk_delete` | Bulk delete rows — asynchronous, returns a job ID | | `bulk_export` | Export rows to CSV or JSON — asynchronous, download link via SSE | **Warning: Known issues in `query_data` filters** `IN` / `NOT_IN` currently match nothing (single-parameter binding bug), and a filter on a raw foreign-key column that has a relationship display column is silently ignored — the response is the **unfiltered** result. Verify `totalRecords` before acting on filtered data, and prefer `EQ` filters on regular columns. Both issues are tracked. The generated REST API's `filter[...]` syntax is unaffected. ### Constraint Tools | Tool | Description | |------|-------------| | `list_constraints` | List validation constraints on a column | | `add_constraint` | Add a validation constraint (REQUIRED, NOT_BLANK, MAX_LENGTH, EMAIL, etc.) | | `update_constraint` | Change a constraint's value, message or enabled state | | `toggle_constraint` | Enable or disable a constraint without deleting it | | `delete_constraint` | Remove a validation constraint | | `list_entity_constraints` | List table-level (multi-field) constraints on a view | | `add_entity_constraint` | Add a table-level constraint | | `update_entity_constraint` | Update a table-level constraint | | `delete_entity_constraint` | Remove a table-level constraint | ### Relationship Tools | Tool | Description | |------|-------------| | `get_relationship_graph` | Traverse how a view connects to others via foreign keys (depth 1–5) | | `get_available_relationships` | Which related tables/columns can still be added | | `add_relationship_column` | Add a related-record column to a view | | `get_relationship_paths` | Read the configured join paths for a view | | `set_relationship_paths` | Configure join paths | | `clear_relationship_paths` | Reset join paths | ### Schema Tools | Tool | Description | |------|-------------| | `import_schema` | Import an existing database schema — creates views and columns from live tables. **Alias of `sync_schema`** | | `check_drift_quick` | Hash comparison: has the database drifted from stored metadata? | | `check_drift` | Full drift report against the workspace database | | `sync_schema` | Detect and apply changes after the database was altered externally. **Alias of `import_schema`** | | `sync_view_columns` | Backfill view columns for views that have **none** — it does not refresh a view that already has them | | `reset_schema` | **Destructive** — drop all schema metadata and re-import from the database | **Note: Two names, one operation** `import_schema` and `sync_schema` run exactly the same sync. Both are safe to run repeatedly, and neither duplicates existing views — use whichever name reads better for what you are doing. `sync_view_columns` is narrower than its name suggests: it only fills in views that have no columns at all, and skips any view that already has them. It cannot repair a view whose columns have drifted — use `sync_schema` for that. ### Index Tools | Tool | Description | |------|-------------| | `list_indexes` | List database indexes on a view's table | | `create_index` | Create an index, including composite and unique | | `delete_index` | Drop an index | ### API Config Tools | Tool | Description | |------|-------------| | `get_entity_api_config` | Which REST operations a view exposes on the public Workspace API | | `update_entity_api_config` | Turn read/create/update/delete on or off, set max page size | ### Filter Preset Tools | Tool | Description | |------|-------------| | `list_filter_presets` | List saved filter/sort/column configurations for a view | | `get_filter_preset` | Read one preset | | `create_filter_preset` | Save a reusable filter/sort/column configuration | | `update_filter_preset` | Update a preset | | `delete_filter_preset` | Delete a preset | | `get_filter_preset_tags` | List tags used across a view's presets | ### External Identity Tools | Tool | Description | |------|-------------| | `get_external_idp_config` | Read the workspace's external OIDC configuration. Requires a **full-access** key — this is authentication configuration, not workspace data | Configuring the external identity provider is **not available over MCP**, at any access level. Pointing a workspace at an OIDC issuer grants standing access to everyone holding that issuer's tokens, and it outlives revoking the key that set it — so "revoke the credential" would stop being a complete response to a leak. It stays an administrator action: the admin UI, or `PUT /api/workspaces/{uuid}/external-idp` with a signed-in session. ## Connecting a Hosted AI Client **Tip: Announcement** The walkthrough version of this section, with the full flow diagram: [Add your database to Claude with one URL](https://schemastack.io/blog/agent-connector). Claude Desktop, Claude Code and Cursor read a config file, so pasting an `mcp_` key works. A **hosted** client — one where you only ever type a URL, like adding a connector in Claude or ChatGPT — has nowhere to put a key. Those clients sign in with OAuth, and SchemaStack now tells them how without you configuring anything. Give the connector this URL: ``` https://schemastack.io/mcp ``` What happens next is automatic: 1. The client calls the URL with no credential and gets a `401` naming `/.well-known/oauth-protected-resource/mcp`. 2. That document names the authorization server, whose own document at `/.well-known/oauth-authorization-server` lists the endpoints. 3. The client registers itself and gets a `client_id`. Registering grants it nothing. 4. Your browser opens the SchemaStack consent screen. You sign in if you are not already, and see which application is asking and what for. Because the URL named no workspace, the screen also lists the workspaces you can reach so you can choose one. If the application registered itself rather than being added by an administrator, the screen says so. 5. Approving returns the client an access token for the workspace you chose, and it can use the tools that workspace allows. **Warning** The consent screen shows a name the application chose for itself. Anyone can register a client under any name, so treat the name as a claim, not a recommendation — only approve a connection you started yourself and recognise. ### Naming the workspace up front You can skip the picker by naming the workspace in the URL: ``` https://schemastack.io/mcp?workspace= ``` Open the workspace in the admin app to find its UUID; it is in the address bar. Worth doing when you are writing setup instructions for other people, or connecting an account that can reach many workspaces and you want no ambiguity about which one. The consent screen then shows that workspace instead of a list, and offers no choice — if you cannot reach it, consent is refused rather than quietly falling back to one you can. Either way the token is bound to one workspace: pointing it at another workspace's URL is refused. The list only ever contains workspaces you can already reach. An organisation owner or admin sees every workspace in the organisation; everyone else sees the ones they are a member of. A workspace in maintenance mode is offered to administrators only, exactly as it is elsewhere. ### Cutting off a connection **Workspace → OAuth2** lists the applications with active sessions in the workspace, including ones that registered themselves, and who granted them. An administrator can revoke a whole application's sessions or one person's. Revoking takes effect immediately, including for a token the application is already holding. Access tokens are not stored anywhere and cannot be reached, so a revocation records the moment it happened and any token issued before that is refused from then on — on MCP and on the Workspace API alike. ## Signing In With OAuth Instead of a Key An MCP client can also authenticate with an **OAuth 2.0 access token** — the same token the Workspace API accepts, obtained through an Authorization Code + PKCE flow. Use this when an application acts on behalf of a person who signs in, rather than holding a workspace key of its own. An administrator registers the application first, under **Workspace → OAuth2** in the admin app, which is where its client ID and redirect URIs come from. Point the client at the workspace you mean: ``` https://schemastack.io/mcp?workspace= ``` The `?workspace=` parameter is optional — the token already says which workspace it is for — but when you supply it, it has to match. That way a token for one workspace cannot be pointed at another workspace's address. ### What an OAuth token is allowed to do An access token is capped lower than an API key: | Scope | MCP access | |-------|-----------| | `workspace:read` | Read-Only | | `workspace:write` | Data-Only | | — | Full is not reachable with an OAuth token | Consenting to a scope called "write" means agreeing to let the application write records — not to let it drop a column or change a type on a database you own. Schema changes stay with `mcp_` keys, which an administrator creates knowingly. The workspace access level still applies on top, so a workspace set to **Read-Only** keeps an application with `workspace:write` at Read-Only, and a workspace with MCP **Disabled** refuses the token outright. **Tip** Disabling an OAuth2 client in **Workspace → OAuth2** cuts off its MCP access straight away, without waiting for its access tokens to expire. ## Transport Protocol SchemaStack uses the **Streamable HTTP** MCP transport (not SSE). This is the newer, stateless-friendly transport from the [MCP specification (2025-03-26)](https://modelcontextprotocol.io/specification/draft/basic/transports). ### How It Works All communication happens via `POST` requests to a single `/mcp` endpoint. The server uses sessions to track state: 1. **Initialize** — your first request must be an `initialize` call. The server returns an `Mcp-Session-Id` header in the response. 2. **Include the session ID** — all subsequent requests must include the `Mcp-Session-Id` header from step 1. Most MCP clients (Claude Desktop, Claude Code, Cursor) handle this handshake automatically. If you're building a custom integration, here's the flow: ```bash # Step 1: Initialize and capture the session ID curl -D- -X POST https://schemastack.io/mcp \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -H "Accept: application/json, text/event-stream" \ -d '{ "jsonrpc": "2.0", "id": 1, "method": "initialize", "params": { "protocolVersion": "2025-03-26", "capabilities": {}, "clientInfo": {"name": "my-app", "version": "1.0"} } }' # → Response includes: Mcp-Session-Id: abc123... # Step 2: Call tools with the session ID curl -X POST https://schemastack.io/mcp \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -H "Accept: application/json, text/event-stream" \ -H "Mcp-Session-Id: abc123..." \ -d '{"jsonrpc": "2.0", "id": 2, "method": "tools/call", "params": {"name": "get_workspace", "arguments": {}}}' ``` **Tip** If you see the error `Mcp-Session-Id header not found`, your client is skipping the initialize handshake. Make sure it sends an `initialize` request first and includes the returned session ID on all follow-up requests. ## Schema Migrations When an admin changes a column's type or adds constraints, a schema migration runs on the database. During this time, the affected table may be temporarily unavailable. If your AI assistant tries to read or write data on a table that's being migrated, it will receive an error like: ``` Table 'customers' is currently being migrated (operation: abc-123, impact: BLOCKING). Please retry in ~15 seconds. ``` The AI assistant can inform you and retry after the migration completes. Most migrations take seconds — only large tables with type changes take longer. **What's blocked depends on the migration type:** | Migration | Reads blocked? | Writes blocked? | |---|---|---| | Column type change (PostgreSQL) | Yes | Yes | | Column type change (MySQL) | No | Yes | | Add NOT NULL (PostgreSQL) | Yes | Yes | | Add NOT NULL (MySQL) | No | No | | Add unique constraint | No | Yes | **Tip** If your AI workflow needs uninterrupted access, coordinate schema changes with your team to avoid running migrations during active AI sessions. ## Security - **Authentication**: an `mcp_` API key, or an OAuth 2.0 access token. Your browser session token is not accepted — signing in to the admin app grants an AI client nothing. - **Authorization**: the workspace MCP access level is the ceiling, and it is **Disabled** until an administrator raises it. A key may carry a lower level than the ceiling, and may be scoped to named views; the effective permission is always the lower of the two. An OAuth token additionally cannot exceed Data-Only. - **Workspace isolation**: a credential reaches exactly one workspace — the one it was issued for. There is no credential that spans an organisation. **Warning** An `mcp_` key is a workspace credential, not a personal one: anyone holding it has the access level it carries, whoever created it. Keep keys at the lowest level that does the job, scope them to specific views where you can, and revoke them in **Workspace → MCP** when an integration is retired. ## Checking MCP Status To check the current MCP configuration for a workspace: ```http GET /api/workspaces/{workspaceUuid}/mcp-config Authorization: Bearer ``` Response: ```json { "accessMode": "READ_ONLY" } ``` --- # Zapier Integration Source: https://docs.schemastack.io/guide/zapier-integration SchemaStack integrates with [Zapier](https://zapier.com) to connect your data to 7,000+ apps — no code required. Send SchemaStack data to Mailchimp, Slack, Google Sheets, and more. ## Two Ways to Connect ### 1. Outbound Webhooks (Push Data Out) Send selected rows from SchemaStack to Zapier using webhooks: 1. In Zapier, create a new Zap with **Webhooks by Zapier** → **Catch Hook** as the trigger 2. Copy the webhook URL 3. In SchemaStack, add this URL as a webhook on your view (View Properties → Webhooks) 4. Select rows and use "Send to webhook" to push data 5. In Zapier, map the fields to your destination app **Best for:** Manually triggered exports, batch operations, ad-hoc data pushes. ### 2. Native Zapier Integration (Automated) Use the SchemaStack Zapier app for automated triggers and actions: #### Triggers (Data → Zapier) - **New Row** — Triggers when a row is added to a table - **Updated Row** — Triggers when a row is modified (requires `updated_at` column) #### Actions (Zapier → Data) - **Create Row** — Add a new row to a table - **Update Row** — Modify an existing row by ID #### Searches - **Find Row** — Look up a row by field value **Best for:** Automated workflows, real-time syncing, two-way integrations. ## Setting Up the Native Integration ### 1. Get Your API Key 1. Go to **Admin Console** → **Workspace Settings** → **API Keys** 2. Create a new API key with appropriate permissions 3. Copy the key (starts with `sk_`) ### 2. Connect in Zapier 1. Search for **SchemaStack** in Zapier's app directory 2. Click **Connect** 3. Enter your API key, organisation slug, and workspace slug 4. Test the connection ### 3. Create Your First Zap **Example: New customer → Slack notification** 1. Trigger: SchemaStack → New Row → Select "customers" table 2. Action: Slack → Send Channel Message 3. Map fields: `{{name}} just signed up ({{email}})` **Example: Form submission → SchemaStack row** 1. Trigger: Typeform → New Entry 2. Action: SchemaStack → Create Row → Select "leads" table 3. Map form fields to table columns ## Dynamic Fields The SchemaStack Zapier integration automatically loads your table's columns as dynamic fields. When you select a table, Zapier fetches the column definitions and presents them as form fields with the correct types. ## Required Timestamp Columns For Zapier triggers to work reliably, your tables should have: | Column | Timestamp Behavior | Used by | |--------|-------------------|---------| | `created_at` / `createdAt` / `created` | **On insert** (created_at) | New Row trigger — sorts by this to find newest rows | | `updated_at` | **On update** (updated_at) | Updated Row trigger — sorts by this to find recently changed rows | Set these up in the Data Platform: select the column → Properties panel → **Timestamp Behavior**. The New Row trigger looks for columns named `created_at`, `createdAt`, or `created` (in that order). If none are found, it falls back to any timestamp column. **Tip: UUID Primary Keys** Tables with UUID primary keys **must** have a timestamp column for the New Row trigger. Integer/auto-increment PKs work without it since new rows always have the highest ID. ## Limitations - **Polling interval:** Zapier checks for new/updated rows every 1-15 minutes (depends on your Zapier plan) - **New Row trigger:** For UUID PK tables, requires a `created_at` column with "On insert" timestamp behavior - **Updated Row trigger:** Requires an `updated_at` column with "On update" timestamp behavior - **API key permissions:** The API key must have read/write access to the tables you want to use - **Rate limits:** Standard SchemaStack API rate limits apply --- # Relationship inheritance & OWNED editing Source: https://docs.schemastack.io/guide/relationship-inheritance When you build a view that pulls in fields from a related entity (e.g. a **Customer** view showing the customer's **Address.street**), SchemaStack treats those fields intelligently: - The widget type, label, and validation rules are **inherited** from the entity's *canonical view* — set them once on Address and every view that pulls in Address fields renders them the same way. - You can opt to **edit related rows inline** ("OWNED" mode) — change the address from inside the customer view, without leaving the table. SchemaStack enforces who's allowed to do that and blocks it when sharing the related row with other parents would cause silent side effects. This page explains how it works and how to configure it. ## Canonical view per entity Every entity in SchemaStack has exactly one **canonical view** — the source of truth for that entity's display configuration (widget types, labels, widget options, inline-edit fields). When a table is imported via schema sync, SchemaStack creates a default view for it and marks that view canonical. If you create additional views for the same entity, they **inherit** display configuration from the canonical view by default — so a "DATE" widget you set on `CourseYear.year_value` in the canonical view shows up as a DATE widget in every other view that pulls in `year_value`, including views displaying it through a relationship. ### Overriding inheritance per view Each inheritable property can be overridden per view. In the column properties panel, fields with an inheritance source show a small badge: > Inherited from `CourseYear.year_value` · **Override** Click **Override** to set a custom value just for this view. The badge switches to: > Overridden · **Reset to inherited** Click **Reset to inherited** to drop the override and resume following the canonical view. The properties that participate in inheritance: | Property | Inherited from | Override scope | |---------------------|--------------------------------------|-------------------| | Widget type | Canonical view's column | Per ViewColumn | | Display name | Canonical view's column | Per ViewColumn | | Widget options | Canonical view's column | Per ViewColumn | | Inline-edit fields | Canonical view's column | Per ViewColumn | | Constraints / rules | Entity-level (locked, always apply) | Add view-level rules on top | Constraints defined at the entity level are **always enforced** — you cannot disable them per view. You can only add view-level rules that layer on top. ## Relationship types When you create a relationship column from an entity, you pick the *shape* of the relationship: | Shape | Meaning | |---------------|-----------------------------------------------------------------------------| | **Many-to-one** | This row references one related row; many rows can reference the same target. | | **One-to-one** | Each row links to at most one related row; each related row is referenced from at most one row here. | | **Many-to-many** | Either side can link to multiple rows on the other side via a join table. | | **One-to-many** | Inverse of many-to-one — read-only display of rows that reference this one. | One-to-one is implemented as a many-to-one with a UNIQUE constraint on the FK column at the database level — but SchemaStack tracks the intent so the UI treats it as 1:1. ## Ownership Each relationship has an **isOwned** flag: - **Owned** — the related row exclusively belongs to the parent. Editing the related row from the parent view is safe, because no other parent references it. (Typical: Customer→Address, User→Profile, Order→Shipment.) - **Shared** — the related row is referenced by multiple parents. Editing it from one parent would silently affect the others. (Typical: Order→Country, Product→Category.) Defaults: - 1:1 → owned (you usually want this) - M:1 → shared (you almost always want this) - M:M, 1:M → never owned (semantically meaningless to "own" a many-side) You can flip the default in the picker when creating a relationship column. ## Edit modes for relationship columns A relationship column can be edited in one of three modes (set in the column properties panel under **Edit Mode**): ### Reference (the default for shared relationships) Picking a value changes which row is referenced (the FK). The related row itself is not modified. > "Switch this customer's country from France to Germany." ### Owned (the default for 1:1 owned relationships) Editing changes the field on the related row directly. The FK is unchanged. > "Change the street on this customer's address." The OWNED option is only enabled when **every hop** in the relationship path is owned. For multi-hop columns (e.g. Customer → Address → Geolocation), all hops must be marked owned. If any hop is shared, OWNED is disabled with a message naming the offending hop: > OWNED edit blocked — relationship `country` is shared. Editing through it > would affect other rows that reference the same target. ### Association (M:M) Add or remove links in the join table without modifying either endpoint row. ## Permissions OWNED edits write to a row in a different table than the one the source view exposes. SchemaStack checks permissions against the **terminal entity**, not the source view — so reading the customer view does not imply permission to edit the address. The user needs `EDIT_ROW` on the entity whose row is actually being modified. ## What happens on save When you save an OWNED edit, SchemaStack: 1. Walks the relationship path to find the actual row to update (one query per hop, following FK values through real data). 2. Verifies every hop in the path is owned. 3. Checks you have permission on the target entity. 4. Validates the new value against the target column's constraints. 5. Updates the related row. 6. Refetches the source row so the table reflects the joined update immediately. 7. Broadcasts a `terminal.row.edited` event so other open views joining the target entity refresh. If the relationship path passes through a row that hasn't been created yet (e.g. the customer has no Address row), SchemaStack returns a clear error naming the missing intermediate — you can create that row first and try again. ## See also - [Schema migrations](https://docs.schemastack.io/guide/schema-migrations.md) — how SchemaStack manages underlying database changes. - [Key concepts](https://docs.schemastack.io/guide/key-concepts.md) — entities, views, columns explained. --- # Workspace API Source: https://docs.schemastack.io/api/ SchemaStack automatically generates a full REST API for every workspace. Once you've defined your entities and columns, you can immediately start reading and writing data through the API — no code generation or deployment needed. ## How It Works 1. **Define your schema** — create entities with columns, types, and relationships in SchemaStack 2. **Generate an API key** — create a key in your workspace settings with the appropriate permission level 3. **Start making requests** — your data is available at predictable, RESTful endpoints Every entity in your workspace gets its own set of CRUD endpoints automatically. ## Base URL ``` https://your-instance.schemastack.com/api/v1/{orgSlug}/{workspaceSlug} ``` Replace `{orgSlug}` and `{workspaceSlug}` with your organization and workspace slugs. ## Quick Example ```bash # List all customers curl "https://api.schemastack.com/api/v1/acme-corp/sales/Customer" \ -H "Authorization: Bearer sk_live_abc123..." # Create a customer curl -X POST "https://api.schemastack.com/api/v1/acme-corp/sales/Customer" \ -H "Authorization: Bearer sk_live_abc123..." \ -H "Content-Type: application/json" \ -d '{ "name": "Jane Doe", "email": "jane@example.com" }' ``` ## API Sections | Section | Description | | --------------------------------------------- | ------------------------------------------------- | | [Authentication](https://docs.schemastack.io/api/authentication.md) | API keys, permissions, and headers | | [CRUD Operations](https://docs.schemastack.io/api/data.md) | Create, read, update, delete, and list records | | [Filtering](https://docs.schemastack.io/api/filtering.md) | Filter records by field values with operators | | [Expanding Relationships](https://docs.schemastack.io/api/relationships.md) | Include related data with `?expand=` | | [Field Selection](https://docs.schemastack.io/api/field-selection.md) | Control which fields are returned with `?fields=` | | [Pagination & Sorting](https://docs.schemastack.io/api/pagination.md) | Page through results and sort by fields | | [Validation](https://docs.schemastack.io/api/validation.md) | Constraint validation and error details | | [Bulk Operations](https://docs.schemastack.io/api/bulk-operations.md) | Batch create, update, and delete | | [Errors](https://docs.schemastack.io/api/errors.md) | Error response format and status codes | ## Response Format All responses are JSON. Successful responses wrap data in a `data` field: ```json { "data": { "id": 1, "name": "Jane Doe", "email": "jane@example.com" } } ``` List responses include pagination metadata: ```json { "data": [ ... ], "meta": { "page": 0, "size": 20, "totalElements": 150, "totalPages": 8 } } ``` Null fields are omitted from responses. ## AI Integration (MCP) SchemaStack also supports the [Model Context Protocol (MCP)](https://docs.schemastack.io/guide/ai-integration.md) — an open standard that lets AI assistants like Claude interact directly with your workspace data. Connect Claude Desktop, Claude Code, or any MCP-compatible client to browse schemas, query data, and manage records through natural conversation. [Learn more about MCP integration →](https://docs.schemastack.io/guide/ai-integration.md) ## Interactive API Docs Every workspace includes built-in OpenAPI documentation. Once your workspace is deployed, you can explore and test your API interactively: - **Swagger UI**: `https://data.schemastack.io/api/v1/{orgSlug}/{workspaceSlug}/_docs` - **OpenAPI spec**: `https://data.schemastack.io/api/v1/{orgSlug}/{workspaceSlug}/_openapi` These are generated automatically from your schema — no configuration needed. --- # Authentication Source: https://docs.schemastack.io/api/authentication The Workspace API uses **API keys** for authentication. Each key is scoped to a single workspace and has a specific permission level. ## API Keys ### Creating an API Key 1. Open your workspace in the Admin app 2. Navigate to workspace settings 3. Create a new API key 4. Choose a permission level: - **Read & Write** — full CRUD access - **Read Only** — only GET requests are allowed 5. Optionally set an expiration date 6. Copy the key immediately — it's only shown once **Warning** API keys are shown only once when created or rotated. Store them securely. If you lose a key, you can rotate it to get a new one. ### Using an API Key Include the key in the `Authorization` header as a Bearer token: ```http GET /api/v1/acme-corp/sales/Customer Authorization: Bearer sk_live_abc123... ``` ### Permission Levels | Permission | GET | POST | PUT | DELETE | | ---------------- | --- | ---- | --- | ------ | | **Read & Write** | Yes | Yes | Yes | Yes | | **Read Only** | Yes | No | No | No | Attempting a write operation with a read-only key returns `403 Forbidden`. ### Key Expiration API keys can have an optional expiration date. Expired keys return `401 Unauthorized`. Create a new key before the current one expires to avoid downtime. ## Workspace Scoping Each API key is bound to a specific organization and workspace. A key for `acme-corp/sales` cannot access data in `acme-corp/inventory`. Attempting to use a key outside its workspace returns `401 Unauthorized`. ## Security ### Key Storage API keys are **SHA-256 hashed** before storage. SchemaStack never stores your raw key — only the hash is persisted. This means: - If the database is compromised, attackers cannot recover your keys - Lost keys cannot be retrieved — rotate the key to generate a new secret - The `keyPrefix` (first 8 characters) is stored separately for identification in the admin UI ### Expiration & Rotation - **Expiration**: Set an optional expiration date when creating a key. Expired keys immediately return `401 Unauthorized`. - **Rotation**: Use the rotate action to generate a new secret while keeping the key's name, permissions, and expiration. The old secret stops working immediately and the new key is returned once — store it securely. Revoked keys cannot be rotated. - **Revocation**: Revoked keys stop working immediately. ### Auditing The `last_used_at` timestamp is updated on each request, allowing you to identify unused keys that should be revoked. ### Best Practices - **Use Read Only keys** whenever write access isn't needed - **Set expiration dates** — keys without expiration are a security risk - **Rotate regularly** — replace keys periodically even if not compromised - **Revoke immediately** if a key may be compromised - **One key per integration** — create separate keys for each external service (Jotform, Zapier, Make) so you can revoke them independently ## OAuth 2.0 For an application acting on behalf of a person who signs in, rather than holding a workspace key of its own. One access token covers both the Workspace API and that workspace's MCP endpoint. ```http GET /api/v1/acme-corp/sales/Customer Authorization: Bearer eyJhbGciOi... ``` ### Registering an application An administrator registers it in the admin app under **Workspace → OAuth2**, setting its name, redirect URIs and scopes. That produces a `client_id`; there is no client secret, because the flow uses PKCE. An application can also **register itself**, which is how a hosted AI client — one you only ever give a URL to — takes part at all: ```http POST https://schemastack.io/api/oauth2/register Content-Type: application/json { "client_name": "My integration", "redirect_uris": ["https://example.com/callback"], "scope": "workspace:read workspace:write" } ``` Registering grants nothing. The application reaches no data until someone signs in and approves it, and the consent screen says plainly when an application registered itself rather than being added by an administrator. Redirect URIs must be `https`, or `http` on loopback for an application redirecting to the machine it runs on. ### The flow Authorization Code with PKCE (`S256` only): | | | |---|---| | Authorization | `https://schemastack.io/admin/oauth2/consent` | | Token | `https://schemastack.io/api/oauth2/token` | | Revocation | `https://schemastack.io/api/oauth2/revoke` | | Registration | `https://schemastack.io/api/oauth2/register` | | Scopes | `workspace:read`, `workspace:write` | Rather than hard-coding those, a client can read them from `https://schemastack.io/.well-known/oauth-authorization-server`. Access tokens last an hour; refresh tokens last 30 days and rotate on use. If a refresh token is ever replayed, every token for that application and user is cut off — a replay means one of them was stolen. ### Permissions | Scope | Workspace API | MCP | |-------|---------------|-----| | `workspace:read` | GET only | Read-Only | | `workspace:write` | full CRUD | Data-Only | An OAuth token cannot reach Full MCP access, so it cannot change your schema. Agreeing to a scope called "write" means letting an application write records, not letting it drop a column. Schema changes need an API key created by an administrator. ### Revoking access **Workspace → OAuth2** lists the applications with active sessions, including ones that registered themselves, and who granted them. Revoking takes effect immediately — including for a token the application is already holding, not just the next one it would have been issued. ## External Identity Providers You can configure an external OIDC identity provider (Auth0, Clerk, Firebase) so your end-users can authenticate with their own accounts and call the Workspace API directly. See [External Identity Providers](https://docs.schemastack.io/api/external-identity-providers.md) for setup guides. ## Public Access Entities can be configured for unauthenticated access. See [API Settings](https://docs.schemastack.io/api/settings.md#public-access) for details. --- # CRUD Operations Source: https://docs.schemastack.io/api/data Every entity in your workspace gets a full set of CRUD endpoints. The entity name in the URL matches the entity name defined in your schema. ## Endpoint Pattern ``` /api/v1/{orgSlug}/{workspaceSlug}/{entityName} /api/v1/{orgSlug}/{workspaceSlug}/{entityName}/{id} ``` ## List Records ```http GET /api/v1/acme-corp/sales/Customer?page=0&size=20 Authorization: Bearer sk_live_abc123... ``` **Response (200):** ```json { "data": [ { "id": 1, "name": "Jane Doe", "email": "jane@example.com" }, { "id": 2, "name": "John Smith", "email": "john@example.com" } ], "meta": { "page": 0, "size": 20, "totalElements": 150, "totalPages": 8 } } ``` See [Pagination & Sorting](https://docs.schemastack.io/api/pagination.md) for details on paging and sort parameters. ## Get a Single Record ```http GET /api/v1/acme-corp/sales/Customer/42 Authorization: Bearer sk_live_abc123... ``` **Response (200):** ```json { "data": { "id": 42, "name": "Jane Doe", "email": "jane@example.com", "age": 30 } } ``` Returns `404` if the record doesn't exist. ## Create a Record ```http POST /api/v1/acme-corp/sales/Customer Authorization: Bearer sk_live_abc123... Content-Type: application/json { "name": "Jane Doe", "email": "jane@example.com", "age": 30 } ``` **Response (201):** ```json { "data": { "id": 43, "name": "Jane Doe", "email": "jane@example.com", "age": 30 } } ``` The `id` is generated automatically. Fields not included in the request default to `null` (if the column allows it). ## Update a Record ```http PUT /api/v1/acme-corp/sales/Customer/42 Authorization: Bearer sk_live_abc123... Content-Type: application/json { "name": "Jane Smith", "email": "jane.smith@example.com" } ``` **Response (200):** ```json { "data": { "id": 42, "name": "Jane Smith", "email": "jane.smith@example.com", "age": 30 } } ``` Only the fields you include are updated. Omitted fields keep their current values. ## Delete a Record ```http DELETE /api/v1/acme-corp/sales/Customer/42 Authorization: Bearer sk_live_abc123... ``` **Response:** `204 No Content` Returns `404` if the record doesn't exist. ## Relationships If your entities have relationships (e.g., an Order belongs to a Customer), you can include related data in responses using the `expand` query parameter and control which fields are returned with `fields`. See [Expanding Relationships](https://docs.schemastack.io/api/relationships.md) and [Field Selection](https://docs.schemastack.io/api/field-selection.md) for details. ## Schema Migrations When an admin changes a column type or adds constraints, a schema migration runs on the underlying database table. During this time, requests to the affected entity may return **503 Service Unavailable**: ```json { "error": { "code": "SCHEMA_MIGRATION_IN_PROGRESS", "message": "Table 'Customer' is currently being migrated. Please retry after the migration completes." }, "retryAfterSeconds": 15 } ``` The response includes a `Retry-After` header. Wait the specified number of seconds and retry. Depending on the migration type, reads may still work while writes are blocked. Check the `blocksReads` and `blocksWrites` fields in the error response to determine which operations are affected. See [Errors](https://docs.schemastack.io/api/errors.md) for the full response format. ## Content Type All request bodies must use `Content-Type: application/json`. Dates are formatted as ISO 8601 strings (`2026-01-15T10:30:00Z`). --- # Filtering Source: https://docs.schemastack.io/api/filtering Filter records by field values using bracket notation in query parameters. Filters work with pagination, sorting, relationship expansion, and field selection. ## Syntax ``` ?filter[field]=value # Equality (default) ?filter[field.operator]=value # With operator ``` Multiple filters are combined with AND logic: ``` ?filter[status]=active&filter[age.gte]=18 ``` ## Operators | Operator | Syntax | Description | Example | | ----------- | -------------------------- | ----------------------------------- | ---------------------------------- | | `eq` | `filter[field]=value` | Equal to (default) | `filter[status]=active` | | `neq` | `filter[field.neq]=value` | Not equal to | `filter[status.neq]=inactive` | | `gt` | `filter[field.gt]=value` | Greater than | `filter[age.gt]=18` | | `gte` | `filter[field.gte]=value` | Greater than or equal | `filter[age.gte]=18` | | `lt` | `filter[field.lt]=value` | Less than | `filter[price.lt]=100` | | `lte` | `filter[field.lte]=value` | Less than or equal | `filter[price.lte]=100` | | `like` | `filter[field.like]=value` | Contains substring (case-sensitive) | `filter[name.like]=Ali` | | `in` | `filter[field.in]=a,b,c` | In list (comma-separated) | `filter[status.in]=active,pending` | | `notIn` | `filter[field.notIn]=a,b` | Not in list (comma-separated) | `filter[status.notIn]=archived,void` | | `between` | `filter[field.between]=a,b`| Inclusive range, exactly two bounds | `filter[age.between]=18,65` | | `isNull` | `filter[field.isNull]=` | Is null | `filter[email.isNull]=` | | `isNotNull` | `filter[field.isNotNull]=` | Is not null | `filter[email.isNotNull]=` | ## Combining filters with OR Several `filter[...]` parameters are combined with **AND**. To express alternatives, put conditions into numbered **OR groups**: ``` filter[or][][field]=value filter[or][][field.operator]=value ``` Conditions inside one group are AND-ed, the groups are OR-ed with each other, and the whole disjunction is AND-ed with any plain filters outside it. ```bash # region = 'EU' AND ( (status='paid' AND total>=100) OR priority='urgent' ) curl ".../api/v1/acme-corp/sales/orders\ ?filter[region]=EU\ &filter[or][0][status]=paid\ &filter[or][0][total.gte]=100\ &filter[or][1][priority]=urgent" \ -H "Authorization: Bearer sk_live_abc123..." ``` Group numbers only separate the groups — they do not rank them, and they need not start at zero or be contiguous. **Warning: Malformed groups are refused** `filter[or][field]=value` (no group number) or a non-numeric group number returns a `400`. Ignoring a malformed group would *widen* the result — you would be handed rows you meant to filter out — so it is refused instead. **Note: Row-level security is never widened by OR** If the workspace uses [row-level security](https://docs.schemastack.io/api/external-identity-providers.md#rls), those conditions are applied outside your groups. `customer_id = you AND (your alternatives)` — an OR in your filter can never reach another user's rows. ## Examples ### Basic Equality Filter ```bash curl ".../api/v1/acme-corp/sales/customers?filter[name]=Alice" \ -H "Authorization: Bearer sk_live_abc123..." ``` ### Numeric Range ```bash # Customers aged 18-65 curl ".../api/v1/acme-corp/sales/customers?filter[age.gte]=18&filter[age.lte]=65" \ -H "Authorization: Bearer sk_live_abc123..." ``` ### Substring Search ```bash # Customers with "smith" in their name curl ".../api/v1/acme-corp/sales/customers?filter[name.like]=smith" \ -H "Authorization: Bearer sk_live_abc123..." ``` ### Multiple Values (IN) ```bash # Orders with status "pending" or "processing" curl ".../api/v1/acme-corp/sales/orders?filter[status.in]=pending,processing" \ -H "Authorization: Bearer sk_live_abc123..." ``` ### Null Checks ```bash # Customers without an email address curl ".../api/v1/acme-corp/sales/customers?filter[email.isNull]=" \ -H "Authorization: Bearer sk_live_abc123..." ``` ## Combining with Other Features Filters work seamlessly with pagination, sorting, expansion, and field selection: ```bash # Active customers, sorted by name, page 2, with order count curl ".../api/v1/acme-corp/sales/customers\ ?filter[status]=active\ &sort=name,asc\ &page=1&size=10\ &expand=orders\ &fields=id,name,email,orders" \ -H "Authorization: Bearer sk_live_abc123..." ``` ## Type Conversion Filter values are automatically converted to match the column type: | Column Type | Filter Value | Converted To | | -------------------- | ------------ | -------------------- | | `INTEGER`, `BIGINT` | `"42"` | `42` (Long/Integer) | | `DECIMAL`, `NUMERIC` | `"19.99"` | `19.99` (BigDecimal) | | `BOOLEAN` | `"true"` | `true` (Boolean) | | `VARCHAR`, `TEXT` | `"hello"` | `"hello"` (String) | **Warning: A value that will not convert matches nothing** A value that cannot be converted to the column's type does **not** currently return an error. `filter[age]=abc` on an `INTEGER` column answers `200` with an empty result set, as though no row matched. This is a known defect — it should be a `400` — and this page described it as one until it was checked against the running API. A filter that silently matches nothing is easy to mistake for a filter that legitimately found nothing, so validate values before you send them. ## Unknown Fields Filtering by a field that doesn't exist on the entity returns `400 BAD_REQUEST`: ```json { "error": { "code": "BAD_REQUEST", "message": "Unknown filter field: 'nonexistent'" } } ``` ## Filterable Fields Whitelist Workspace admins can restrict which fields are filterable per entity via the API configuration. When a whitelist is set, only the listed fields accept filter queries. Attempting to filter on a non-whitelisted field returns `400 BAD_REQUEST`. --- # Expanding Relationships Source: https://docs.schemastack.io/api/relationships When your entities have relationships (e.g., an Order belongs to a Customer), the API returns only the foreign key by default. Use the `expand` query parameter to include the full related object in the response. ## Default Behavior (No Expand) Without `expand`, a foreign key column is returned as a plain value: ```http GET /api/v1/acme-corp/sales/Order/1 Authorization: Bearer sk_live_abc123... ``` ```json { "data": { "id": 1, "orderNumber": "ORD-001", "total": 150.0, "customerId": 42 } } ``` ## Expanding a Relationship Add `?expand=customer` to include the full customer object: ```http GET /api/v1/acme-corp/sales/Order/1?expand=customer Authorization: Bearer sk_live_abc123... ``` ```json { "data": { "id": 1, "orderNumber": "ORD-001", "total": 150.0, "customerId": 42, "customer": { "id": 42, "name": "Jane Doe", "email": "jane@example.com" } } } ``` ## Multiple Relationships Expand several relationships at once by separating them with commas: ```http GET /api/v1/acme-corp/sales/Order?expand=customer,lineItems ``` ```json { "data": [ { "id": 1, "orderNumber": "ORD-001", "total": 150.0, "customer": { "id": 42, "name": "Jane Doe", "email": "jane@example.com" }, "lineItems": [ { "id": 1, "productName": "Widget", "quantity": 2 }, { "id": 2, "productName": "Gadget", "quantity": 1 } ] } ], "meta": { "page": 0, "size": 20, "totalElements": 1, "totalPages": 1 } } ``` ## Nested Expansion Use dot notation to expand relationships on related objects. For example, if a LineItem belongs to an Order, and the Order belongs to a Customer: ```http GET /api/v1/acme-corp/sales/LineItem?expand=order.customer ``` ```json { "data": [ { "id": 1, "productName": "Widget", "quantity": 2, "order": { "id": 1, "orderNumber": "ORD-001", "customer": { "id": 42, "name": "Jane Doe", "email": "jane@example.com" } } } ], "meta": { "page": 0, "size": 20, "totalElements": 1, "totalPages": 1 } } ``` You can combine nested and flat expansions: `?expand=order.customer,warehouse`. ## Works on All Read Endpoints The `expand` parameter works on both list and single-record endpoints: ``` GET /api/v1/{org}/{workspace}/{entity}?expand=customer # list GET /api/v1/{org}/{workspace}/{entity}/{id}?expand=customer # single record ``` ## Admin Configuration Workspace administrators can configure expansion behavior per entity: ### Default Expand Set a default expansion that applies when no `?expand` parameter is provided. For example, if `defaultExpand` is set to `customer`, then `GET /orders` automatically expands the customer relationship without the caller needing to specify it. An explicit `?expand=` parameter always overrides the default. ### Expandable Relationships (Whitelist) Restrict which relationships can be expanded. When a whitelist is configured, requesting an unlisted relationship returns `400 Bad Request`: ```json { "error": { "code": "BAD_REQUEST", "message": "Relationship 'internalNotes' is not expandable. Allowed: customer,lineItems" } } ``` When no whitelist is configured, all relationships are expandable. ### Maximum Depth The maximum nesting depth for dot-notation expansion (defaults to 3). Exceeding the limit returns `400 Bad Request`: ```json { "error": { "code": "BAD_REQUEST", "message": "Expand depth exceeds maximum of 2: order.customer.address" } } ``` ## Performance Notes - Expanded relationships are loaded using SQL `JOIN FETCH`, which avoids the N+1 query problem - Pagination remains accurate when using `expand` — the total count is computed separately - Only expand what you need. Each expanded relationship adds a JOIN to the underlying query --- # Field Selection Source: https://docs.schemastack.io/api/field-selection Use the `fields` query parameter to control which fields are included in the response. This reduces payload size and lets you fetch only the data you need. ## Default Behavior Without `fields`, all columns are returned: ```http GET /api/v1/acme-corp/sales/Customer/42 ``` ```json { "data": { "id": 42, "name": "Jane Doe", "email": "jane@example.com", "phone": "+1-555-0123", "company": "Acme Corp", "notes": "VIP customer since 2024..." } } ``` ## Selecting Fields Specify a comma-separated list of field names: ```http GET /api/v1/acme-corp/sales/Customer/42?fields=id,name,email ``` ```json { "data": { "id": 42, "name": "Jane Doe", "email": "jane@example.com" } } ``` Fields not in the list are omitted from the response. ## Nested Field Selection When combined with [`expand`](https://docs.schemastack.io/api/relationships.md), you can select specific fields on the expanded relationship using dot notation: ```http GET /api/v1/acme-corp/sales/Order?expand=customer&fields=id,total,customer.name ``` ```json { "data": [ { "id": 1, "total": 150.0, "customer": { "name": "Jane Doe" } } ], "meta": { "page": 0, "size": 20, "totalElements": 1, "totalPages": 1 } } ``` Note how `customer.name` includes only the `name` field from the expanded customer, and top-level fields like `orderNumber` are excluded because they weren't listed. ### Expanding Without Filtering If you include a relationship name without dot notation, all of its fields are returned: ```http GET /api/v1/acme-corp/sales/Order?expand=customer&fields=id,customer ``` ```json { "data": [ { "id": 1, "customer": { "id": 42, "name": "Jane Doe", "email": "jane@example.com" } } ] } ``` ## Works on All Read Endpoints The `fields` parameter works on both list and single-record endpoints: ``` GET /api/v1/{org}/{workspace}/{entity}?fields=id,name # list GET /api/v1/{org}/{workspace}/{entity}/{id}?fields=id,name # single record ``` ## Admin Configuration Workspace administrators can set a **default field selection** per entity. When configured, responses only include the specified fields by default, even when no `?fields` parameter is provided. An explicit `?fields=` parameter always overrides the default. ## Combining with Other Parameters Field selection works alongside all other query parameters: ```http GET /api/v1/acme-corp/sales/Order?fields=id,total,customer.name&expand=customer&sort=total,desc&page=0&size=10 ``` --- # Pagination & Sorting Source: https://docs.schemastack.io/api/pagination List endpoints return paginated results by default. ## Pagination Control pagination with `page` and `size` query parameters: ```http GET /api/v1/acme-corp/sales/Customer?page=0&size=50 ``` | Parameter | Default | Description | | --------- | ------- | ------------------------ | | `page` | 0 | Page number (zero-based) | | `size` | 20 | Records per page | ### Response Metadata Every list response includes a `meta` object with pagination info: ```json { "data": [ ... ], "meta": { "page": 0, "size": 20, "totalElements": 150, "totalPages": 8 } } ``` | Field | Description | | --------------- | ------------------------------------------ | | `page` | Current page number | | `size` | Records per page | | `totalElements` | Total number of records matching the query | | `totalPages` | Total number of pages | ### Navigating Pages To get the next page, increment the `page` parameter: ```bash # Page 1 (first 20 records) GET /api/v1/acme-corp/sales/Customer?page=0&size=20 # Page 2 (records 21-40) GET /api/v1/acme-corp/sales/Customer?page=1&size=20 # Page 3 (records 41-60) GET /api/v1/acme-corp/sales/Customer?page=2&size=20 ``` When `page` exceeds `totalPages`, the `data` array is empty. ## Sorting Sort results with the `sort` parameter in `field,direction` format: ```http GET /api/v1/acme-corp/sales/Customer?sort=name,asc ``` | Direction | Description | | --------- | ----------------------------------- | | `asc` | Ascending (A-Z, 0-9, oldest first) | | `desc` | Descending (Z-A, 9-0, newest first) | ### Examples Sort by name alphabetically: ``` ?sort=name,asc ``` Sort by creation date, newest first: ``` ?sort=createdAt,desc ``` ### Combining with Pagination All parameters can be combined: ``` ?page=0&size=50&sort=name,asc ``` --- # Validation Source: https://docs.schemastack.io/api/validation SchemaStack validates request data against your entity's column constraints before persisting. When validation fails, the API returns a **422 Unprocessable Entity** response with per-field error details. ## How It Works Constraints defined on your columns (via the SchemaStack UI or API) are automatically enforced on every **create** and **update** request. No additional configuration is needed. - **Create (POST)**: All constraints are checked, and required fields (non-nullable columns) must be present. - **Update (PUT)**: Only the submitted fields are validated. Missing fields are not treated as errors (partial updates are allowed). ## Validation Error Format When one or more fields fail validation, the response includes a `details` array listing each error: ```json { "error": { "code": "VALIDATION_ERROR", "message": "Validation failed: 2 error(s)", "details": [ { "field": "email", "message": "must be a valid email address" }, { "field": "name", "message": "name is required" } ] } } ``` ## Constraint Types | Type | Applies To | Description | Example Value | | ------------------ | ---------- | ------------------------------------ | -------------- | | `REQUIRED` | All | Value must be present on every write; the column may stay nullable in the database | — | | `NOT_BLANK` | String | Must not be blank or whitespace-only | — | | `MIN_LENGTH` | String | Minimum character length | `"5"` | | `MAX_LENGTH` | String | Maximum character length | `"100"` | | `PATTERN` | String | Must match a regex pattern | `"^[A-Z]{3}$"` | | `EMAIL` | String | Must be a valid email address | — | | `URL` | String | Must be a valid URL | — | | `MIN` | Number | Minimum numeric value | `"0"` | | `MAX` | Number | Maximum numeric value | `"999"` | | `POSITIVE` | Number | Must be greater than zero | — | | `NEGATIVE` | Number | Must be less than zero | — | | `POSITIVE_OR_ZERO` | Number | Must be zero or greater | — | | `NEGATIVE_OR_ZERO` | Number | Must be zero or less | — | | `PAST` | Date/Time | Must be in the past | — | | `FUTURE` | Date/Time | Must be in the future | — | | `PAST_OR_PRESENT` | Date/Time | Must be today or in the past | — | | `FUTURE_OR_PRESENT`| Date/Time | Must be today or in the future | — | ## Cross-Field Rules Entity-level constraints — rules that relate two or more columns, like *start date before end date* or *at least one of email and phone* — are enforced on API writes as well, with the same nine implemented types the app enforces. - **Create** validates the payload, which is the complete row. - **Update** validates the row **as it will be stored** — the merged state — because an update payload is partial and a rule comparing two fields may only see one of them in the request. You cannot update a row into violation through the field the rule does not mention. Comparison rules skip when either field is null; presence rules treat an empty string as absent and zero or false as present. Violations return the same 422 envelope as column constraints, with the rule's custom message when one is set. ## Built-In Checks In addition to explicit constraints, the API automatically validates: - **Required fields**: Non-nullable columns must be present on create requests. - **Column length**: String values cannot exceed the column's defined `length`. - **Type matching**: Values must be compatible with the column type (e.g., numbers for INTEGER columns, strings for VARCHAR). ## Create vs. Update | Check | Create (POST) | Update (PUT) | | ------------------------------ | :-----------: | :----------: | | Required fields (non-nullable) | Enforced | Skipped | | Type validation | Enforced | Enforced | | Column length | Enforced | Enforced | | Constraint rules | Enforced | Enforced | ## Custom Error Messages When defining constraints, you can optionally set a custom error message. If no custom message is set, a sensible default is used: ```json // Constraint with custom message { "type": "PATTERN", "value": "^[A-Z]{3}-\\d{4}$", "message": "Product code must follow the format ABC-1234" } // Default message: "must match pattern: ^[A-Z]{3}-\\d{4}$" ``` ## Examples ### Creating a Record with Validation Errors ```bash curl -X POST ".../api/v1/acme-corp/sales/products" \ -H "Authorization: Bearer sk_live_abc123..." \ -H "Content-Type: application/json" \ -d '{ "price": -5, "sku": "invalid" }' ``` Response (422): ```json { "error": { "code": "VALIDATION_ERROR", "message": "Validation failed: 3 error(s)", "details": [ { "field": "name", "message": "name is required" }, { "field": "price", "message": "must be positive" }, { "field": "sku", "message": "must match pattern: ^[A-Z]{3}-\\d{4}$" } ] } } ``` ### Updating with Partial Data (Valid) ```bash curl -X PUT ".../api/v1/acme-corp/sales/products/42" \ -H "Authorization: Bearer sk_live_abc123..." \ -H "Content-Type: application/json" \ -d '{ "price": 29.99 }' ``` Response (200) — `name` is not required on update: ```json { "data": { "id": 42, "name": "Widget", "price": 29.99, "sku": "WDG-0001" } } ``` --- # Bulk Operations Source: https://docs.schemastack.io/api/bulk-operations Perform batch create, update, or delete operations in a single request. Bulk endpoints process each item independently — individual failures don't prevent other items from succeeding. ## Endpoints | Method | Endpoint | Description | | -------- | ---------------- | ----------------------- | | `POST` | `/{entity}/bulk` | Create multiple records | | `PUT` | `/{entity}/bulk` | Update multiple records | | `DELETE` | `/{entity}/bulk` | Delete multiple records | ## Batch Limits - **Maximum batch size**: 100 items per request - Empty arrays return `400 BAD_REQUEST` - Arrays exceeding 100 items return `400 BAD_REQUEST` ## Bulk Create Create multiple records at once. Each item is validated and persisted independently. **Request:** ```bash curl -X POST ".../api/v1/acme-corp/sales/customers/bulk" \ -H "Authorization: Bearer sk_live_abc123..." \ -H "Content-Type: application/json" \ -d '[ { "name": "Alice", "email": "alice@example.com" }, { "name": "Bob", "email": "bob@example.com" }, { "name": "Charlie", "email": "charlie@example.com" } ]' ``` **Response (200):** ```json { "succeeded": 3, "failed": 0 } ``` ## Bulk Update Update multiple records. Each item must include the primary key field (`id`). **Request:** ```bash curl -X PUT ".../api/v1/acme-corp/sales/customers/bulk" \ -H "Authorization: Bearer sk_live_abc123..." \ -H "Content-Type: application/json" \ -d '[ { "id": 1, "name": "Alice Updated" }, { "id": 2, "email": "bob-new@example.com" } ]' ``` **Response (200):** ```json { "succeeded": 2, "failed": 0 } ``` ## Bulk Delete Delete multiple records by ID. **Request:** ```bash curl -X DELETE ".../api/v1/acme-corp/sales/customers/bulk" \ -H "Authorization: Bearer sk_live_abc123..." \ -H "Content-Type: application/json" \ -d '[1, 2, 3]' ``` **Response (200):** ```json { "succeeded": 3, "failed": 0 } ``` ## Partial Success Bulk operations always return **200 OK**, even when some items fail. The response reports how many succeeded and failed, with per-item error details. **Example — 1 of 3 items fails validation:** ```bash curl -X POST ".../api/v1/acme-corp/sales/customers/bulk" \ -H "Authorization: Bearer sk_live_abc123..." \ -H "Content-Type: application/json" \ -d '[ { "name": "Alice", "email": "alice@example.com" }, { "email": "missing-name@example.com" }, { "name": "Charlie" } ]' ``` **Response (200):** ```json { "succeeded": 2, "failed": 1, "errors": [ { "index": 1, "error": { "code": "VALIDATION_ERROR", "message": "Validation failed: 1 error(s)", "details": [{ "field": "name", "message": "name is required" }] } } ] } ``` ## Per-Item Error Codes Each failed item includes an error with one of these codes: | Code | Description | | ----------------------------- | ---------------------------------------------- | | `VALIDATION_ERROR` | Field validation failed (with `details` array) | | `NOT_FOUND` | Record with given ID not found (update/delete) | | `UNIQUE_CONSTRAINT_VIOLATION` | Duplicate value for a unique column | | `BAD_REQUEST` | Missing primary key or other request issue | | `INTERNAL_ERROR` | Unexpected database error | ## Error Responses (Non-Partial) These errors return standard error responses (not the bulk format): | Status | When | | ------- | ------------------------------------- | | **400** | Empty array or batch size exceeds 100 | | **401** | Missing or invalid API key | | **403** | Read-only API key or wrong workspace | | **404** | Entity not found | --- # API Settings Source: https://docs.schemastack.io/api/settings The Workspace API provides configuration at multiple levels to control behavior, security, and access. ## Settings Hierarchy Settings are resolved in order of specificity: 1. **Entity-level** — per-table overrides (highest priority) 2. **Workspace-level** — workspace-wide defaults 3. **System default** — hardcoded fallback (lowest priority) ## Workspace Settings Configure workspace-wide API defaults in the Admin app under **Workspace Settings > API Config**. ### Max Expand Depth Controls how deeply relationships can be expanded. Default: **3**. ``` GET /api/v1/acme-corp/sales/orders?expand=customer.address.country ``` If the depth exceeds the configured limit, expansion stops at the maximum level. ### CORS Allowed Origins Restrict which browser origins can call the API. By default, all origins are allowed (`*`). Set specific origins to lock down browser access: ```json { "maxExpandDepth": 5, "corsAllowedOrigins": [ "https://app.example.com", "https://dashboard.example.com" ] } ``` **Tip** CORS restrictions only affect browser-based requests. Server-to-server requests (cURL, Zapier, Make) are not affected by CORS. Setting `corsAllowedOrigins` to `null` or an empty array allows all origins. ## Entity Settings Configure per-entity API behavior in the Admin app under **View Settings > API Config**. These override workspace-level defaults. ### Default Expand Relationships to expand by default when no `?expand` parameter is provided. ```json { "defaultExpand": "customer,items.product" } ``` ### Max Expand Depth Overrides the workspace-level max expand depth for this specific entity. ### Default Fields Fields to return by default when no `?fields` parameter is provided. `null` means all fields. ```json { "defaultFields": "id,name,email,created_at" } ``` ### Expandable Relationships Whitelist of relationships that can be expanded. If set, only these relationships are allowed in `?expand`. `null` means all relationships are expandable. ```json { "expandableRelationships": "customer,items" } ``` Attempting to expand a non-whitelisted relationship returns `400 Bad Request`. ### Filterable Fields Whitelist of fields that can be used in `?filter[...]` parameters. If set, only these fields are allowed. `null` means all fields are filterable. ```json { "filterableFields": "name,email,created_at,status" } ``` ## Column API Visibility Individual columns can be hidden from API responses using the `apiVisible` flag. This is separate from the UI `visible`/`hidden` setting. - **`apiVisible: true`** (default) — column appears in API responses - **`apiVisible: false`** — column is excluded from API responses Use this to hide sensitive data (SSN, internal notes, etc.) from the REST API while keeping it visible in the admin UI. **Note** The primary key (`id`) column is always included in API responses regardless of the `apiVisible` setting. ## Public Access Entities can be configured for unauthenticated access, allowing requests without an API key. | Level | GET | POST | PUT | DELETE | |---|---|---|---|---| | **NONE** (default) | Requires API key | Requires API key | Requires API key | Requires API key | | **READ_ONLY** | Public | Requires API key | Requires API key | Requires API key | | **READ_WRITE** | Public | Public | Public | Public | ### Use Cases - **READ_ONLY**: Public product catalogs, reference data, company directories - **READ_WRITE**: Contact forms, feedback submissions, public registrations ### Behavior When a request arrives without an `Authorization` header: 1. The API checks the entity's `publicAccess` setting 2. If `NONE` — returns `401 Unauthorized` 3. If `READ_ONLY` — allows GET requests, returns `401` for mutations 4. If `READ_WRITE` — allows all operations When an `Authorization` header is present, the API key is always validated and its permissions take priority over the public access setting. **Warning** Public READ_WRITE access means anyone can create, update, and delete records without authentication. Use this only for entities where this is intentionally desired (e.g., form submissions). --- # Errors Source: https://docs.schemastack.io/api/errors When a request fails, the API returns a JSON error envelope with a machine-readable code and a human-readable message. ## Error Format ```json { "error": { "code": "NOT_FOUND", "message": "Entity not found: Customer" } } ``` Some errors include a `details` array with per-field information: ```json { "error": { "code": "VALIDATION_ERROR", "message": "Validation failed: 2 error(s)", "details": [ { "field": "email", "message": "must be a valid email address" }, { "field": "name", "message": "name is required" } ] } } ``` ## Status Codes | Code | Error Code | Description | | ------- | ----------------------------- | ------------------------------------------------------------- | | **400** | `BAD_REQUEST` | Invalid request body or parameters | | **400** | `FOREIGN_KEY_VIOLATION` | Referenced record does not exist | | **401** | `UNAUTHORIZED` | Missing, invalid, or expired API key | | **403** | `FORBIDDEN` | API key doesn't have permission for this operation | | **404** | `NOT_FOUND` | Entity or record not found | | **409** | `WORKSPACE_STATUS` | Workspace access mode prevents this operation | | **503** | `SCHEMA_MIGRATION_IN_PROGRESS`| A schema migration is running on the target table | | **409** | `UNIQUE_CONSTRAINT_VIOLATION` | Duplicate value for a unique column | | **422** | `VALIDATION_ERROR` | Request data fails constraint validation (includes `details`) | | **500** | `INTERNAL_ERROR` | Unexpected server error | ## Common Errors ### Invalid API Key ```json { "error": { "code": "UNAUTHORIZED", "message": "Invalid API key" } } ``` The key is missing, incorrect, or has been revoked. Check that you're including the full key in the `Authorization: Bearer` header. ### Expired API Key ```json { "error": { "code": "UNAUTHORIZED", "message": "API key has expired" } } ``` Create a new API key in your workspace settings. ### Read-Only Key ```json { "error": { "code": "FORBIDDEN", "message": "This API key has READ_ONLY permissions — data modifications are not allowed" } } ``` You're using a read-only key for a POST, PUT, or DELETE request. Create a key with **Read & Write** permissions. ### Wrong Workspace ```json { "error": { "code": "UNAUTHORIZED", "message": "API key is not valid for this workspace" } } ``` The API key belongs to a different workspace than the one in the URL. ### Schema Migration in Progress ```json { "error": { "code": "SCHEMA_MIGRATION_IN_PROGRESS", "message": "Table 'customers' is currently being migrated. Please retry after the migration completes." }, "tableName": "customers", "operationId": "abc-123", "impactLevel": "BLOCKING", "blocksReads": true, "blocksWrites": true, "retryAfterSeconds": 15, "estimatedCompletionAt": "2026-03-17T10:35:00Z" } ``` A schema change (column type change, adding constraints, etc.) is running on this table. The response includes a `Retry-After` header with the number of seconds to wait. **What's blocked depends on the migration:** | `blocksReads` | `blocksWrites` | Effect | |---|---|---| | `false` | `true` | Reads (GET) still work. Writes (POST/PUT/DELETE) return 503. | | `true` | `true` | All operations return 503. | Retry your request after the `retryAfterSeconds` period, or listen for the `view.column.schema.changed` SSE event to know when the migration completes. ### Workspace in Restricted Mode ```json { "error": { "code": "WORKSPACE_STATUS", "message": "Workspace is in read-only mode" } } ``` The workspace access mode is preventing write operations. An admin needs to change the workspace back to **Active** mode. ### Entity Not Found ```json { "error": { "code": "NOT_FOUND", "message": "Entity not found: Custoer" } } ``` The entity name in the URL doesn't match any entity in the workspace. Check for typos — entity names are case-sensitive. ### Record Not Found ```json { "error": { "code": "NOT_FOUND", "message": "Record not found: 42" } } ``` No record exists with the given ID. ### Unique Constraint Violation ```json { "error": { "code": "UNIQUE_CONSTRAINT_VIOLATION", "message": "A record with email 'alice@example.com' already exists" } } ``` You're trying to create or update a record with a value that already exists in a unique column. Use a different value or update the existing record instead. ### Foreign Key Violation ```json { "error": { "code": "FOREIGN_KEY_VIOLATION", "message": "Referenced customer_id '999' does not exist in customers" } } ``` The record references a related record that doesn't exist. Make sure the referenced record exists before creating the relationship. ### Validation Error ```json { "error": { "code": "VALIDATION_ERROR", "message": "Validation failed: 2 error(s)", "details": [ { "field": "email", "message": "must be a valid email address" }, { "field": "age", "message": "must be at least 0" } ] } } ``` The request data doesn't pass the entity's column constraints. Each failed field is listed in the `details` array. See [Validation](https://docs.schemastack.io/api/validation.md) for the full list of constraint types. --- # External Identity Providers Source: https://docs.schemastack.io/api/external-identity-providers External identity providers allow your end-users to authenticate with their own accounts (Auth0, Clerk, Firebase, etc.) and call the Workspace API directly — no SchemaStack account needed. This is ideal for building customer-facing SPAs where users sign in through your identity provider and interact with your workspace data. ## How It Works 1. Your end-user authenticates with your identity provider and receives a JWT 2. Your app sends API requests with that JWT in the `Authorization` header 3. SchemaStack validates the JWT against your provider's JWKS endpoint 4. If valid, the request is processed with the permissions you configured ```http GET /api/v1/acme-corp/sales/Customer Authorization: Bearer eyJhbGciOiJSUzI1NiIs... ``` SchemaStack inspects the `iss` (issuer) claim in the JWT to determine whether it's an internal SchemaStack token or an external provider token. External tokens are then validated against the JWKS keys published by your provider. ## Configuring an External Identity Provider 1. Open your workspace in the Admin app 2. Navigate to **Settings > Identity Provider** 3. Fill in the configuration: | Field | Required | Description | | ---------------------- | -------- | ------------------------------------------------------------------------------------- | | **Issuer URL** | Yes | Your provider's issuer URL — must exactly match the `iss` claim in your JWTs | | **JWKS URI** | No | URL to the provider's public keys. Leave blank to auto-discover via OIDC discovery | | **Audience** | No | Expected `aud` claim. Leave blank to skip audience validation | | **Default Permissions**| Yes | Permission level for all external users: **Read Only** or **Read & Write** | | **Enabled** | Yes | Toggle to enable/disable external authentication | 4. Click **Save Provider** **Tip: Auto-Discovery** If you leave the JWKS URI blank, SchemaStack will automatically discover it from your provider's `/.well-known/openid-configuration` endpoint. This works with all OIDC-compliant providers. ## JWT Requirements External JWTs must include the following claims: | Claim | Required | Description | | ----- | -------- | --------------------------------------------------------- | | `iss` | Yes | Must match the configured Issuer URL | | `sub` | Yes | Subject identifier — uniquely identifies the user | | `exp` | Yes | Expiration time — expired tokens are rejected | | `aud` | Conditional | Must contain the configured Audience value, if set | The JWT must be signed with an RSA or EC key that is published in the provider's JWKS endpoint. ## Row-Level Security (RLS) {#rls} By default, all external users see all data in the workspace. Row-Level Security lets you restrict each user to only their own rows based on JWT claims. ### How It Works You configure per-view rules that map a table column to a JWT claim. When an external user makes a request, SchemaStack automatically adds a WHERE clause to every query. **Example:** Your `orders` table has a `customer_id` column. You create an RLS policy: `customer_id = {jwt.sub}`. When user `auth0|abc123` calls the API, they only see orders where `customer_id = 'auth0|abc123'`. ### Configuring RLS Policies In the Spread app, open the view properties panel and add RLS rules: | Field | Description | |-------|-------------| | **Column** | The table column to filter on (e.g., `customer_id`) | | **JWT Claim** | The JWT claim to match against (e.g., `sub`, `email`) | | **Enabled** | Toggle to enable/disable the rule | You can add multiple rules per view — they are combined with AND logic. ### Enforcement by Operation | Operation | Behavior | |-----------|----------| | **List / Query** | WHERE clause automatically added — user only sees matching rows | | **Read by ID** | Returns 404 if the row doesn't belong to the user | | **Create** | RLS column is auto-set from the JWT claim. If the request includes a different value, it's rejected with 403 | | **Update** | Row must belong to the user (404 otherwise). Cannot change the RLS column to a different value (403) | | **Delete** | Row must belong to the user (404 otherwise) | ### RLS Scope RLS **only applies to external IdP tokens**. API keys and SchemaStack OAuth2 tokens are workspace-owner credentials — they bypass RLS and see all data. ### Example JWT Claims for RLS | Claim | Description | Example Value | |-------|-------------|---------------| | `sub` | Subject (user ID) | `auth0\|69d120454a06869772a10d84` | | `email` | User's email | `john@example.com` | | Custom claims | Provider-specific | `org_id`, `tenant_id`, etc. | **Tip: Index Your RLS Columns** For best performance, create a database index on the column used in your RLS policy (e.g., `customer_id`). Without an index, every query requires a full table scan. ## Permission Model All users authenticating via an external provider receive the **same permission level** — the Default Permissions you configured. There is no per-user permission differentiation for external users. | Permission | GET | POST | PUT | DELETE | | ---------------- | --- | ---- | --- | ------ | | **Read & Write** | Yes | Yes | Yes | Yes | | **Read Only** | Yes | No | No | No | **Warning** External users get broad access to all entities in the workspace. Use **Read Only** unless your application genuinely needs write access. ## Auth0 Setup Guide {#auth0} ### Step 1: Create an Auth0 Application 1. Log in to [Auth0 Dashboard](https://manage.auth0.com/) 2. Go to **Applications > Applications > Create Application** 3. Choose **Single Page Application** (for SPAs) or **Regular Web Application** (for server-side apps) 4. Give it a name (e.g., "My App - SchemaStack") 5. Click **Create** ### Step 2: Configure Application Settings In your new application's **Settings** tab: - **Allowed Callback URLs**: Add your app's callback URL (e.g., `https://myapp.com/callback`) - **Allowed Logout URLs**: Add your app's logout URL - **Allowed Web Origins**: Add your app's origin for CORS (e.g., `https://myapp.com`) ### Step 3: Create an API **Danger: Required** Without an API, Auth0 returns an opaque token instead of a JWT. SchemaStack cannot validate opaque tokens — this step is mandatory. 1. Go to **Applications > APIs > Create API** 2. Set a name (e.g., "SchemaStack Workspace API") 3. Set an **Identifier** — this becomes the `aud` claim (e.g., `https://api.myapp.com`) 4. Click **Create** ### Step 3b: Authorize Your Application After creating the API, you need to grant your application access: 1. Go to **Applications > APIs > your new API > Application Access** 2. Find your application in the list 3. If **User Access** shows "UNAUTHORIZED", click **Edit** and enable it 4. Your application must be **AUTHORIZED** for User Access to request JWTs ### Step 4: Note Your Auth0 Values From your Auth0 dashboard, collect: | Value | Where to Find | Example | | ------------- | -------------------------------------- | ----------------------------------------- | | **Domain** | Applications > Your App > Settings | `casino.eu.auth0.com` | | **Audience** | Applications > APIs > Your API > Identifier | `https://api.myapp.com` | **Warning: Important** Auth0 shows the Domain **without** `https://` and **without** a trailing `/`. You need to add both when entering the Issuer URL in SchemaStack: - Auth0 shows: `casino.eu.auth0.com` - You enter: `https://casino.eu.auth0.com/` This must match exactly — Auth0 JWTs contain `"iss": "https://casino.eu.auth0.com/"` with both the scheme and trailing slash. **Note** The JWKS URI is automatically published by Auth0 at `https://{your-domain}/.well-known/jwks.json` — you don't need to set it manually. ### Step 5: Configure SchemaStack In your workspace's **Settings > Identity Provider**: | Field | Value | | ----------------------- | ---------------------------------------------------------- | | **Issuer URL** | `https://casino.eu.auth0.com/` *(add https:// and trailing /)* | | **JWKS URI** | *(leave blank — auto-discovered)* | | **Audience** | `https://api.myapp.com` *(if you created an API)* | | **Default Permissions** | Read Only *(recommended to start)* | | **Enabled** | On | ### Step 6: Get a Token and Test Using your SPA or a test script, obtain a JWT from Auth0: ```javascript // Example using Auth0 SPA SDK const token = await auth0.getTokenSilently({ authorizationParams: { audience: 'https://api.myapp.com', }, }); ``` Then call the SchemaStack Workspace API: ```bash curl -H "Authorization: Bearer " \ https://api.schemastack.io/api/v1/acme-corp/sales/Customer ``` **Expected results:** - Valid token: `200` with data - Expired token: `401 Unauthorized` - Wrong issuer: `401 Unauthorized` - Provider disabled: `401 Unauthorized` ## Clerk Setup Guide {#clerk} ### Step 1: Get Your Clerk Values 1. Log in to [Clerk Dashboard](https://dashboard.clerk.com/) 2. Go to **API Keys** 3. Find your **Frontend API URL** — this is your issuer | Value | Where to Find | Example | | -------------- | -------------------------------- | ------------------------------------------- | | **Issuer URL** | Clerk Dashboard > API Keys | `https://myapp.clerk.accounts.dev` | ### Step 2: Configure SchemaStack | Field | Value | | ----------------------- | ----------------------------------------------- | | **Issuer URL** | `https://myapp.clerk.accounts.dev` | | **JWKS URI** | *(leave blank — auto-discovered)* | | **Audience** | *(leave blank — Clerk doesn't use aud by default)* | | **Default Permissions** | Read Only | | **Enabled** | On | ### Step 3: Get a Token and Test ```javascript // Example using Clerk React SDK const { getToken } = useAuth(); const token = await getToken(); ``` ## Firebase Auth Setup Guide {#firebase} ### Step 1: Get Your Firebase Values 1. Open [Firebase Console](https://console.firebase.google.com/) 2. Your issuer URL follows the pattern: `https://securetoken.google.com/{project-id}` | Value | Pattern | Example | | -------------- | ----------------------------------------------------- | ---------------------------------------------------- | | **Issuer URL** | `https://securetoken.google.com/{project-id}` | `https://securetoken.google.com/my-app-12345` | | **Audience** | Your Firebase project ID | `my-app-12345` | ### Step 2: Configure SchemaStack | Field | Value | | ----------------------- | -------------------------------------------------------- | | **Issuer URL** | `https://securetoken.google.com/my-app-12345` | | **JWKS URI** | *(leave blank — auto-discovered)* | | **Audience** | `my-app-12345` | | **Default Permissions** | Read Only | | **Enabled** | On | ### Step 3: Get a Token and Test ```javascript // Example using Firebase JS SDK const token = await firebase.auth().currentUser.getIdToken(); ``` ## Troubleshooting ### 401 — "No external identity provider configured for this workspace" The workspace doesn't have an external IdP configured, or it's disabled. Check **Settings > Identity Provider**. ### 401 — "Token issuer does not match configured identity provider" The `iss` claim in the JWT doesn't match the **Issuer URL** you configured. Common issues: - Missing trailing slash (Auth0 includes one: `https://myapp.us.auth0.com/`) - Wrong region or domain - Using a different Auth0 tenant ### 401 — "Token audience does not match configured audience" The `aud` claim in the JWT doesn't include your configured audience. Make sure: - You're requesting the correct audience when obtaining the token - The audience value matches exactly (case-sensitive) ### 401 — "Token has expired" The JWT's `exp` claim is in the past. Tokens have a limited lifetime (typically 1 hour for Auth0). Your app should refresh tokens automatically. ### 401 — "Failed to discover JWKS endpoint for issuer" SchemaStack couldn't reach the provider's `/.well-known/openid-configuration` endpoint. Check that the Issuer URL is correct and the provider is accessible. ### 403 — "READ_ONLY permissions — data modifications are not allowed" The external IdP is configured with **Read Only** permissions. Change to **Read & Write** in workspace settings if you need write access. --- # Data Platform Overview Source: https://docs.schemastack.io/app/overview The data platform is where you work with your data day-to-day. It provides a spreadsheet-like interface with the structure and validation of a database. ## Interface Layout The main interface consists of: - **Toolbar** — Menu bar with File, Tools, and Help menus, plus your user profile - **Table Toolbar** — Row count, add row button, filter/sort controls, and preset management - **Data Table** — Your data in a scrollable, editable grid - **Properties Panel** — Column and view settings (right sidebar) - **Activity Panel** — Real-time activity feed showing changes by your team ## Working with Data ### Adding Rows Click the **Add Row** button in the table toolbar. A dialog will appear with fields for each column in the current view. Required fields are marked, and constraints are validated before saving. ### Editing Cells Click on any cell to edit it inline. The cell editor adapts to the column type — text fields, number inputs, date pickers, dropdowns, and more. Press **Enter** to save, **Escape** to cancel. ### Selecting Rows Click the checkbox on any row to select it. You can: - Select individual rows for bulk operations - Use "Select all" to select all rows on the current page - Select all rows matching the current filter ### Bulk Operations With rows selected, a floating action bar appears at the bottom with options to: - **Edit** — Change a field's value across all selected rows - **Export** — Download selected rows - **Delete** — Remove selected rows (with confirmation) ## Real-Time Updates SchemaStack uses server-sent events (SSE) to keep your view synchronized with other users. When someone else adds, edits, or deletes data, you'll see the changes appear automatically. The connection status is shown as a banner: - **Connected** — No banner, everything is in sync - **Reconnecting** — A brief interruption, reconnecting automatically - **Disconnected** — Connection lost, click "Reconnect" to restore ## Activity Panel The Activity Panel (right sidebar) shows a real-time feed of changes happening in the current workspace. Each entry includes: - **Event type** — Row created, updated, or deleted; column added or modified; view changed - **Timestamp** — When the event occurred - **User** — Who made the change Use the activity panel to stay aware of what your team is doing, review recent changes, or track down when a specific change was made. ## Keyboard Shortcuts | Shortcut | Action | | -------- | ---------------- | | `Ctrl+R` | Refresh data | | `Enter` | Save cell edit | | `Escape` | Cancel cell edit | --- # Views Source: https://docs.schemastack.io/app/views A view is a configured perspective on your workspace data. Views let different team members see the same data in different ways — different columns, filters, and sorting for different workflows. ## Creating a View 1. Open the **File** menu in the toolbar 2. Select **New View** 3. Enter a name for your view 4. The new view will be created and opened ## Switching Views Views appear as tabs. Click a tab to switch between views. Each view remembers its own column configuration, filters, and sort rules. ## Renaming a View Double-click on a view tab name to enter edit mode. Type the new name and press **Enter** to save. ## View Properties Open the properties panel (right sidebar) and select the **View** tab to see and edit: - **Name** — the display name of the view - **Description** — optional description for your team - **Addable** — whether users can add new rows - **Entity Constraints** — cross-field validation rules ## Column Visibility Not every column needs to be visible in every view. Use the properties panel to show or hide columns, and drag to reorder them. ## Column Locking Lock columns to keep them visible while scrolling horizontally. Locked columns stay pinned to the left side of the table. ## Private Views **Note: Coming soon** Private views are planned but not yet available. By default, all workspace members can see all views through role inheritance. Private views break this inheritance — only users with explicit view membership can access them. **Use cases:** - **Confidential data** — HR compensation views that only HR admins should see - **Work in progress** — draft views you're still configuring - **Role-specific** — different departments see different views When a view is marked as private: - It won't appear in the view tabs for non-members - Workspace-level role inheritance is bypassed - Only users explicitly added to the view can access it - Organization Owners and Admins retain access for management purposes ## Archiving a View Archive a view to remove it from the tab bar without permanently deleting it. 1. Right-click on the view tab (or click the tab menu icon) 2. Select **Archive** 3. The view is removed from the tab bar but preserved in the system To restore an archived view: 1. Open the **File** menu 2. Select **Archived Views** 3. Click **Restore** next to the view you want to bring back ## Duplicating a View Duplicate a view to create a copy with the same column configuration, filters, and sort rules. 1. Right-click on the view tab (or click the tab menu icon) 2. Select **Duplicate** 3. A new view named "Copy of [original name]" is created and opened --- # Columns & Schema Source: https://docs.schemastack.io/app/columns Columns define the structure of your data. Each column has a type, a display widget, and optional constraints. ## Adding a Column 1. Open the **File** menu 2. Select **New Column** 3. The **Insert Column** dialog opens with two tabs: - **Normal column** — a scalar column backed by a database column (Text, Number, Date, Boolean, Formula, etc.). Pick a widget type and configure schema options. - **Relationship column** — a column backed by a foreign key to another entity. Browse the relationship tree, pick a target column, and the widget type is inferred from the target column's database type (you can override it). 4. Fill in the column name and tab-specific settings, then click **Create Column** (for Normal columns) or **Add Column** inside the picker (for Relationship columns). 5. The column will be added to the current view. All column types (regular, computed, and relationship) are created through the same `POST /api/columns` endpoint. The presence of specific fields determines the column type: - **Regular column** — `displayName` + `widgetType` (creates a physical database column) - **Computed column** — includes `formula` field (no database column, evaluated at query time) - **Relationship column** — includes `targetColumnName` + `relationshipPath` (displays data from a related entity) ## Column Types SchemaStack supports a range of data types: | Type | Description | Widget Examples | | ------------ | ------------------------------ | --------------------------- | | String | Short text | Text input | | Text | Long text | Textarea, Rich Text editor | | Integer | Whole numbers | Number input | | Decimal | Fractional numbers | Number input | | Boolean | True/false | Checkbox, toggle | | Date | Date values | Date picker | | DateTime | Date and time | DateTime picker | | Email | Email addresses | Email input with validation | | URL | Web addresses | URL input with validation | | Phone | Phone numbers | Phone input with formatting | | Select | One of a fixed set of options | Dropdown with labels | | Multi-select | Several of a fixed option set | Multi-select dropdown | | Image | Image files | Image thumbnail, upload | | File | File attachments | File link, upload | | UUID | Unique identifier | Read-only text | Currency and percentage rendering are **display options** on Integer/Decimal columns (see [Display Options](#display-options)), not separate types — the stored value stays a plain number. ## Column Properties Select a column in the properties panel to configure: - **Name** — Display label - **Description** — Help text shown to users - **Widget Type** — How the data is displayed and edited - **Required** — Whether the field must have a value - **Default Value** — Pre-filled value for new rows - **Hidden** — Hide from the current view without deleting - **Constraints** — Validation rules (e.g., Not Blank, Max Length, Email) can be set when creating a column ## Reordering Columns Drag column headers to reorder them. The new order is saved automatically and synced to other users. ## Resizing Columns Drag the right edge of a column header to resize it. ## Rich Text Editing Text columns can use the **Rich Text** widget, which provides a TipTap-based editor with a formatting toolbar. Supported formatting: - **Bold**, *Italic*, and ~~Strikethrough~~ - Ordered and unordered lists - Links - Inline code and code blocks Rich text content is stored as HTML. When switching a Text column from a plain textarea to the Rich Text widget, existing plain text is preserved. ## File & Image Columns **Image** and **File** columns let you attach files to rows. - Click the cell to open the upload dialog, or drag and drop a file onto it - **Image columns** display a thumbnail preview in the cell. Click the thumbnail to view full size - **Image cropping** — After uploading an image, use the built-in cropping tool to adjust the visible area before saving - **File columns** display the file name as a link. Click to download - **Automatic cleanup** — When you replace a file or image, the old file is automatically removed from storage. Clearing a file cell also removes the stored file Supported image formats: JPEG, PNG, GIF, WebP, SVG. ### Image Mode Image columns support two modes, configurable via the column's widget options: - **Managed** (default) — Images are uploaded through SchemaStack. Thumbnails are pre-generated on upload for fast display. Cell values are stored as JSON with file metadata (key, thumbnail key, file name, MIME type, size). - **URL** — The column contains plain image URLs (e.g. `https://example.com/photo.jpg`). Thumbnails are generated lazily on first view and cached for subsequent requests. Use this mode when your data already contains image URLs from external sources. The cache refreshes weekly, so if an image at the same URL changes, the thumbnail updates within 7 days. ## Computed Columns Computed columns calculate values from other columns using SQL expressions (e.g., `price * quantity` or `CONCAT(first_name, ' ', last_name)`). They have no physical database column — values are calculated at query time. See [Computed Columns](https://docs.schemastack.io/app/computed-columns.md) for full documentation. ## Display Options Widgets can carry display-only formatting in their options — the stored value and editing never change: - **Select** — value/label pairs; the grid shows the label ("African" instead of `african`), editing offers a dropdown. Works on text, integer, and yes/no storage without a migration. - **Numbers** — `currency` (thousands grouping, 2 decimals), `percent`, or `number` with configurable decimals, prefix, and suffix. - **Dates** — `medium` ("16 Apr 2014") or `us` ("04-16-2014"). - **Text** — casing (UPPERCASE / lowercase / Title Case) plus a prefix/suffix shown around non-empty values. Applies to plain cells and single-value relationship links; multi-value chips keep their raw labels. Configure these in the column properties panel, or via the API/MCP with `widgetOptions`. ## Form Visibility Grid visibility and form visibility are separate. Each column has a **Form visibility** setting: | Setting | Meaning | |---------|---------| | Same as table (default) | Hidden columns stay out of forms — the historical behavior | | Always in forms | Editable in create/edit forms even when hidden in the grid | | Never in forms | Shown in the grid but never editable through forms | This mirrors admin apps where a curated grid hides fields that staff still edit through the record form. ## API Keys (dataKey) Every column has a stable `dataKey` — the key it uses in API payloads, SQL aliases, and exports. Keys are generated at creation (relationship columns derive theirs from the relationship name) and can be **renamed explicitly** via the column-update API when you want friendlier payload keys. Saved filters, presets, and style rules reference columns internally by ID and are unaffected; external API consumers see the new key. ## Aggregate Columns A relationship column over a **collection** (one-to-many or many-to-many, anywhere in a multi-hop path) can summarize the related records: - **Aggregate function** — count, sum, average, min, max, or join into one cell. - **Member expression** — a formula over the *related* entity's columns used for each record, e.g. `CONCAT('APT-', LPAD(invoice_id, 5, '0'), '/', course_year)`. - **Row filter** — a condition on the related records, e.g. `sent_date IS NOT NULL`. - **Separator** — the join string for concatenated values (default `, `). SchemaStack generates the underlying query in your database, so the grid, API, exports, and sorting all agree. Expressions are validated like formulas and are scoped to the related entity — no subqueries, no cross-relationship references. ## Display Groups Display groups let you combine multiple columns into a single cell, separated by a configurable character (e.g., a space, dash, or comma). To create a display group: 1. Open the **Properties Panel** and select a column 2. Click **Create Display Group** 3. Select the columns to include in the group 4. Set the **separator** character 5. The grouped columns appear as a single cell in the view Editing a grouped cell opens a form with each column as a separate field. --- # Computed Columns Source: https://docs.schemastack.io/app/computed-columns Computed columns calculate values from other columns in the same row using SQL expressions. They behave like spreadsheet formulas — the value is computed at query time and never stored in the database. ## How It Works A computed column defines a SQL expression that references other columns on the same table. When you query data, the database evaluates the expression for each row and returns the result alongside the regular column values. For example, if your table has `price` and `quantity` columns, you can create a computed column with the formula `price * quantity` that displays the total. Computed columns: - Have **no physical database column** — nothing is altered in your database - Are **always read-only** — values are calculated, not entered - Are **evaluated by the database engine** — fast and accurate - Are **visible in the API** — included in query responses just like regular columns - Are **per-view** — different views of the same table can have different computed columns ## Creating a Computed Column Use the standard column creation endpoint. Include a `formula` field to make it a computed column: ``` POST /api/columns ``` ```json { "viewUuid": "your-view-uuid", "displayName": "Total", "formula": "price * quantity", "widgetType": "DECIMAL" } ``` No separate endpoint is needed — the presence of a `formula` field automatically creates a computed column instead of a physical database column. This is the same `POST /api/columns` endpoint used for regular columns and [relationship columns](https://docs.schemastack.io/app/relationships.md). | Field | Required | Description | |-------|----------|-------------| | `displayName` | Yes | The column header shown in the UI | | `formula` | Yes | SQL expression referencing column names from the same table | | `widgetType` | No | How to render the result: `STRING`, `INTEGER`, `DECIMAL`, `BOOLEAN`, etc. Defaults to `STRING` | ## Formula Types ### CONCAT — String Concatenation Combine multiple text columns into one. ```json { "displayName": "Full Name", "formula": "CONCAT(first_name, ' ', last_name)", "widgetType": "STRING" } ``` ### ARITHMETIC — Calculations Basic math using `+`, `-`, `*`, `/`. ```json { "displayName": "Line Total", "formula": "unit_price * quantity * (1 - discount)", "widgetType": "DECIMAL" } ``` ### CONDITIONAL — If/Then Logic Use SQL `CASE WHEN` for conditional values. ```json { "displayName": "Status Label", "formula": "CASE WHEN is_active = true THEN 'Active' ELSE 'Inactive' END", "widgetType": "STRING" } ``` ### COALESCE — Fallback Values Return the first non-null value. ```json { "displayName": "Display Name", "formula": "COALESCE(nickname, first_name, email)", "widgetType": "STRING" } ``` ## Supported SQL Functions Formulas support standard ANSI SQL functions that work across PostgreSQL and MySQL: | Category | Functions | |----------|-----------| | **String** | `CONCAT`, `UPPER`, `LOWER`, `TRIM`, `LENGTH`, `SUBSTRING`, `REPLACE` | | **Numeric** | `ABS`, `ROUND`, `CEIL`, `FLOOR`, `MOD`, `GREATEST`, `LEAST` | | **Null handling** | `COALESCE`, `NULLIF` | | **Conditional** | `CASE WHEN ... THEN ... ELSE ... END` | | **Type conversion** | `CAST(column AS type)` | | **Date** | `NOW()`, `CURRENT_DATE`, `CURRENT_TIMESTAMP` | ## Updating a Computed Column Use the standard column update endpoint: ``` PATCH /api/columns/item/{columnUuid} ``` ```json { "formula": "price * quantity * (1 - discount)", "displayName": "Net Total" } ``` Changes take effect on the next query — no database migration needed. ## Deleting a Computed Column Use the standard column delete endpoint: ``` DELETE /api/columns/item/{columnUuid} ``` Removes the computed column from the view. No database changes are made. ## Limitations ### Read-Only Computed column values cannot be edited directly. They are calculated from other columns. ### No Aggregate Functions Formulas operate on a single row. Aggregate functions like `SUM`, `COUNT`, `AVG` across multiple rows are not supported. Each formula evaluates independently per row. ### Formula Must Be Valid SQL The formula is embedded as a SQL expression in the database query. If the expression is invalid, queries to the view will fail. Test formulas with simple expressions first. ### SQL Injection Protection Formulas pass two gates before saving. First, a structural check rejects: - Subqueries and statements (`SELECT`, `FROM`, `WHERE`, DDL, DML) - Comments (`--`, `/* */`), statement separators (`;`), square brackets - Execution commands (`EXEC`, `EXECUTE`), `ARRAY`/`VALUES` constructs - Unbalanced parentheses, unbalanced `CASE`/`END`, unterminated string literals Words inside single-quoted literals are treated as data — `'set menu'` or `'price with surcharge'` are fine. Second, the expression is validated **against your own database** (vendor-aware), so anything structurally valid but semantically wrong — a typo'd column, a vendor-specific function that doesn't exist — is rejected at save time instead of breaking the view later. Formulas can also reference a directly related record's fields (`category.name`) — see [Cross-Entity References](https://docs.schemastack.io/guide/formula-columns.md#cross-entity-references). ## Filtering and Sorting Computed columns support filtering and sorting just like regular columns. The formula expression is evaluated by the database, so standard filter operators work: ``` GET /api/data/{viewSlug}?filter[lineTotal.gte]=100&sort=lineTotal,desc ``` This translates to `WHERE (price * quantity) >= 100 ORDER BY (price * quantity) DESC` in the generated SQL. **Note: Performance Note** Filtering and sorting on computed columns may be slower than on indexed columns, since the database evaluates the expression for every row. For frequently filtered computed values, consider creating a database-level generated column or index. ## Cross-Table Formulas Computed columns can reference data from related tables using SQL subqueries. Hibernate's `@Formula` evaluates the subquery for each row in the SELECT clause. ```json { "displayName": "Customer Name", "formula": "(SELECT c.name FROM customers c WHERE c.id = customer_id)", "widgetType": "STRING" } ``` **Tip** For better performance on frequently accessed cross-table values, consider using [relationship columns](https://docs.schemastack.io/app/relationships.md) instead, which use JOINs rather than subqueries. ## Schema Sync Behavior Computed columns are **preserved during schema sync**. When you import or re-sync a workspace: - Existing computed columns are not deleted (they have no database counterpart to compare against) - Computed columns are excluded from drift detection (they can't drift — there's nothing in the database to drift from) - Computed columns are excluded from the schema hash (so adding/removing them doesn't trigger a false "drift detected" warning) ## API Response Format Computed column values appear in query responses alongside regular columns: ```json { "id": 1, "firstName": "John", "lastName": "Doe", "fullName": "John Doe", "price": 29.99, "quantity": 3, "lineTotal": 89.97 } ``` The `fullName` and `lineTotal` fields are computed — they don't exist in the database but appear in every query response. --- # Relationships Source: https://docs.schemastack.io/app/relationships Relationships connect rows in one table to rows in another. SchemaStack discovers relationships from your database schema and lets you add them as columns to your views. ## Discovering Relationships To add a relationship column: 1. Open the **Properties Panel** (right sidebar) 2. Click **Add Relationship Column** 3. Browse the **discovery tree** — it shows all tables related to your current workspace, organized by foreign key paths 4. Select the relationship you want to add 5. Configure the edit mode and display settings (see below) 6. The relationship column appears in your view The discovery tree follows foreign keys in your database, so only valid relationships are shown. ## Relationship Edit Modes Each relationship column uses one of three edit modes that control how related records are created and managed: | Mode | Behavior | Use Case | | --------------- | ------------------------------------------------ | ------------------------------------------- | | **Reference** | Link to an existing row in the related table | Order → Customer (customer already exists) | | **Owned** | Create and manage the related row inline | Order → Shipping Address (belongs to order) | | **Association** | Many-to-many link through a join table | Student ↔ Course (enrolled in multiple) | **Note** OneToMany columns are always **read-only** from the parent view — they display aggregated data from child records. Edit child records in their own view. ### Reference Mode Reference mode links to existing rows. When editing a reference cell, you get a searchable dropdown of rows from the related table. - The related row must already exist — you cannot create new rows from the reference field - Clearing the reference removes the link but does not delete the related row ### Owned Mode Owned mode lets you create and edit the related row directly within the parent row's form. The related row's lifecycle is tied to the parent. - Adding a parent row can simultaneously create the owned row - Deleting the parent row may cascade to the owned row (depending on your database constraints) - Edit the owned row's fields inline without leaving the parent form ### Association Mode Association mode handles many-to-many relationships through an intermediate join table. - Select multiple related rows from a searchable list - Add or remove associations without affecting the related rows themselves - The join table is managed automatically ### Read-Only (Display Only) Separate from the edit mode, any relationship column can be marked **read-only** — the value is displayed but cannot be edited regardless of the mode you chose. - **Default:** new relationship columns are **editable** with the edit mode you selected (Reference by default). - **Opt-in:** toggle **Read-only** in the column properties to disable editing. The dropdown, inline editor, or multi-select editor is replaced with a plain display of the current value. - Read-only is useful when the related data should be visible but protected — for example, showing a customer's plan tier on an order without letting order editors change the plan. **Note** Read-only used to be the default for all relationship columns. It is now an explicit opt-in, so columns you add via the discovery tree are editable unless you choose otherwise. ## Self-Referential Relationships A table can point at itself: categories with a parent category, employees with a manager, threaded comments replying to other comments. **Creating one:** in the relationship picker's **Other Entities** section, your view's own table is offered first, labelled **"this table"**. Pick the referenced column (usually the primary key), choose a **Display Field** such as `name`, and name the column — SchemaStack adds the foreign-key column (for example `parent_id`) and the relationship, exactly like any other reference. **Looking up along the chain:** multi-hop lookups work through a self-reference — a category row can show its parent's name *and* its grandparent's name (`parent.parent.name`). The picker's drill-down offers each further hop up to **five levels deep**. **The reverse side** is created automatically, like any other relationship: a "rows pointing at this one" OneToMany appears on the same table, so a category can show a count of its direct children. Two limits to know: - **Self many-to-many is not offered.** A join table between a table and itself would derive two identical column names; the "Multiple links" toggle is hidden when the target is the view's own table. Model it with an explicit junction entity instead (see *Power User: Intermediate Entities* below). - **Lookups follow the chain a fixed number of hops** (five in the picker). Deeper hierarchies exist happily in your data — the picker just won't build a single column that reaches further. "Show all descendants" is a recursive query, which is a different feature. ## Many-to-Many Relationships Many-to-many (M2M) relationships link records across two tables where each record on either side can relate to multiple records on the other side. Common examples: Users ↔ Roles, Posts ↔ Tags, Students ↔ Courses. ### Creating a M2M Relationship 1. Open the **Properties Panel** → **Add Relationship Column** 2. Under **Other Entities**, expand the target entity (e.g., "Role") 3. Select any column (e.g., the primary key) 4. Check **Multiple links (many-to-many)** 5. Set the **Display Field** (e.g., "name") — this controls what appears in the chips 6. Click **Add Column** SchemaStack creates a join table automatically (e.g., `users_roles`) with two foreign key columns and a composite primary key. No manual table creation needed. ### How M2M Columns Display M2M columns display as **colored chips** in the spreadsheet grid — each chip represents one linked record. For example, a user's "Roles" column might show: `Admin` `Editor`. ### Editing M2M Associations Click on a M2M cell to open the **multi-select editor**: - **Search** — filter target records by the display field - **Checkboxes** — toggle associations on/off instantly (each toggle saves immediately) - **Chips** — selected items appear as removable chips at the top - **Pagination** — "Load more" for large datasets Changes are saved instantly per toggle — there is no "Apply" button. ### Power User: Intermediate Entities For M2M relationships that need extra columns (e.g., `assigned_at`, `permissions` on a User→Role link), create a junction entity explicitly: 1. Create a new entity (e.g., "UserRole") with two foreign key columns (`user_id`, `role_id`) plus your extra columns 2. Each FK is a regular ManyToOne relationship 3. Use Reference mode for editing each side This approach gives you full control over the junction entity while still allowing CRUD on the intermediate data. ## OneToMany Relationships OneToMany relationships show data from child records that reference the current row. For example, a Customer view can show all Orders for each customer. ### How OneToMany Columns Display OneToMany columns support two display modes: - **Count** (default) — shows the number of related records (e.g., `5`) - **Values** — shows related records as colored chips (e.g., `ORD-001` `ORD-002`) ### Adding a OneToMany Column 1. Open the **Properties Panel** → **Add Relationship Column** 2. In **Existing Relationships**, find the OneToMany relationship (labeled with `1:N`) 3. Select a display column from the target entity 4. The column appears showing aggregated values or count ### Changing Display Mode After adding a OneToMany column: 1. Click the column header to open column properties 2. Find the **Display Mode** dropdown 3. Switch between **Count** and **Values (chips)** ### Rollup Functions For OneToMany columns in Count mode, you can change the aggregate function: - **Count** — number of related records - **Sum** — sum of a numeric field across related records - **Average** — average of a numeric field - **Minimum** — smallest value - **Maximum** — largest value Set the aggregate function in the column properties panel. OneToMany columns are **read-only** from the parent view. Edit child records in their own view. ## Relationship Discovery The relationship picker helps you find and add relationship columns. It has two sections: ### Existing Relationships Shows relationships already defined in your database schema (foreign keys). Each relationship is labeled with its cardinality: - No label — Many-to-One (most common) - `1:1` — One-to-One - `1:N` — One-to-Many ### Other Entities Shows all other tables in your workspace. You can: - **Direct link** — click a column to create a new foreign key relationship - **Through junction** — expand a junction table's relationships to create a Many-to-Many link via an existing join table Junction tables (tables whose primary key columns are all foreign keys) are detected automatically and labeled with `M2M`. ### Search Filter Use the search box at the top to filter entities by name — useful in workspaces with many tables. ## Configuring Relationship Columns After adding a relationship column, configure how it displays and behaves: - **Widget Type** — How the value is displayed in the cell (and, for Owned mode, the widget used for inline editing). See **Widget Types for Relationship Columns** below. - **Display Field** — Which field from the related table is shown in the cell (e.g., a customer's name instead of their ID) - **Search Fields** — Which fields are searched when filtering the dropdown (e.g., search by name and email) - **Inline Edit Fields** — In owned mode, which fields of the related row are editable inline These settings are configured per-column in the Properties Panel. ### Widget Types for Relationship Columns Each relationship column has a **widget type** that controls how the value is rendered. When you add a relationship column, SchemaStack infers the best widget type from the underlying database column's type (the same way schema sync picks widgets for regular columns) — for example, a `DATE` column comes in with the Date widget, a `VARCHAR` with Text, and so on. You can override the inferred choice in the Properties Panel. What the widget type affects depends on the edit mode: | Mode | Widget type affects | | --------------- | ---------------------------------------------------------------------- | | **Reference** | **Display only.** Editing is always a dropdown picker that swaps the FK. | | **Association** | **Display only.** Editing is always a multi-select chip editor. | | **Owned** | **Display AND inline-edit widget.** The widget you pick is what users see when editing. | Only widgets compatible with the underlying column's database type are available (e.g., you can pick Text, Number, or Date for a DATE column, but not Email or URL). SchemaStack filters the choices automatically. **Tip** If you want an Owned relationship column to be edited with a date picker rather than a plain text input, change the widget type to **Date** (or whichever type fits) in the Properties Panel. Leaving it at the inferred default is correct in most cases. ## Referential Actions (Cascade Rules) Referential actions control what happens to rows in the current table when the referenced (parent) row is deleted or updated. Configure these in the **Properties Panel** under **Referential Actions** for any foreign key column. | Action | On Delete | On Update | | --------------- | -------------------------------------------------- | ------------------------------------------------- | | **No Action** | Block delete if child rows exist (default) | Block update if child rows reference the old key | | **Cascade** | Delete all child rows when the parent is deleted | Update the FK in all child rows to the new key | | **Set Null** | Set the FK column to NULL in child rows | Set the FK column to NULL in child rows | | **Restrict** | Same as No Action (fails immediately) | Same as No Action (fails immediately) | | **Set Default** | Set the FK column to its default value | Set the FK column to its default value | **Warning** Changing a referential action requires a database migration — the foreign key constraint is dropped and re-created. This is safe but may briefly lock the table on large datasets. **Tip** **Set Null** requires the FK column to be nullable. SchemaStack will warn you if the column is NOT NULL. ## Further Reading - [Expanding Relationships (API)](https://docs.schemastack.io/api/relationships.md) — How to query relationship data via the REST API --- # Constraints & Validation Source: https://docs.schemastack.io/app/constraints Constraints enforce data quality at the schema level. When a user enters data that violates a constraint, they see a clear error message before the data is saved. ## Column Constraints Column constraints validate a single field's value. Select a column in the properties panel to manage its constraints. ### Available Types | Constraint | Applies To | Description | | ---------------- | ---------- | ----------------------------------- | | Required | All types | A value must be provided on every write, even though the database column stays nullable | | Not Blank | Text | Value cannot be empty or whitespace | | Min | Numbers | Value must be at least this number | | Max | Numbers | Value must be at most this number | | Min Length | Text | Minimum number of characters | | Max Length | Text | Maximum number of characters | | Pattern | Text | Must match a regular expression | | Email | Text | Must be a valid email address | | URL | Text | Must be a valid URL | | Positive | Numbers | Must be greater than zero | | Negative | Numbers | Must be less than zero | | Positive or Zero | Numbers | Must be zero or greater | | Negative or Zero | Numbers | Must be zero or less | | Past | Dates | Must be in the past | | Future | Dates | Must be in the future | | Past or Present | Dates | Must be today or in the past | | Future or Present| Dates | Must be today or in the future | **REQUIRED** deserves a special mention: it makes a field mandatory on every write (UI, API, MCP) while the database column stays nullable. Use it when legacy rows predate the rule or the column has a database default — new writes must provide a value, old rows stay valid, and no migration runs. Inserts that rely on a database default still pass; clearing the field on edit is rejected. ### Adding a Column Constraint 1. Select a column in the properties panel 2. Scroll to the **Constraints** section 3. Choose a constraint type from the dropdown 4. Configure the value (if required) and error message 5. Click **Add** ### Custom Error Messages Each constraint has an error message shown to users when validation fails. Write clear, actionable messages like: - "Email address is required" (not "Validation error") - "Price must be greater than 0" (not "Invalid value") ## Entity Constraints Entity constraints validate relationships between multiple columns. Manage them in the **View** tab of the properties panel. ### Available Types | Constraint | Description | | --------------------------- | ------------------------------------------------------------- | | Unique Composite | Combination of selected fields must be unique across all rows | | Field Less Than | First field must be less than second field | | Field Less Than or Equal | First field must be less than or equal to second field | | Field Greater Than | First field must be greater than second field | | Field Greater Than or Equal | First field must be greater than or equal to second field | | Fields Not Equal | Two fields must have different values | | Conditional Required | Field is required when another field has a value | | At Least One Required | At least one of the selected fields must have a value | | All or None | All selected fields must have values, or none of them | ### Example: Date Range Validation To ensure a "Start Date" is always before an "End Date": 1. Open the **View** tab in the properties panel 2. Add an entity constraint of type **Field Less Than** 3. Select "Start Date" as the first field and "End Date" as the second 4. Set the error message to "Start date must be before end date" ### Enabling and Disabling Each constraint has an **enabled** toggle. Disable a constraint temporarily without deleting it — useful during data migration or bulk imports. ### Reordering Drag constraints to reorder them. Constraints are evaluated in order, and the first violation is shown to the user. --- # Database Indexes Source: https://docs.schemastack.io/app/indexes Indexes improve query performance by letting the database look up rows without scanning the entire table. SchemaStack lets you create, list, and delete indexes on any view through the Admin UI or the REST API. ## How Indexes Work An index is a separate data structure that the database maintains alongside your table. When you query or sort by an indexed column, the database uses the index to find matching rows quickly instead of reading every row. **When to add an index:** - Columns you frequently filter on (e.g., `status`, `email`, `created_date`) - Columns you sort by in large tables - Foreign key columns (often indexed automatically) - Columns used in unique constraints **When indexes aren't needed:** - Small tables (under a few thousand rows) — full scans are fast enough - Columns with very few distinct values (e.g., a boolean `active` flag) - Columns you rarely query or filter on **Tip** Adding an index speeds up reads but adds a small overhead to writes (inserts, updates, deletes), since the database must keep the index up to date. For most workloads this is negligible. ## Index Types | Type | Description | Use Case | |------|-------------|----------| | **Single-column** | Index on one column | Simple lookups and sorts | | **Composite** | Index on multiple columns | Queries that filter or sort by several columns together | | **Unique** | Enforces uniqueness across indexed columns | Ensuring no duplicate values (e.g., email addresses) | ## Creating an Index ### Via API ```http POST /api/indexes/view/{viewUuid} Content-Type: application/json Authorization: Bearer {token} { "name": "idx_orders_customer_date", "columnNames": ["customer_id", "order_date"], "isUnique": false } ``` **Response:** `201 Created` ```json { "uuid": "a1b2c3d4-...", "name": "idx_orders_customer_date", "columnNames": ["customer_id", "order_date"], "isUnique": false } ``` The metadata record is created immediately. The actual `CREATE INDEX` DDL runs asynchronously in the background and is typically applied within seconds. ### Naming Conventions Choose descriptive index names that indicate the table and columns: - `idx_orders_customer_id` — single column - `idx_orders_customer_date` — composite - `idx_users_email_unique` — unique index ## Listing Indexes ```http GET /api/indexes/view/{viewUuid} Authorization: Bearer {token} ``` **Response:** `200 OK` ```json [ { "uuid": "a1b2c3d4-...", "name": "idx_orders_customer_date", "columnNames": ["customer_id", "order_date"], "isUnique": false } ] ``` Returns an empty array when no indexes exist on the view. ## Deleting an Index ```http DELETE /api/indexes/{indexUuid} Authorization: Bearer {token} ``` **Response:** `204 No Content` The index is dropped from the database asynchronously, similar to creation. ## Schema Import When you import an existing database schema, SchemaStack automatically detects and preserves all existing indexes — including composite and unique indexes. No manual setup is needed for tables that already have indexes. ## Permissions | Action | Required Permission | |--------|-------------------| | List indexes | `VIEW_DATA` | | Create index | `CONFIGURE_VIEW` | | Delete index | `CONFIGURE_VIEW` | ## Limitations - **Index rename** is not supported — delete and recreate instead - **Partial indexes** (indexes with a `WHERE` clause) are not yet supported - **Expression indexes** (indexes on computed expressions) are not yet supported - Only **B-tree indexes** are created (the PostgreSQL default and most common type) --- # Filters & Sorting Source: https://docs.schemastack.io/app/filters Filters and sorting let you focus on the data that matters. ## Filters ### Opening the Filter Panel Click the **Filter** button in the table toolbar to open the filter panel. ### Adding a Filter 1. Click **Add Filter** in the filter panel 2. Select the column to filter on 3. Choose an operator (equals, contains, greater than, etc.) 4. Enter the filter value 5. The data updates immediately ### Multiple Filters Add multiple filters to narrow down your data further. All filters are applied together (AND logic). ### Clearing Filters Click **Clear all** in the toolbar to remove all filters, sorting, and the active preset. ## Sorting ### Adding a Sort Rule Click the **Sort** button in the table toolbar to add sort rules. - Choose a column - Select ascending or descending order - Add multiple sort rules for tie-breaking ### Column Header Sorting Click a column header to sort by that column. Click again to reverse the direction. ## URL Sync Filters and sort rules are synced to the URL. This means you can: - Share a filtered view by copying the URL - Bookmark specific data views - Navigate back/forward to restore previous filter states --- # View Search Source: https://docs.schemastack.io/app/search Find columns and cell values anywhere in your current view without leaving the page. ## Where it lives - **Desktop** — a pill-shaped search box sits in the top toolbar, to the right of the **Help** menu. - **Mobile** — open the hamburger menu and tap **Search**. The results dialog appears with its own search input at the top. - **Anywhere** — press `Ctrl + K` (Windows/Linux) or `⌘ + K` (macOS) to focus the search box from any part of the app. ## What it searches Every search is scoped to the **current view only** — it covers two distinct things: 1. **Metadata** — the table name and every column in the view. Matches on display name, database column name, API field name, or data type. 2. **Row data** — every cell in the view, case-insensitively, across the entire table (not just the rows currently on screen). JSON blobs, dates and numbers are included too, so `42` matches a row with `age = 42` and `2025-11` matches any date cell in November 2025. ## Using the results dialog As you type, the dialog opens with two clearly separated sections: - **Metadata** — green-themed cards for table / column matches. Each card has an **Edit metadata** button that jumps to the column in the table and opens its properties panel for editing. - **Row Data** — blue-themed cards for cell-value matches. Each card shows the matched value, which row it belongs to, and which column contained it. Cards include two quick actions: - **Copy value** — copies the cell value to your clipboard (with a brief check-mark confirmation). - **Filter by value** — adds a temporary `LIKE` filter for that column + value. The **Save Preset** button starts flashing so you can persist the filter if the search turned out to be useful; otherwise it's thrown away on the next refresh. If your query matches more rows than the server returns in one response, a note appears under the Row Data heading: _"Showing the first N matches. Refine your search for more specific results."_ ## Keyboard shortcuts | Key | Action | |---|---| | `Ctrl/⌘ + K` | Focus the search box from anywhere | | `↑` / `↓` | Move between results | | `Enter` | Activate the selected result | | `Esc` | Close the dialog | ## Activating a result - **Metadata result** — the dialog closes, the matching column header scrolls into view and briefly pulses green, and its properties panel opens so you can edit the column. - **Row Data result** — the dialog closes and the properties panel opens for the matched column. If the matching row happens to be on the currently loaded page, the row is also scrolled into view and the matched cell pulses yellow; otherwise the quick actions on the card (Copy value, Filter by value) are the fastest way to act on the match. ## Tips - Search is case-insensitive in both directions: `AMSTERDAM`, `amsterdam` and `AmsterDam` all match the same cells. - The query matches anywhere in a cell — substring, not whole-word. Use longer, more specific terms to narrow results. - Use **Filter by value** on a row match to quickly convert a one-off search into a persistent filter in your view. --- # Presets Source: https://docs.schemastack.io/app/presets Presets save your current filter, sort, and column configuration so you can return to it later or share it with your team. ## Saving a Preset 1. Configure your filters, sorting, and column visibility 2. Click **Save** in the toolbar 3. Enter a name for your preset 4. Click **Save** ## Loading a Preset 1. Click **Load Preset** in the toolbar 2. Browse available presets 3. Click a preset to apply it ## Updating a Preset When you have an active preset and make changes: 1. Click the **Save** dropdown arrow 2. Choose **Save "Preset Name"** to update the existing preset 3. Or choose **Save as new preset** to create a copy ## Default Preset Set a preset as your personal default so it's automatically applied every time you open the view. **From the Save dialog:** Check **Set as my default preset for this view** when saving or updating a preset. **From the Load dialog:** Click the pin icon next to any preset to toggle it as your default. Each user has their own default — setting a default does not affect other users. To remove a default, uncheck it in the Save dialog or click the pin icon again in the Load dialog. ## Favorites Mark frequently used presets as favorites for quick access. ## Clearing Click the active preset chip in the toolbar to clear it and return to the default view configuration. ## Visibility Each preset has a visibility level that controls who can see and use it: | Level | Who Can See It | | ----------- | ----------------------------------------------------------- | | **Private** | Only you | | **Shared** | Anyone who can see the view — workspace members and holders of a guest share link | There are two levels, not three. **Shared** does not distinguish between workspace members and guests: if someone can open the view, they can see the preset. If a preset's name or filter values would reveal something a guest should not know, keep it private. Set the visibility when saving a preset, or change it later from the preset's settings. ## Tags Add up to 20 tags per preset to organize and categorize them. Tags make it easier to find presets when you have many saved — use the tag filter in the Load Preset panel to narrow the list. ## Column Overrides Presets can override column settings without affecting the underlying view: - **Display Name** — Show a different label for a column - **Position** — Reorder columns within the preset - **Hidden** — Hide specific columns - **Width** — Set a custom column width These overrides apply only when the preset is active and do not affect other users' view of the data. ## Usage Tracking SchemaStack tracks how presets are used. In the preset list, you can see: - **Usage Count** — How many times the preset has been loaded - **Last Used** — When the preset was last applied This helps teams identify which presets are actively used and which may be outdated. --- # Conditional Row Styles Source: https://docs.schemastack.io/app/row-styles Style rows based on data conditions — like conditional formatting in spreadsheets. Rules apply a background color, a text color, bold, italic, and strikethrough to rows that match filter criteria. ## How it works 1. Open the **Row Styles** dialog from the view tab context menu 2. Add a rule with one or more conditions (same operators as filters: equals, not equals, greater than, etc.) 3. Choose a background color and a text color from the palettes, or toggle text styles (bold, italic, strikethrough) 4. Rules are priority-ordered — **first matching rule wins** ## Creating a rule Each rule has: - **Name** — a label for the rule (e.g., "Overdue tasks") - **Conditions** — filter criteria using column values and operators - **Condition logic** — `AND` (all conditions must match) or `OR` (any condition matches) - **Style** — background color, text color, bold, italic, strikethrough - **Enabled** — toggle rules on/off without deleting them ## Priority ordering Drag rules to reorder them. The first enabled rule whose conditions match a row determines that row's style. Later rules are not evaluated for that row. **Order narrowest to broadest.** Because evaluation stops at the first match, a rule whose conditions are a superset of the rule below it makes that rule unreachable. `amount > 100` placed above `amount > 1000` will colour every row over a thousand with the first rule's style, and the second will never fire — put the more specific threshold first. A rule with no conditions never matches, so a half-written rule is inert rather than applying to everything. The same is true of a condition whose comparison value is still empty. ## Stored on the view Style rules belong to the view, not to a filter preset and not to an individual user. Everyone with access to the view sees the same styling, and a rule you add or disable changes it for all of them. Switching filter presets does not change the styling. ## Color palettes Eight predefined M3-compatible pastel backgrounds that work in both light and dark mode: | Color | Hex | |-------|-----| | Red | `#FFEBEE` | | Orange | `#FFF3E0` | | Yellow | `#FFFDE7` | | Green | `#E8F5E9` | | Blue | `#E3F2FD` | | Purple | `#F3E5F5` | | Teal | `#E0F2F1` | | Grey | `#F5F5F5` | And eight matching text colors. Each one is the dark end of its hue in light mode and the light end in dark mode, so it stays readable on white, on its own pastel background, and on the dark surface — every pair clears WCAG AA contrast. | Color | Hex | |-------|-----| | Red | `#B71C1C` | | Orange | `#BF360C` | | Yellow | `#6D6213` | | Green | `#1B5E20` | | Blue | `#0D47A1` | | Purple | `#4A148C` | | Teal | `#004D40` | | Grey | `#424242` | Colors outside these two palettes can be set through the API. They are applied as inline styles, which means they cannot switch between light and dark mode — prefer the palettes unless you have a reason not to. ## Selection and hover priority When a styled row is selected or hovered, the selection/hover styling takes precedence over the conditional style. The conditional style reappears when the row is deselected. ## Performance Style rules are evaluated client-side against loaded rows (100 per page). This means: - No impact on database queries - SSE-inserted rows pick up styles immediately - Evaluation is sub-millisecond even with many rules --- # Real-Time Collaboration Source: https://docs.schemastack.io/app/collaboration SchemaStack keeps your data in sync across all connected users using server-sent events (SSE). Multiple users can view and edit the same workspace simultaneously — changes appear instantly for everyone. ## How It Works When you open a workspace, SchemaStack establishes a real-time connection to the server. Any changes made by other users appear in your view automatically. No manual refresh needed. ### What syncs in real time - **Cell edits** — when someone changes a value, you see it immediately - **Row additions and deletions** — new and removed rows appear for all users - **Column changes** — reordering, visibility, renaming, and schema changes - **Constraint updates** — validation rule changes propagate instantly - **View updates** — view name, description, and settings changes ### Architecture SchemaStack uses **Server-Sent Events (SSE)** for real-time updates: ``` User A edits a cell │ ▼ REST API saves the change │ ▼ Server broadcasts SSE event to workspace │ ├──→ User B receives update (applied automatically) ├──→ User C receives update (applied automatically) └──→ User A's event is filtered out (no echo) ``` Each user's own changes are applied optimistically (instant feedback) while the SSE broadcast updates everyone else. Events are filtered by user ID to prevent echoing your own changes back to you. ## Connection States The toolbar shows your connection status: | State | What you see | What to do | | ---------------- | -------------------------------- | -------------------------------------------------------------- | | **Connected** | Green workspace status bar | Nothing — everything works normally | | **Reconnecting** | Yellow banner: "Reconnecting..." | Wait, it will reconnect automatically with exponential backoff | | **Disconnected** | Red banner with "Reconnect" link | Click Reconnect, or check your network | ### Automatic Reconnection If the SSE connection drops, SchemaStack reconnects automatically with exponential backoff and jitter. This prevents all users from reconnecting simultaneously if the server restarts. ### Catch-up After a Reconnect Every real-time update carries an id, and on reconnect your browser tells the server the last one it received. The server replays what you missed and the "you may have missed updates" banner comes down by itself — you are simply caught up. The replay window is finite (the most recent updates per workspace and per view). If the gap is longer than the window — a laptop asleep overnight, or a server restart — the server says so honestly and the banner stays, offering a refresh. A partial replay presented as complete would be worse than the banner. ## New Rows Indicator When other users add rows while you're working, a pill appears at the top of the table showing how many new rows are available. Click it to load them into your view. This prevents unexpected jumps in the data while you're focused on a specific area. ## Offline Behavior If your network connection drops entirely, an offline banner appears across the top of the app. You can continue viewing data, but: - Edits won't be saved until you're back online - You won't receive updates from other users - The banner disappears automatically when your connection is restored --- # CSV Import Source: https://docs.schemastack.io/app/csv-import Import CSV files into any view with a visual field mapping dialog. Map CSV columns to view fields, preview records before importing, and save mapping configurations for repeated use. ## Opening the Import Dialog There are two ways to start an import: - **Toolbar** — click the **Import** button in the top toolbar - **File menu** — click **File > Import Data** The Import button is only enabled when the current view allows adding rows. ## Upload Select a `.csv`, `.tsv`, or `.txt` file. The dialog scans the file to detect columns and count rows without loading the entire file into memory — files up to 100 MB are supported. ## Field Mapping After selecting a file, the mapping table shows: | Column | Description | |--------|-------------| | **CSV Fields** | Values from the current record, numbered by CSV column position | | **Table Target Fields** | View column name with data type badge (e.g. `VARCHAR(255)`) and constraint badges (`PRIMARY`, `UNIQUE`) | ### Auto-Mapping Columns are automatically matched by name: 1. CSV header matches view column **display name** (case-insensitive) 2. Fallback: matches **field name** (camelCase API name) 3. Fallback: matches **database column name** (snake_case) Unmapped columns show `Default: ` and are skipped during import. ### Manual Mapping Click any CSV cell to open a dropdown and reassign the CSV column for that row. Select `—` to unmap. ### Record Browser Use the `< >` paginator at the bottom to browse through records and verify that CSV values land in the correct columns. ## Options | Option | Description | |--------|-------------| | **First line contains field names** | When checked, the first CSV row is used as column headers for auto-mapping. When unchecked, columns are mapped by position. | | **Import Method** | `INSERT` creates new rows. `UPDATE` updates existing rows (requires primary key mapped). | | **Align field names by** | Controls CSV column ordering: `File order` (original position) or `Alphabetical` (sorted by header name). | ## Column Handling | Column Type | Importable? | Notes | |-------------|-------------|-------| | Regular columns | Yes | User provides values | | UUID primary key | Yes | Map from CSV or leave blank for auto-generation | | Auto-increment primary key | No | Database generates automatically | | FK columns (with edit mode) | Yes | Provide the foreign key value | | Relationship display columns | No | Read-only joined data | | Computed / formula columns | No | Calculated at query time | | Auto-set timestamps | No | Created/updated timestamps are generated automatically | | Display groups | Expanded | Individual member columns shown separately | ## Saved Mappings Save a mapping configuration by entering a name and clicking **Save** above the mapping table. Saved mappings can be loaded from the dropdown when importing the same CSV structure again. Mappings are saved per view. Visibility can be **Private** (only you) or **Shared** (all view members). ## Import Execution Click **Import** to start. Rows are sent in batches of 500 to the server. A progress bar shows batch progress, success count, and error count. ## Validation on import Imported rows go through the same validation as a value typed into a cell: column constraints (required, max length, email, and the rest) and cross-field entity constraints. A row that fails is **reported with its row number and reason, and skipped** — the rest of the file still imports. Click **Export Errors CSV** to download every rejection with its details. This means an import can partially succeed, which is deliberate: one bad row in a 5,000-row file shouldn't cost you the other 4,999. **Tip** Fix the rejected rows in the exported errors CSV and import that file again — it has the same columns, so your saved mapping still applies. **Warning** Errors the database alone can detect — a unique value that collides with a row already in the table, a foreign key pointing at something that isn't there — are found when the batch is written, not during validation. Those fail the batch of 500 they belong to, and the report says so rather than blaming a single row. Validation catches the common cases first, so this is the exception. ## API Endpoint The bulk insert endpoint can also be called directly: ``` POST /api/data/bulk/{viewUuid}/insert ``` **Request body:** ```json { "rows": [ { "name": "Angola", "code": "AO" }, { "name": "Argentina", "code": "AR" } ] } ``` **Response (201):** ```json { "viewUuid": "abc-123", "totalCount": 2, "successCount": 2, "failureCount": 0, "skippedCount": 0, "errors": [] } ``` Maximum 1000 rows per request. Field names use the camelCase API field names (same as the single-row insert endpoint). ### Re-importing without duplicating By default an import only ever adds rows, so running the same file twice gives you everything twice. Name the columns that identify a row and the same file becomes a correction instead: ```json { "rows": [{ "email": "ada@example.com", "name": "Ada L." }], "conflictColumns": ["email"], "onConflict": "UPDATE" } ``` | `onConflict` | What happens to a row that already exists | | ------------ | ----------------------------------------- | | `ERROR` | The default. The database refuses the batch, and nothing is imported. | | `IGNORE` | The stored row is kept and the incoming one is skipped. Skipped rows are counted in `skippedCount`, not `failureCount` — they are the requested outcome, not a failure. | | `UPDATE` | The stored row's other columns are overwritten with the incoming values. The match columns themselves are never rewritten. | **Warning: The match columns need a unique index** A database can only recognise a duplicate if a unique index or primary key says those columns identify a row. Without one, PostgreSQL refuses the statement and the import returns an error saying so — nothing is written. Add the index first, or import without a match column. **Note: MySQL matches on any unique key** PostgreSQL matches on exactly the columns you name. MySQL has no equivalent, so it reconciles on **any** unique key on the table — a row colliding on a different unique column is updated too. Worth knowing if your table has more than one. --- # Bulk Operations Source: https://docs.schemastack.io/app/bulk-operations Bulk operations let you act on multiple rows at once. ## Selecting Rows - **Individual selection** — Click the checkbox on each row - **Select all on page** — Click the header checkbox - **Select all matching filter** — After selecting rows on a page, click "Select all X rows" in the banner ## Available Operations When rows are selected, a floating action bar appears at the bottom of the screen. ### Bulk Edit 1. Click **Edit** in the action bar 2. Select the column to update 3. Enter the new value 4. Confirm — all selected rows will be updated ### Export Click **Export** to download the selected rows as a **CSV** file (`.csv`) — comma-separated values, readable by any spreadsheet or data tool. The export runs in the background rather than blocking on the download: you get a job straight away, and the file appears in the **Downloads** panel when it is ready. **Note: Formats** Exports are CSV. There is no Excel (`.xlsx`) export — open the CSV in Excel, which reads it natively. Headers are the underlying **database column names** rather than the display names shown in the grid, and hidden columns are included. Rows come out ordered by primary key, not by the sort applied to your view. ### Downloads Panel After starting an export, the **Downloads** dropdown appears in the toolbar. It shows the status of your exports — in progress, ready to download, or failed. Click a completed export to download the file. ### Delete Click **Delete** to remove all selected rows. A confirmation dialog will appear before any data is deleted. ## Deselecting Click **Deselect** in the action bar, or click the header checkbox to clear the selection. --- # Webhooks Source: https://docs.schemastack.io/app/webhooks Outbound webhooks let you automatically send row data to external services when triggered from the Data Platform. Use webhooks to connect SchemaStack to tools like Zapier, Make, n8n, or any HTTP endpoint. ## How Webhooks Work 1. **Configure** — Set up a webhook endpoint in your view's properties 2. **Select** — Choose rows in the Data Platform 3. **Send** — Use the "Send to webhook" bulk action 4. **Receive** — Your endpoint receives the row data as JSON ## Setting Up a Webhook ### Add a Webhook Endpoint 1. Open a view in the Data Platform 2. Click the **Properties** panel (right sidebar) 3. Scroll to the **Webhooks** section 4. Click **Add Webhook** 5. Fill in: - **Name** — A descriptive label (e.g., "Zapier - New Subscribers") - **URL** — Your webhook endpoint URL (must be HTTPS in production) - **Headers** — Optional custom HTTP headers (key-value pairs) - **HMAC Secret** — Optional signing secret for payload verification 6. Click **Save** ### Enable/Disable Webhooks Toggle the enabled switch next to any webhook to temporarily disable it without deleting the configuration. ## Sending Data to a Webhook 1. Select rows in the Data Platform (individually or "Select All") 2. Click the **more menu** (⋯) in the bulk action bar 3. Choose **Send to webhook** 4. Pick which webhook endpoint to send to 5. Confirm the send The operation runs asynchronously — you'll see progress in the bulk operation status bar, and the delivery result appears in the webhook's delivery log. ## Payload Format SchemaStack sends a JSON payload with this structure: ```json { "rows": [ { "id": 1, "name": "Alice", "email": "alice@example.com" }, { "id": 2, "name": "Bob", "email": "bob@example.com" } ], "metadata": { "viewId": "a1b2c3d4-...", "tableName": "contacts", "rowCount": 2, "timestamp": "2026-04-04T12:00:00Z", "jobId": "e5f6g7h8-..." } } ``` ### HTTP Headers Every webhook request includes: | Header | Description | |--------|-------------| | `Content-Type` | `application/json` | | `User-Agent` | `SchemaStack-Webhook/1.0` | | `X-SchemaStack-Delivery-Id` | Unique delivery ID (same as jobId) | | `X-SchemaStack-Signature` | HMAC-SHA256 signature (if secret configured) | Plus any custom headers you configured. ## HMAC Signature Verification If you set an HMAC secret, SchemaStack signs each payload with HMAC-SHA256: ``` X-SchemaStack-Signature: sha256= ``` To verify in your endpoint: ```javascript const crypto = require('crypto'); function verifySignature(payload, signature, secret) { const expected = crypto .createHmac('sha256', secret) .update(payload) .digest('hex'); return `sha256=${expected}` === signature; } ``` ## Delivery Log Each webhook shows a delivery log with: - **Status code** — HTTP response from your endpoint - **Duration** — Round-trip time in milliseconds - **Attempt number** — Retry attempt (up to 3) - **Response body** — First 4KB of the response - **Error message** — If the delivery failed ## Retry Policy Failed deliveries are retried up to 3 times with exponential backoff: | Attempt | Delay | |---------|-------| | 1st | Immediate | | 2nd | 1 second | | 3rd | 4 seconds | Retries occur for server errors (5xx) and rate limits (429). Client errors (4xx except 429) are not retried. ## Limits - **Maximum rows per send:** 10,000 - **Request timeout:** 30 seconds - **Response body stored:** 4KB (truncated) - **SSRF protection:** Private/internal IPs are blocked ## Use with Zapier The easiest way to connect SchemaStack to external services is through Zapier: 1. Set up a **Webhooks by Zapier** trigger (Catch Hook) 2. Copy the webhook URL from Zapier 3. Add it as a webhook in SchemaStack 4. Send test data to map fields in Zapier 5. Connect to any of Zapier's 7,000+ integrations See the [Zapier Integration guide](https://docs.schemastack.io/guide/zapier-integration.md) for details. --- # Schema Advisor Source: https://docs.schemastack.io/app/schema-advisor The Schema Advisor is a built-in AI assistant available in every workspace. It helps you understand, design, and improve your database schema through a conversational interface. ## What It Does The Schema Advisor can: - **Explain your schema** — describe your tables, columns, types, relationships, and constraints in plain language - **Suggest improvements** — recommend normalization, better column types, missing indexes, or naming conventions - **Help with data modeling** — advise on how to structure new tables, model relationships (one-to-many, many-to-many), and handle common patterns - **Review constraints** — check if your validation rules (required, unique, min/max, regex) are complete and consistent - **Create tables and columns** — build out your schema through conversation ("Add an orders table with customer_id, total, and status columns") - **Plan schema changes** — help you think through the impact of adding, renaming, or removing columns before you make the change ## What It Doesn't Do The Schema Advisor only reads schema metadata (table names, column definitions, types, constraints). It **does not**: - Read, modify, or access your actual row data - Execute schema changes directly - Access other workspaces in your organisation **Tip: Data operations via MCP** For AI-powered data queries, record creation, and schema modifications, connect your own AI client (Claude Desktop, Cursor, or any MCP-compatible tool) to your workspace via [MCP integration](https://docs.schemastack.io/guide/ai-integration.md). Your data stays in your AI account — SchemaStack just provides the tools. ## How to Use 1. Open any view in the spreadsheet interface 2. Click the **robot icon** in the toolbar 3. A floating chat window appears — ask your question ### Example Questions | Question | What it does | |----------|-------------| | "What tables do I have?" | Lists all views with column counts | | "Describe the orders table" | Shows all columns, types, constraints, and relationships | | "Should I add an index on customer email?" | Analyzes the column and advises based on your schema | | "How should I model tags for products?" | Suggests a many-to-many pattern with a junction table | | "What foreign keys does the orders table have?" | Shows relationship details | | "Is my schema normalized?" | Reviews your table structure and suggests improvements | | "Create a tags table with name and slug columns" | Creates the table and columns in your database | | "Add an email column to the customers table" | Adds the column with appropriate type and constraints | ## Privacy The Schema Advisor sends **only schema metadata** to the AI service (Anthropic Claude) — table names, column names, types, and constraints. Your actual data rows are never sent. For details on Anthropic's data handling, see [Anthropic's privacy policy](https://www.anthropic.com/privacy). ## Availability The Schema Advisor is available on all plans with daily message limits: | Plan | AI messages/day per member | |------|---------------------------| | Free | 3 | | Pro | 20 | | Enterprise | Unlimited | --- # Guest Access Source: https://docs.schemastack.io/app/guest-access Guest access lets you share specific views with external users — clients, contractors, auditors, or the public — without requiring them to join your organization. ## Why View-Level Access Guests only see the specific view you share, not the entire workspace. They can't see your entity schema, other views, or workspace structure. This follows the principle of least privilege: ``` Workspace: "Marketing Analytics" ├── View: "Campaign Performance" (internal only) ├── View: "Budget Overview" (internal only) └── View: "Client Report" ← guest sees only this ``` ## Use Cases ### Client Reporting A marketing agency builds an analytics workspace. The client gets read-only access to a "Client Report" view — they see their data without seeing internal dashboards. ### Contractor Access A freelance developer needs access to a "Design System" view for a 3-month project. Access is time-limited and scoped to that one view. ### Partner Collaboration Two organizations co-developing a product. Users from one org get editor access to a shared view in the other org's workspace, without becoming full members. ### External Audit An auditor reviews compliance records through a time-limited, read-only view. No permanent org membership needed. ### Public Data A government agency publishes open datasets. Anyone can access the view via a public link — no authentication required. ### Customer Self-Service A SaaS company gives each customer their own analytics view. Customers access their specific view without seeing internal data. ## How it works ### Guest links Each guest gets a link backed by a unique access token. The token is single-use-friendly to share but unguessable, and only the hash is stored — the plaintext is shown once when the link is created and never again. Each link is scoped to **one view**. There is no "guest in a workspace" concept; a guest is always a guest of a specific view. ### Roles A guest link has one of two roles: - **`GUEST_VIEWER`** — read-only. Can open the view, read rows, run filters, export if the view itself is exportable. - **`GUEST_EDITOR`** — read + write. Can edit cells, add rows, and delete rows, **subject to the view's own settings** (see below). There is no "create permission" or "export permission" as a separate toggle. The view's `editable`, `addable`, and `exportable` flags are the source of truth; the guest's role just determines whether they're allowed to use those capabilities. ### Limits and expiration Each link can have: - **An expiration timestamp** (`expiresAt`) — after this moment the link stops working. - **A maximum-use count** (`maxUses`) — the link's `currentUses` counter increments on each access; once it hits the limit, the link stops working. Either, both, or neither can be set. A link with no expiration and no max-uses works until it's revoked. ### Revocation Any workspace admin can revoke a link at any time from the view's permissions panel. Revocation is recorded with `revokedAt` and `revokedBy`; the link is rejected on subsequent access. Revocation is instant — there's no grace period and no token re-issuance. ### Real-time updates Guest-link creation and revocation broadcast as SSE events to all connected workspace members (`view.guest.created`, `view.guest.revoked`), so a teammate revoking a link is reflected in everyone else's UI immediately. ### Permission resolution When a guest hits a view, both the role and the view settings apply. The view always wins on capability gates: | View setting | Guest role | Result | | ------------------ | -------------- | ------------------------------ | | `editable: true` | GUEST_VIEWER | Read-only (role wins) | | `editable: true` | GUEST_EDITOR | Can edit | | `editable: false` | GUEST_EDITOR | Read-only (view setting wins) | | `addable: true` | GUEST_EDITOR | Can add rows | | `addable: false` | GUEST_EDITOR | Cannot add rows | | `exportable: true` | GUEST_VIEWER | Can export | | `exportable: false`| any | Cannot export | In short: the role grants intent ("can edit"); the view grants capability ("editing is enabled here"). Both must agree. ### What guests cannot see A guest only sees the one view their link is scoped to. They cannot see: - The workspace's other views - The entity schema (column metadata, constraints, relationships) - Other guest links on this view or any other view - Workspace members or organisation structure - Any audit log ### Creating a guest link Workspace admins create links from the view's permissions panel: 1. Open the view's **Permissions** panel 2. Click **Add guest link** 3. Choose role: `GUEST_VIEWER` or `GUEST_EDITOR` 4. Optionally: name, email (both for your own tracking), expiration, max-uses 5. The plaintext token is shown once — copy it before closing the dialog. The link can be regenerated by revoking and creating a new one if the original is lost. --- # Administration Source: https://docs.schemastack.io/admin/overview The Admin app is where you manage your organization, workspaces, team members, and account settings. ## What You Can Do | Area | Description | | ------------------------------------- | ----------------------------------------------------------------------- | | [Organizations](https://docs.schemastack.io/admin/organizations.md) | Manage your organization's profile, details, and settings | | [Workspaces](https://docs.schemastack.io/admin/workspaces.md) | Create and configure workspaces, connect databases, manage access modes | | [Members & Roles](https://docs.schemastack.io/admin/members.md) | Invite team members, assign roles, manage permissions | | [Billing & Subscriptions](https://docs.schemastack.io/admin/billing.md) | Subscribe to a plan, manage your subscription, view invoices | | [Security Settings](https://docs.schemastack.io/admin/security.md) | Change your password, enable 2FA, manage active sessions | ## Dashboard After logging in, the dashboard shows: - **Your profile** — name, email, UUID, and current role - **Selected organization** — name, slug, and your role within it - **Quick actions** — navigate to workspaces, switch organizations, or log out If you belong to multiple organizations, use **Select Organization** to switch between them. --- # Organizations Source: https://docs.schemastack.io/admin/organizations An organization is the top-level container in SchemaStack. All workspaces, members, and data belong to an organization. ## Organization Settings Navigate to **Organization → Settings** to manage your organization's profile. ### General Information | Field | Description | | ----------------------------------- | ------------------------------------------- | | Name | Your organization's display name (required) | | Description | A short description of the organization | | Website | Organization website URL | | Email | Contact email address | | Phone | Contact phone number | | Address, City, Country, Postal Code | Physical address | | VAT Number | Tax identification number | | Registration Number | Business registration number | Changes are saved when you click **Save Changes**. ## Switching Organizations The organization switcher sits in the toolbar at the top of every page, showing the organization you are currently working in. 1. Click the current organization's name in the toolbar 2. Choose the organization you want to work in 3. Your session updates to reflect the new organization context All data — workspaces, members, roles — is scoped to the selected organization. Two organizations can each have a workspace with the same name; they never see each other's data. ## Creating Another Organization You are not limited to one. Separate organizations are the right choice when you want data, members, and billing kept fully apart — a second business, a client you work for, or a clean space to try something out. 1. Click the organization name in the toolbar 2. Choose **New organisation** at the bottom of the menu 3. Give it a name and continue You become the owner of the new organization, and it starts empty — your existing workspaces, members, and data stay where they are. Being invited to someone else's organization does not prevent you from creating your own. --- # Workspaces Source: https://docs.schemastack.io/admin/workspaces A workspace is a container for your data views. Each workspace can optionally connect to a database and holds one or more views that define how your data is structured and displayed. ## Workspace List The workspace overview shows all workspaces in your organization. Each workspace displays its name, description, status badge, and view count. ## Creating a Workspace When you click **Create Workspace** (requires Admin or Owner role), you're given two paths: - **Start with sample data** — provisions a managed PostgreSQL database pre-loaded with the **Acme Store** sample dataset (categories, products, customers, orders, with realistic relationships). Useful for evaluating SchemaStack without bringing your own database, or for trying out features on data that already has shape. The managed database is yours; you can export, query, or migrate away from it at any time. - **Connect your own database** — point SchemaStack at your existing PostgreSQL or MySQL database. This is the production path: your data stays where you put it, no data leaves your database to any SchemaStack-managed storage. Either path then asks for: - **Name** — Display name (required, minimum 2 characters) - **Slug** — URL-friendly identifier (auto-generated from name, or enter your own) - **Description** — Optional description For the connect-your-own path, you'll also configure the database connection (see [Database Connection](#database-connection) below). Click **Create** to finish. **Tip: Just trying it out?** Pick **Start with sample data**. You can always create a second workspace later to connect your own database — workspaces are independent. ## Workspace Detail Click a workspace to see its detail page, which includes: - **Header** — Name, description, slug, and status badge - **Statistics** — Total views, schema status, team members, recent activity - **Quick Actions** — Invite members, change access mode, connect database ### Schema Drift Detection If your workspace is connected to a database, SchemaStack can detect when the database schema has changed: - **In Sync** — Workspace metadata matches the database - **Drift Detected** — Changes found (columns added, modified, or removed) When drift is detected, a warning banner appears with options to **Synchronize Schema** or **View Detailed Changes**. You can also manually trigger a drift check from the workspace detail page. ### Reset Workspace Organization Admins and Owners can reset all view columns to match entity definitions. This is useful after major schema changes. ## Database Connection Each workspace can connect to an external database. ### Supported Databases | Vendor | Default Port | | ---------- | ------------ | | PostgreSQL | 5432 | | MySQL | 3306 | ### Configuring a Connection 1. Open **Workspace Settings** 2. Enable the database connection toggle 3. Fill in the connection details: - **Vendor** — Select your database type - **Host** — Database server address - **Port** — Connection port (auto-filled based on vendor) - **Database Name** — Name of the database - **Username** and **Password** — Database credentials - **Additional Parameters** — Optional (e.g., `sslmode=require`) 4. Click **Test Connection** to verify 5. Click **Save Changes** ### Editing an Existing Connection If a database is already configured, you'll see the current connection info (database name, host, port, vendor). Click **Edit** to modify the configuration. When updating, username and password are optional — leave them blank to keep the existing credentials. ## Schema Import After connecting a database, you need to import the schema to start working with your data. ### First-Time Import 1. Click **Import Schema** on the workspace detail page 2. SchemaStack reads the database and presents a list of available tables 3. Select the tables you want to include in the workspace 4. Click **Import** 5. Columns and relationships are created based on the database schema ### Syncing After Database Changes When your database schema changes (new tables, altered columns), sync the workspace to pick up the changes: 1. Click **Sync Schema** on the workspace detail page (or respond to a drift detection warning) 2. Review the proposed changes — added, modified, and removed tables/columns are listed 3. Confirm to apply the changes Syncing never touches the data in your database — it only updates SchemaStack's picture of the schema, and issues no DDL. **Warning** It does remove metadata for things your database no longer has. A column dropped from the database loses its SchemaStack configuration too — display name, widget type, validation rules — and that removal is not reversible. The column's data went with the column; this is the configuration around it. ## Access Modes Workspaces support different access modes to control what operations are allowed: | Mode | Data Reads | Data Writes | Schema Changes | Who Can Access | | ----------------- | ---------- | ----------- | -------------- | -------------- | | **Active** | Yes | Yes | Yes | Everyone | | **Schema Locked** | Yes | Yes | No | Everyone | | **Read Only** | Yes | No | No | Everyone | | **Design** | Yes | No | Yes | Everyone | | **Maintenance** | No | No | No | Admins only | To change the access mode: 1. Click **Change Mode** on the workspace detail page (or in settings) 2. Select the desired mode 3. Confirm the change A status banner appears on the workspace when it's in any mode other than Active. ## Workspace Settings ### General Settings - **Name** — Workspace display name - **Slug** — URL identifier (lowercase, alphanumeric, hyphens only) - **Description** — Optional description ### API Keys Manage API keys for programmatic access to workspace data. - **Create** — Click **New API Key**, enter a description, and copy the generated key. The key is only shown once. - **View** — The API Keys list shows each key's description, creation date, and last-used date. - **Rotate** — Generate a new secret for an existing key while keeping its name, permissions, and expiration. The old secret stops working immediately. - **Revoke** — Click **Revoke** next to a key to permanently disable it. Revoked keys cannot be restored or rotated. See [API Authentication](https://docs.schemastack.io/api/authentication.md) for how to use API keys in requests. ### Storage Settings Configure S3-compatible storage for file and image uploads. - **Bucket** — S3 bucket name (required) - **Region** — AWS region (required) - **Access Key ID / Secret** — Credentials for the bucket. Leave blank when updating to keep existing credentials. - **Custom Endpoint** — For S3-compatible providers like MinIO, Backblaze B2, etc. - **Path-style access** — Use path-style URLs (required by some providers) - **Presigned URL mode** — Generate presigned URLs for direct file access - **File Path Template** — Customize the S3 key structure for uploaded files. See [File Path Templates](https://docs.schemastack.io/guide/file-path-templates.md) for details. ### Danger Zone - **Delete Workspace** — Permanently removes the workspace and all its views. A confirmation dialog will appear before deletion. --- # Members & Roles Source: https://docs.schemastack.io/admin/members SchemaStack uses a multi-level permission model: **organization roles** control what you can do across the organization, **workspace roles** control access within individual workspaces, and **view roles** provide fine-grained access to specific views. ## Permission Hierarchy ``` Organization └── Workspace └── View └── Column (future) ``` Permissions cascade downward. An organization Admin inherits access to all workspaces. A workspace Admin inherits access to all views. At each level, you can override the inherited role with a more specific one. ## Organization Roles | Role | Manage Members | Manage Workspaces | Manage Settings | Create Workspaces | | ---------- | -------------- | ----------------- | --------------- | ----------------- | | **Owner** | Yes | Yes | Yes | Yes | | **Admin** | Yes | Yes | Yes | Yes | | **Member** | No | No | No | No | ### Managing Organization Members Navigate to **Organization → Members** to see all members. Each member shows: - Name and email - Role (Owner, Admin, or Member) - Status (Active, Invited, or Inactive) **Changing a member's role:** 1. Click the role dropdown next to the member 2. Select the new role (Admin or Member) **Tip** You cannot change the Owner's role or your own role. **Removing a member:** 1. Click the remove button next to the member 2. Confirm in the dialog ### Inviting Members 1. Click **Invite Member** 2. Enter the person's email address 3. Select a role (Member or Admin) 4. Click **Send Invitation** The invitee receives an email with a link to join. Their status shows as **Invited** until they accept. You can **Resend Invitation** for pending invitations. ## Workspace Roles Workspace roles provide finer-grained control within a specific workspace: | Role | View Data | Edit Data | Edit Schema | Manage Members | Manage Settings | | -------------------- | --------- | --------- | ----------- | -------------- | --------------- | | **Workspace Admin** | Yes | Yes | Yes | Yes | Yes | | **Workspace Editor** | Yes | Yes | Yes | No | No | | **Workspace Viewer** | Yes | No | No | No | No | ### Role Inheritance Organization roles automatically map to workspace roles: | Organization Role | Inherited Workspace Role | | ----------------- | ------------------------------------ | | Owner | Workspace Admin | | Admin | Workspace Admin | | Member | No access (must be explicitly added) | ### Managing Workspace Members Navigate to a workspace's **Members** tab to see all workspace members. **Adding a workspace member:** 1. Click **Add Member** 2. Select the member and assign a workspace role 3. Click **Add** **Changing a workspace role:** 1. Click the role dropdown next to the member 2. Select the new role ### Inherited vs Direct Members Members can access a workspace in two ways: - **Direct** — explicitly added to the workspace with a specific role - **Inherited** — access granted through their organization role (Owners and Admins automatically have access) **Note** Inherited members cannot be removed from the workspace — their access is managed at the organization level. ## View Roles View roles control access to individual views within a workspace: | Role | View Data | Edit Data | Delete Rows | Add Rows | Export | | --------------- | --------- | --------- | ----------- | -------- | ------ | | **View Admin** | Yes | Yes | Yes | Yes | Yes | | **View Editor** | Yes | Yes | Yes | Yes | Yes | | **View Viewer** | Yes | No | No | No | No | Workspace roles automatically map to view roles: | Workspace Role | Inherited View Role | | ---------------- | ------------------- | | Workspace Admin | View Admin | | Workspace Editor | View Editor | | Workspace Viewer | View Viewer | You can override these by explicitly adding a user to a view with a different role. For example, a Workspace Viewer can be given View Editor access to a specific view. ## Member Statuses | Status | Meaning | | ------------ | ----------------------------------------------------------------------- | | **Active** | Member has accepted their invitation and is actively using the platform | | **Invited** | Invitation sent, waiting for the member to accept | | **Inactive** | Account exists but is currently inactive | ## Real-Time Updates The members list updates in real time. When another admin makes changes to the member list, you'll see a notification banner with a refresh option. --- # Billing & Subscriptions Source: https://docs.schemastack.io/admin/billing Manage your organization's subscription, view invoices, and access the billing portal. ## Plans SchemaStack offers a simple pricing model: | Plan | Price | Includes | |------|-------|----------| | Free | $0/month | Limited workspaces, views, members, and 3 AI messages/day | | Pro | $19/month | More workspaces, views, members, 20 AI messages/day, and priority support | | Enterprise | Custom | Unlimited everything, dedicated support, custom SLAs | ## Subscribing to a Plan 1. Go to **Plan & Billing** in the admin sidebar 2. Click **Subscribe** on the plan you want 3. You'll be redirected to a secure checkout page powered by LemonSqueezy 4. Complete payment — your subscription activates immediately 5. You're automatically redirected back to SchemaStack ## Managing Your Subscription From the **Plan & Billing** page you can: - **View your current plan** — see your active subscription, billing interval, and renewal date - **Cancel** — cancel your subscription; you keep access until the end of the current billing period - **Resume** — if you've cancelled but the period hasn't ended yet, you can resume your subscription - **Billing portal** — access LemonSqueezy's customer portal to update payment methods, view invoices, and download receipts ## Invoices Past invoices are listed on the **Plan & Billing** page. Click any invoice to view or download the receipt from LemonSqueezy. ## Usage & Limits Each plan has limits on: - **Workspaces** — how many workspaces you can create - **Views per workspace** — how many views each workspace can have - **Members** — how many team members can join your organization - **AI messages** — how many Schema Advisor messages per day per member When you reach a limit, you'll see a clear message explaining the maximum allowed. Upgrade your plan to increase limits. ## FAQ **What happens when I cancel?** Your subscription remains active until the end of the current billing period. After that, your organization downgrades to the Free plan. Your data is never deleted. **Can I switch plans?** Yes — upgrade or downgrade from the Plan & Billing page. Changes take effect at the next billing cycle. **What payment methods are accepted?** LemonSqueezy supports credit/debit cards and other payment methods depending on your region. **Is my payment information secure?** Yes — SchemaStack never sees or stores your payment details. All payment processing is handled by LemonSqueezy, a PCI-compliant payment provider. --- # Security Settings Source: https://docs.schemastack.io/admin/security Manage your account security from **Settings → Security**. ## Change Password 1. Enter your **current password** 2. Enter your **new password** (minimum 8 characters) 3. Confirm the new password 4. Click **Update Password** ## Two-Factor Authentication (2FA) Two-factor authentication adds an extra layer of security by requiring a verification code from your authenticator app when you log in. ### Enabling 2FA 1. Click **Enable Two-Factor Authentication** 2. Scan the QR code with your authenticator app (Google Authenticator, Authy, etc.) 3. Enter the verification code from your app 4. Save the backup codes in a secure location **Warning** Store your backup codes safely. If you lose access to your authenticator app, you'll need them to log in. ### Disabling 2FA 1. Click **Disable Two-Factor Authentication** 2. Enter your password and a verification code 3. Confirm the action ## Active Sessions View all devices where you're currently logged in. Each session shows: | Field | Description | | ----------- | ----------------------------------- | | Device | Device name or type | | Location | Approximate location (if available) | | Last Active | When the session was last used | | IP Address | The IP address of the session | Your current session is marked with a **Current** badge. ### Revoking a Session To log out of another device: 1. Find the session in the list 2. Click **Revoke** 3. Confirm in the dialog The user on that device will be logged out immediately. ## Account Deletion You can request permanent deletion of your account from **Settings → Personal**. ### Requesting Deletion 1. Scroll to the **Danger Zone** section 2. Click **Delete My Account** 3. Enter your password 4. Select a reason for leaving 5. Click **Request Deletion** An email confirmation is sent. After confirming via email, a grace period begins during which you can recover your account. ### Recovering Your Account If you change your mind during the grace period: 1. Log in to your account 2. Click **Recover My Account** on the deletion notice 3. Enter your password to confirm After the grace period expires, deletion is permanent and cannot be undone. --- # Personal Profile Source: https://docs.schemastack.io/admin/profile Your personal profile stores your identity and preferences across all organizations you belong to. ## Profile Fields | Field | Description | | -------------- | ---------------------------------------- | | **Name** | Your display name (shown to other users) | | **Phone** | Contact phone number | | **Job Title** | Your role or position | | **Department** | Your team or department | | **Language** | Preferred interface language | | **Website** | Personal or professional URL | | **Bio** | Short biography | | **Timezone** | Your local timezone for date display | To edit your profile, click your avatar in the top-right corner and select **Profile**. ## Avatar Upload a profile picture to make yourself recognizable to your team. 1. Click your current avatar (or the placeholder) in the profile page 2. Select an image file 3. Use the cropping tool to adjust the visible area 4. Click **Save** Supported formats: JPEG, PNG, GIF, WebP. ## Changing Your Email To change your email address: 1. Go to your profile settings 2. Enter your new email address 3. Click **Save** 4. A verification email is sent to the new address 5. Click the verification link to confirm the change Your old email remains active until the new one is verified. ## Notification Preferences Control which notifications you receive: - **Email notifications** — Activity summaries and important updates - **Workspace alerts** — Changes to workspaces you're a member of - **Invitation notifications** — When you're invited to new organizations or workspaces Adjust these settings from the **Notifications** section of your profile page. --- # Roadmap Source: https://docs.schemastack.io/roadmap SchemaStack is under active development. Here's what's available today and what's coming next. ## Available Now ### Core Platform - **Visual entity management** — define entities, columns, and relationships through the Admin UI - **Spreadsheet-style data editing** — Excel-like interface with inline editing, sorting, and filtering - **Auto-generated REST API** — full CRUD with OpenAPI/Swagger documentation per workspace - **Real-time collaboration** — live updates via SSE when team members edit data simultaneously - **Your data, always** — SchemaStack connects to your database. Your data never leaves your infrastructure. We only store metadata (entity definitions, views, permissions). [Learn more](https://docs.schemastack.io/guide/key-concepts.md) ### Data Platform - **Filters & sorting** — filter by any column with 9+ operators, multi-column sorting - **Presets** — save and share filter/sort/column configurations with private, team, or public visibility - **Bulk operations** — multi-row editing, export, and deletion with progress tracking - **Relationship navigation** — browse and edit related entities inline, add columns from related tables - **Activity feed** — real-time log of who changed what, when — undockable into a floating, draggable window ### Schema & Validation - **Column types** — text, number, boolean, date, timestamp, and more - **Computed columns** — derived values from SQL expressions, calculated at query time - **Database indexes** — create and manage single-column, composite, and unique indexes - **Constraints** — not null, unique, min/max length, regex patterns, check expressions - **Schema drift detection** — compare entity definitions against the live database - **Migration impact prediction** — see whether a schema change will be instant, brief, or blocking before applying it ### Access Control - **Organizations & workspaces** — multi-tenant structure with isolated databases - **Role-based permissions** — organization-level (Owner, Admin, Member) and workspace-level (Admin, Editor, Viewer) - **View-level access** — control who can see and edit specific views - **API keys** — scoped to workspaces with read-only or read-write permissions ### Security - **Two-factor authentication** — TOTP-based 2FA with recovery codes - **Session management** — view and revoke active sessions - **Audit logging** — track changes across the platform ### AI Integration - **MCP server** — expose your workspace as a [Model Context Protocol](https://modelcontextprotocol.io/) server, so AI assistants (Claude, ChatGPT, Copilot, etc.) can browse schemas, query data, create records, and manage schema through natural conversation. [Learn more](https://docs.schemastack.io/guide/ai-integration.md) ### Integrations - **Zapier** — connect your data to 7,000+ apps with triggers, actions, and searches via the native SchemaStack Zapier app. [Learn more](https://docs.schemastack.io/guide/zapier-integration.md) ## Coming Soon - **Member presence** — see who's online in your organisation. Active members are shown in member lists and updated in real-time via SSE on login, logout, and session expiry - **View Designer** — design your entire view schema in one go. Instead of adding columns one at a time, define all columns — names, types, and constraints — in a single table-style editor, similar to database clients like pgAdmin or DBeaver. Reorder columns by dragging, set primary keys, nullable flags, and defaults before saving. Works for both new views and bulk-editing columns on existing views - **Visual relationship builder** — drag-and-drop relationship creation - **Migration history** — version tracking with rollback support - **Comments & annotations** — discuss data inline - **Notifications** — configurable alerts on changes that matter to you - **Career notifications** — subscribe on the careers page to get notified when new positions open up ## Managed Database Hosting **Note: Coming in a future release** Two managed hosting options are planned, so you can choose the level of infrastructure management that fits your team. ### Managed RDS For teams who don't want to deal with database setup and administration. SchemaStack provisions and manages an AWS RDS instance on your behalf: - **One-click provisioning** — SchemaStack creates a fully configured PostgreSQL (or MySQL) instance with optimized settings, proper security groups, VPC configuration, and encryption at rest. - **Right-sized infrastructure** — instance type and storage selected based on your workload, with recommendations as you grow. - **Automated maintenance** — minor version upgrades, patching, and parameter tuning handled by SchemaStack. - **Daily backups** — RDS automated backups with configurable retention. - **Costs included** — AWS infrastructure costs are bundled into your monthly SchemaStack subscription. No surprise bills. You still own your data — SchemaStack manages the infrastructure, but the database is dedicated to your organization and can be migrated out at any time. ### Enterprise: ZFS-Backed Hosting For teams managing mission-critical, large-scale databases. ZFS-backed infrastructure enables capabilities that aren't possible with traditional database hosting: - **Instant cloning** — create a full copy of your production database in seconds, regardless of size. A 5 TB database clones as fast as a 5 MB one, thanks to ZFS copy-on-write snapshots. - **Safe schema migration testing** — before applying a schema change to production, test it on a live clone with real data. See exactly what will happen — execution time, lock impact, potential errors — without any risk to production. - **Risk-based execution** — SchemaStack assesses each migration's risk level. Critical changes (large tables, destructive operations) require clone testing first. Low-risk changes can be applied directly. - **Tested rollbacks** — roll back schema changes with confidence. Rollback scripts are validated on a clone before touching production. - **Hourly automated backups** — ZFS snapshots provide instant restore to any hourly checkpoint with zero performance impact. - **SSO integration** — SAML/OIDC for enterprise identity providers. - **Dedicated support** #### How It Works Your database is replicated to SchemaStack's ZFS infrastructure via streaming replication. The replication stays in sync continuously, so clones always reflect near-real-time production data. ``` Your Database (AWS RDS, self-hosted, etc.) │ │ Streaming replication ▼ SchemaStack ZFS Infrastructure ├── Live replica (continuously synced) ├── Hourly snapshots (point-in-time backups) └── Instant clones (for migration testing) └── Test migration → review results → apply to production ``` Cloning pauses replication for less than a second to take a consistent snapshot, then resumes immediately. ### Hosting Comparison | Feature | Bring Your Own DB | Managed RDS | Enterprise ZFS | | ------------------------ | -------------------- | ------------------------ | ------------------------------- | | Database setup | You manage | SchemaStack provisions | SchemaStack provisions | | Security groups & VPC | You manage | Configured for you | Configured for you | | Database tuning | You manage | Optimized defaults | Optimized defaults | | Backups | Self-managed | Daily (RDS automated) | Hourly (ZFS snapshots) | | Point-in-time restore | — | RDS restore | Instant (any hourly checkpoint) | | Schema migration testing | Direct to production | Direct to production | Clone-tested first | | Migration rollback | Manual | Manual | Tested on clone | | Instant database cloning | — | — | Seconds, any size | | Infrastructure costs | Your AWS bill | Included in subscription | Included in subscription | | SSO | — | — | SAML / OIDC | | Support | Standard | Standard | Dedicated | ## Schema Templates Instead of building your database from scratch, start from a proven structure. When creating a workspace, pick a template and get a fully structured database with entities, relationships, and pre-configured views — then customize to fit your needs. ### Available Templates **Online Store** — 10 entities including products, categories, shopping carts, orders, payments, customers, addresses, and reviews. Pre-configured views for product catalog, order management, and inventory tracking. **CRM** — contacts, companies, deals, activities, tasks, and notes. Relationships between contacts and companies, deal pipelines, and activity tracking. **Project Management** — projects, tasks, milestones, time entries, and team members. Task assignment, time tracking, and milestone views. **Image Gallery** — albums, images, tags, and metadata. Media organization with tagging and album views. **Content Management** — posts, categories, tags, authors, comments, and media. Blog-ready with moderation support and content organization. **Inventory** — products, warehouses, stock levels, suppliers, purchase orders, receipts, and shipments. Multi-warehouse inventory tracking. ### What's Included Each template comes with: - **Entities** with standard fields, appropriate types, and constraints - **Relationships** between entities (one-to-many, many-to-many) - **Pre-configured views** for common workflows - **Permissions** — role-based access out of the box - **Optional sample data** — see how the template looks with real data ### Template Marketplace Later, SchemaStack will support community-contributed templates. Share your own database designs, browse templates by industry, and install with one click. ## Integration Guides Step-by-step guides for connecting your auto-generated Workspace API to popular external services: - **Jotform** — submit form responses directly into your workspace tables - **Make (Integromat)** — build multi-step automations with your workspace data - **Google Sheets** — sync workspace data to/from spreadsheets - **n8n** — self-hosted workflow automation with your API - **Power Automate** — Microsoft ecosystem integration Each guide will walk through authentication (API keys), endpoint configuration, field mapping, and common recipes. ## Database Integration Guides Step-by-step guides for connecting SchemaStack to popular database providers. Each guide will cover connection setup, security considerations, and provider-specific tips. - **Supabase** — connect to your Supabase PostgreSQL database - **Neon** — serverless PostgreSQL with branching support - **Railway** — managed PostgreSQL and MySQL deployments - **PlanetScale** — MySQL-compatible serverless database - **Render** — managed PostgreSQL instances - **AWS RDS** — Amazon's managed PostgreSQL and MySQL - **Google Cloud SQL** — managed databases on Google Cloud - **Azure Database** — managed PostgreSQL and MySQL on Azure - **DigitalOcean Managed Databases** — simple managed PostgreSQL and MySQL Each guide will walk through obtaining connection credentials, configuring the workspace database settings, and verifying the connection. ## Future - **Workspace & organisation slug renaming** — allow changing slugs after creation, with automatic URL redirects and real-time SSE reconnection across all open sessions - **Webhooks** — get notified on data changes programmatically - **Export/import** — CSV, JSON, and more - **Custom widgets** — extend the data platform with custom column renderers - **Advanced analytics** — dashboards and reporting on your data --- **Tip: Feedback** Have a feature request? We'd love to hear from you. Reach out via [GitHub](https://github.com/jeelo). --- # Changelog Source: https://docs.schemastack.io/changelog All notable changes to SchemaStack are documented here. **See also:** [What is SchemaStack?](https://docs.schemastack.io/guide/what-is-schemastack.md) · [Getting Started](https://docs.schemastack.io/guide/getting-started.md) · [Key Concepts](https://docs.schemastack.io/guide/key-concepts.md) · [API Reference](https://docs.schemastack.io/api/index.md) · [Roadmap](https://docs.schemastack.io/roadmap.md) ## 2026-08-29 ### Fixed - **Background work that failed left no trace beyond a log line.** When the system finished changing your database but then failed to record that change in its own metadata, the message describing the work was discarded — leaving your database migrated, SchemaStack's picture of it stale, and nothing but a log line to say so. Those failures are now kept in a dead-letter queue where they can be inspected and replayed. Two related corrections came with it: only one worker now processes these messages at a time (a setting that had been configured for a long time under a name the message library silently ignored, so it had never actually taken effect), and a message that cannot be read at all is now preserved rather than dropped. Messages the system deliberately skips — ones it has no handler for — are still skipped, not treated as failures. - **A table with a two-part key always looked like it had drifted.** The quick drift check compares a fingerprint of your metadata against one taken from your database. The database side counted each column of a foreign key; our side counted the key once, using only its first column. A two-column key therefore never matched, and the check reported drift on every run, permanently, with nothing to show for it. Single-column keys were unaffected and their fingerprints are unchanged, so only workspaces that were already stuck report anything different now. - **Composite foreign keys created inside SchemaStack reported their own columns as changes made behind your back.** The second and later columns of such a key were invisible to drift detection, so it flagged them as foreign keys appearing in your database that SchemaStack did not know about. Imported keys were never affected. - **A column that gained or lost its primary key in your database kept the old answer.** Re-syncing refreshed everything about that column except whether it was a key — while the flags derived *from* being a key were refreshed, so the two disagreed. Anything reading it (query building, how relationships are classified, drift) trusted the stale value. - **An AI agent restricted to certain views could act on all of them.** MCP keys can be scoped to specific views. That restriction was only enforced for reading and writing *data* — every other operation checked that the target was in the same workspace, which a scoped key always passes. In practice a key limited to one view could delete a different one, drop its indexes, rewrite its saved filters, or rename it. It could also reset the whole workspace's schema. Every tool now checks the scope, and a key with no view scope is refused outright for workspace-wide operations like schema import and reset. If you have issued scoped keys, they are now genuinely limited to the views you chose. - **A foreign key renamed or restructured in your database is now reported.** Drift detection could only tell you when a foreign key's cascade rules changed. Renaming a constraint, re-pointing it at a different table, or splitting a two-column key into two single-column keys all went unreported. All of them are now named in the drift report. Foreign keys created inside SchemaStack are picked up on the next sync. - **Dropping a foreign key no longer makes the column disappear.** When a foreign key constraint was removed from your database, SchemaStack deleted the relationship — and left the underlying column hidden. The column and its data were still there, but the table rendered without them and without saying why. The column is now shown as an ordinary one. - **A table filter that matches nothing now says so.** Import filters are SQL patterns, where `_` matches any single character — so `repair_` matches every seven-character name rather than tables starting with `repair_`. That imported nothing and reported success. It now warns, and the API documentation explains the pattern rules. ## 2026-08-28 ### Fixed - **Accounts scheduled for deletion were never actually deleted.** Confirming account deletion starts a seven-day grace period and sends you an email saying your account will be permanently deleted on a given date. The sweep that performs that deletion existed and worked — but nothing ever ran it. No schedule, no trigger, only the tests called it. Every confirmed account has therefore been sitting in the pending state past its promised date, with its data retained. The sweep now runs hourly, so a grace period that has elapsed is acted on within the hour. If you requested deletion and are still able to sign in, your account will be removed on the next run. - **Deleting a column left a fragment of it behind, and the grid was never told.** When a column delete finished its database migration, the metadata row describing that column was supposed to be removed along with it. It never was — the removal failed instantly, the failure was caught and logged as success, and the rest of the work was committed anyway. Two consequences: an invisible leftover row per deleted column, and the "column deleted" event that tells every open browser to update was never sent, so the column lingered on screen until a refresh. Both are fixed. This was never a new problem — it dates back well beyond this week — and it stayed hidden because the test covering it had never actually executed. - **Reordering a column sometimes moved only that column.** Dragging a column to a new position is supposed to shift the others out of its way. In some cases the shift silently did nothing and reported success, leaving two columns claiming the same position and an order that looked arbitrary afterwards. Fixed. - **Schema drift now notices foreign keys, in all four directions.** Comparing your database against SchemaStack could only ever report a *changed* cascade rule on a foreign key. Adding one, removing one, or re-pointing one at a different table were all invisible — the check would tell you *something* had drifted without saying what. All four are now reported by name. Relatedly, relationships created inside SchemaStack were reported as newly-added foreign keys every time you checked for drift; drift now reads the relationship itself rather than one denormalised copy of it, so that false alarm is gone. - **"Reset schema" reported that it imported no columns.** The summary after a reset always said zero columns imported, however many it actually re-imported. It now reports the real number. (The count it *would* have used was also wrong — it counted every recorded change, including views created and relationships added, not columns.) - **A duplicate column could never be cleaned up.** If your metadata ended up with two entries for the same column — say `id` and `ID` — re-importing was supposed to resolve it. Instead the duplicate was quietly skipped and survived forever, and worse, each re-import handed it a fresh column in your view. Re-import now removes it, keeping the one that matches your database exactly, case included. - **Changing a relationship's edit mode ignored some of its own defaults.** Switching a relationship column to a different edit mode while also setting one of its behaviour toggles left the *other* toggles carrying the previous mode's values — so a column could end up in a state neither mode defines. Setting the mode now applies that mode's defaults, and anything you set explicitly still wins. - **A relationship can no longer be pointed at the wrong column.** Naming the target column when creating a relationship was accepted and then silently discarded unless you named two or more, so the check that refuses to point a foreign key at another foreign key never ran for the ordinary single-column case. Named target columns are now honoured and validated. If you don't name one, SchemaStack still finds the primary key itself — which is what the relationship picker now asks for explicitly, instead of sending the column you picked to *display*. ### Added - **Two new posts on the blog.** [The table that points at itself](https://schemastack.io/blog/self-referencing-tables) — modelling hierarchies where one table's foreign key points at its own primary key: categories with a parent category, employees with a manager, comments replying to comments. It covers building one from the picker, reading a parent and grandparent in the same grid, and what is still missing, including that a self many-to-many is not offered. [Importing the same file twice](https://schemastack.io/blog/import-without-duplicating) — naming the columns that identify a row so a re-import skips or corrects instead of duplicating, why your database needs a unique index on those columns for it to mean anything, and the honest asymmetry that MySQL cannot validate the columns you named at all. ### Fixed - **An AI agent can no longer change who is allowed to log in.** Configuring a workspace's external identity provider — the Auth0/Clerk/Firebase issuer whose tokens the API accepts — was reachable over MCP. It required a full-access key, so it was never open to a read-only one, but it was the wrong thing to delegate to a machine credential at all: trusting an issuer grants standing access to everyone holding that issuer's tokens, and it **outlives revoking the key that set it**, so "revoke the leaked credential" would have stopped being a complete answer. It is now an administrator action only, through the admin UI or the API with a signed-in session. A full-access key can still *read* the configuration, so an agent can see how a workspace is set up without being able to change who is trusted. - **Reading that configuration now states its own requirement.** It always needed a full-access key, but the check lived one layer down in the service rather than on the tool itself — so a later refactor could have widened it without anyone noticing. The rule is now enforced where the boundary is, with a test that fails if it moves. ### Changed - **A read-only AI agent can now preview a migration.** Asking "what would this schema change cost me?" used to require a **full-access** MCP key — the only kind that can also *run* the migration. That was backwards: the credential that cannot change anything is exactly the one that should be able to look first. The preview is now available to any key that can read the workspace. It stays a preview — nothing is applied, and a read-only key is still refused the moment it tries to make the change. The preview never revealed anything a read-only key couldn't already obtain (the column's definition from a normal read, the row count from any query), so this opens no new window onto your data. - **A view-scoped key can no longer preview a column outside its scope.** The same change tightened the other direction: because a preview names the table's row count and the views a cascading foreign key would touch, it now requires access to the view the column belongs to. A key restricted to certain views previously could preview any column in the workspace. ## 2026-08-27 ### Added - **A table can now point at itself.** Categories with a parent category, employees with a manager, threaded comments — self-referential relationships used to be read correctly on import but impossible to create from the relationship picker, and a lookup through one (`parent.parent.name`) was refused by a cycle guard. The guard turned out never to be protecting anything: a lookup follows a stored, finite path, and the depth cap is what actually bounds it. It is gone. The picker now offers your view's own table (pinned first, labelled "this table"), multi-hop self-lookups resolve — a category row can show its parent *and* grandparent — and the automatic reverse relationship ("rows pointing at this one") is created without tripping over the forward one's name. Two edges to know: a self **many-to-many** is still not offered, because its join table would derive two identical column names, and the picker builds paths at most five hops deep. ### Fixed - **Creating a new relationship could finish the database migration and then fail to register the relationship.** Since the composite-key feature in April, the message that travels back after a relationship's migration succeeded carried derived fields that could not survive being read back — reading them either failed outright (the relationship was never registered, though the foreign-key column had been added to your database) or, for composite keys, silently doubled the column list. The derived fields no longer travel; the values they were computed from always did. Caught by rewriting a test that had been passing without executing anything. Fixing it uncovered a second gap behind it: even once the relationship registered, the **visible column you asked for was never created** — the foreign key and the relationship appeared, the "Adding column…" notice resolved, and the grid showed nothing new. The column is now created with the name and display field you chose, and a browser-driven end-to-end test walks the whole journey: picker → migration → the related row's value showing in the grid. - **The Zapier triggers now actually poll newest-first.** The "New Row" and "Updated Row" triggers requested their sort as `field.desc` — a syntax the API never understood, and until today silently ignored, so the triggers were really polling in primary-key *ascending* order the whole time (a new row past the first page could be missed until older rows aged out). Today's stricter API refused the malformed sort with a `400`, which is exactly how the bug finally surfaced — the first Zapier poll after the deploy. Fixed in version 1.5.2 of the Zapier integration; existing Zaps pick it up once migrated to that version. - **A new relationship's foreign-key column no longer copies `SERIAL` from the key it references.** `SERIAL` is not a real type — it is an integer plus an auto-fill default — so a foreign-key column created as `SERIAL` came pre-filled on every existing row with a sequential id: each row silently "referencing" an arbitrary row, most often itself. The column is now created as the plain integer type underneath (`integer`, `bigint`, `smallint`), so existing rows start empty, the way an unset reference should. Rows auto-filled by the old behaviour are not rewritten — if you created a relationship on a table imported with serial keys, check those values before trusting them. - **Linked records work on UUID-keyed tables.** A many-to-many between tables with UUID primary keys was a trap: the chips displayed fine, and then opening the picker or toggling one failed, because every id was pushed through an integer parser. Ids are now read as whatever the key actually is — a number, a UUID, or text — on both sides of the link. Numeric keys behave exactly as before. Verified against real UUID-keyed tables on PostgreSQL; a MySQL table storing UUIDs as `CHAR(36)` has not been exercised and may still refuse. ### Added - **An import can now correct rows instead of duplicating them.** Importing only ever added, so running the same file twice gave you everything twice — which made a corrected export something to clean up after rather than simply re-import. Name the columns that identify a row (`conflictColumns`) and choose what happens when one already exists: keep the stored row and skip the incoming one, or overwrite its other columns. The columns you match on are never themselves rewritten, and skipped rows are counted separately from failures — a clean re-import of the same file should not look like it went wrong. **Two things to know.** The match columns need a **unique index in your database**: that is the only thing that lets a database recognise a duplicate, and without one the import is refused with a message saying so rather than silently duplicating. And on MySQL the match is broader than the columns you name — it reconciles on any unique key on the table, where PostgreSQL uses exactly the ones you gave. Available through the API today; the import dialog has no match-column picker yet, so a file imported through the app still only inserts. ## 2026-08-26 ### Added - **You can now see what an AI agent actually did.** An MCP key used to leave two traces: a "last used" timestamp that every call overwrote, and a monthly request count. Neither answers the question people ask after handing a key to an agent — what did it read, and what did it change? Every tool call is now recorded: which credential, which tool, which view it named, and whether it was allowed. **Refusals are recorded too**, and they are usually the rows worth reading — a key repeatedly bouncing off a view it has no scope for is how a mis-issued key announces itself. Read it at `GET /api/workspaces/{uuid}/mcp-keys/activity` as a workspace admin; a screen in the app will follow. Two deliberate limits: the **arguments are not stored**, only the identifier a call named, because a single create-record call carries a whole row and an audit table should not become a second copy of your data on our side; and a failure to write the record never fails the call it describes, so this is a strong record rather than a guaranteed one. - **Two conditional validation rules now work.** "If the account type is premium, a credit limit is required" and "if the membership level is gold, points must be between 1000 and 10000" — rules that only apply when another field says so. Both are enforced on every write path, the app and the generated REST API alike, and both are careful about the case that matters most: when the condition does not hold, the rule says nothing at all. The condition is matched as text, so a rule written against `true` still fires for a JSON boolean and one written against `PREMIUM` still fires for `premium` — matching on types instead would make the rule quietly never fire, which looks exactly like a row that passed. **These are not yet offered in the rule builder**, because it collects two fields and a conditional rule needs a field, a value to match against, and a target; for now they can be created through the API or an AI agent, and the builder will follow. - **Filters can say OR, `notIn` and `between`.** Filtering was AND-only, so "paid *or* urgent" meant two requests and a merge on your side. Alternatives are now written as numbered groups — `filter[or][0][status]=paid&filter[or][0][total.gte]=100&filter[or][1][priority]=urgent` — where conditions inside a group are AND-ed, the groups are OR-ed with each other, and the whole thing is AND-ed with any plain filters outside it. Two ordinary operators arrived with it: `filter[status.notIn]=archived,void`, and `filter[age.between]=18,65`, which is inclusive and wants exactly two bounds (one or three is a `400` rather than a guess). If your workspace uses row-level security, it is applied *outside* your groups — an OR in a query string can narrow what you see but can never reach another user's rows. A malformed group (no number, or a non-numeric one) is refused rather than ignored, because quietly dropping it would hand you back rows you meant to filter out. ### Fixed - **All seventeen validation rules are now enforced by the generated REST API.** Twelve of them were. `REQUIRED` and the four date rules (`PAST`, `FUTURE`, and their or-present variants) were checked when a person typed in the grid, when a CSV was imported, and when an agent wrote through MCP — but fell straight through on the REST API, which is the path a program uses. That made `REQUIRED` roughly useless where it matters most, because its whole purpose is to demand a value on every write while the column itself stays nullable. All five are enforced there now, with the same whole-day meaning for dates that the app already used. On update the rules behave the way partial updates need them to — a field your payload does not mention is left alone, but explicitly setting a required field to null or blank is refused. - **Four kinds of bad request stopped being answered with a misleading success.** A filter value that isn't valid for its column (`filter[age]=abc`) used to return `200` with an empty list, which reads as "no rows match" — a statement about your data rather than about your query, and impossible to tell apart from a typo. An unknown sort field was silently ignored, so you got primary-key order under a `200`. A filter across a linked collection whose target had no text to search was dropped entirely, so a filter written to *narrow* a result quietly returned every row instead. And asking to expand a relationship that doesn't exist surfaced as a `500`, the API blaming itself for a typo in your query string. All four now answer `400` and name the field at fault, matching how an unknown filter field was always treated. Asking whether a linked collection is empty now works even when there is nothing searchable on the far side, which it previously did not. - **MCP keys now enforce their access level and view scope on every tool — reads included.** An MCP key connected to an agent is meant to be a narrow credential, but several of the tools behind it checked only that the object being touched belonged to the key's workspace, not that the key was allowed to touch it. That gap is closed across the board. A **read-only** key can no longer create, edit, fill, or bulk-change rows, and can no longer add or delete indexes, constraints, or relationship configuration — those all require a **full-access** key now. A key **scoped to specific views** is refused every other view's data, including simply reading it or listing a relationship column's options, rather than being reachable if you knew a view's identifier. Two workspace-boundary holes were also closed: an entity-level constraint and a filter preset can now only be changed or deleted through a key belonging to their own workspace, where before a key could reach another workspace's by identifier. Nothing changes for a properly-scoped key doing what it was issued to do; the fixes only remove reach a key was never meant to have. Verified by 15 new tests that drive the live MCP endpoint with real reduced and view-scoped keys, alongside the existing key-security suite. ### Changed - **Three pieces of documentation that described things the product doesn't do have been corrected.** The bulk operations page advertised an Excel (`.xlsx`) export format — there is no spreadsheet writer and every export is a CSV, so the format table is gone and the page now says so plainly, along with two things it never mentioned (export headers are database column names, not the display names in your grid, and rows come out in primary-key order rather than your view's sort). For AI agents, two MCP tool descriptions were misleading in ways that would waste an agent's time: `import_schema` and `sync_schema` are the same operation under two names, which is now stated on both, and `sync_view_columns` only fills in views that have *no* columns — it cannot refresh a view whose columns have drifted, despite what the name suggests. ### Added - **Four new posts on the blog.** [The widget is not the column type](https://schemastack.io/blog/column-widgets) — how a field type sits on top of a plain database column, and why the currency or date formatting you see lives in the browser, so the API and exports return the raw value underneath. [The export is a snapshot, not the exit](https://schemastack.io/blog/export-downloads) — what a data export actually is when you already own the database it came from, and a candid list of its limits, including that the docs' promised Excel format does not exist and every export is really a CSV. [The join table is a real table](https://schemastack.io/blog/join-tables) — many-to-many relationships as genuine join tables with their own columns you can drill through, and the three ways a related row can be edited. [A key that can't see the whole workspace](https://schemastack.io/blog/mcp-key-scoping) — the access levels and view scoping that narrow an MCP key, written alongside the enforcement fix above. ## 2026-08-24 ### Added - **Validation rules that relate two columns are now enforced on the API.** Rules like "start date before end date" or "at least one of email and phone" used to be checked only when editing inside the app — a program writing through the REST API could create or update rows that broke them. They are now enforced on every API write: creating a row checks the whole payload, and updating a row checks the row **as it will be stored**, so a partial update cannot sneak a violation in through a field the rule also reads. Same rules, same messages, every writer. - **Real-time catch-up after a reconnect.** Updates now carry an id, and a reconnecting browser tells the server the last one it saw; the server replays what was missed and the "you may have missed updates" banner clears itself. When the gap is longer than the replay window — a night's sleep, a server restart — the banner stays and offers a refresh instead, because a partial history presented as complete would be worse. - **Real-time collaboration is now tested.** The four classes behind it had no tests at all, and the three end-to-end tests named after real-time each passed whether it worked or not. There are now 65, covering which connections an event reaches and which it must never reach, that an event belonging to no view is not sent to every view, that dead connections are cleaned up rather than accumulating, and who is allowed to open a stream in the first place. Each was checked by deliberately breaking the code it watches to confirm it fails — including opening two authorisation holes and confirming both are caught. - **A post on real-time collaboration.** [Someone else's keystroke](https://schemastack.io/blog/realtime-collaboration) — how an edit reaches the people who should see it and nobody else, why an event that belongs to no view is dropped rather than sent to every view, and what keeps a connection alive through a proxy. It is also candid about what prompted it. The broadcasters behind real-time had no tests, and the three end-to-end tests named after real-time all passed whether it worked or not — one checked for the absence of an error banner, which is also absent when nothing ever connected; one reloaded the page before checking the row had arrived; and one asserted a count was zero or more, which is true of every count. There are now 65 tests, and each behaviour was checked by breaking the code it watches on purpose to confirm the tests fail when it does. - **Three more posts on the blog.** [Rules the API can't skip](https://schemastack.io/blog/validation-rules) on validation — why a constraint belongs on the column rather than in a form, and which of the two tiers the generated REST API actually enforces. [One cell, five columns](https://schemastack.io/blog/display-groups) on display groups, including why the joined value never exists in SQL and what that buys you. [The arrangement you keep rebuilding](https://schemastack.io/blog/filter-presets) on presets, and why a preset is a way of looking rather than a permission boundary. ### Fixed - **Edits made through the API were invisible to everyone watching the grid.** Editing a cell, inserting a row, filling a column, batch-editing or changing linked records through the REST API saved correctly but never told anyone: the code that attributes the change to its author failed on a technicality after every API write, and the failure was caught and logged where nobody looks. Your own edits always looked live because your browser shows them optimistically — it was everyone else's screen that stayed still until refresh. Found the same day real-time collaboration got honest end-to-end tests, by those tests failing; fixed, and proven by the same tests passing. - **Row style colours are validated.** The dialog always offered a safe palette, but the API accepted any string as a colour and applied it as-is — invisible in dark mode at worst. Colours must now be six-digit hex. - **Two organisations using the same workspace name no longer share a real-time channel.** Workspace names became unique per organisation rather than globally, so two organisations can each have an "acme-store" — but the real-time channel was still addressed by the name alone, so two organisations sharing a name received each other's workspace-level notifications: views being created or deleted, member changes, key and configuration changes. **Records were never affected** — row and cell updates travel on a separate per-view channel with a globally unique address — and nobody gained access to anything. Fixed properly: every notification now carries the organisation it belongs to and is delivered only to that organisation's connections, so two organisations sharing a name each receive exactly their own updates, live. Forgetting the organisation is now a compile error on our side rather than a routing bug on yours. Found while writing tests for real-time collaboration, not through a report. - **Three cross-field constraint types could be chosen but never worked.** A constraint whose validator does not exist is skipped, and a skipped constraint reads as a row that passed — so "Unique Composite" and "Conditional Required" accepted everything, and "Fields Not Equal" was sent under a name the API does not recognise. All three are corrected: the two without validators are no longer offered, and Fields Not Equal now works because it is sent under the right name. Two rules that always worked but were offered nowhere, **Fields Equal** and **Exactly One Required**, are now available. If you configured a rule of one of the removed types, it was not protecting anything — worth re-checking that data. - **The row styles page said style rules are saved inside filter presets.** They are stored on the view, so everyone looking at that view sees the same styling and switching presets does not change it. The blog post that repeated the claim has been corrected too. - **The presets page documented three visibility levels.** There are two. "Shared" does not distinguish workspace members from guest share-link holders — if someone can open the view, they can see the preset. Anyone who chose "Team" expecting guests to be excluded should re-check those presets. - **Filtering by a date failed on every "Created At" column.** Adding a filter to a timestamp column that carries a time zone — which every automatic Created At and Updated At column does — replaced the grid with "'2026-08-17T00:00' is not a valid TIMESTAMPTZ value for this filter". The filter demanded a time zone offset written out in full, and no date picker produces one, so no value you could enter was acceptable. Dates are now read the way they are written: with an offset, without one, or as a plain date. A value with no zone is read as UTC. - **"On or before" a date excluded that whole day.** Once a date filter was accepted, it was compared against midnight, so filtering "on or before 17 August" hid everything that happened *during* 17 August, and "is 17 August" matched only a row stamped exactly 00:00:00 — which no row ever is. A date now means the day: on or before includes all of it, after excludes all of it, and "is" matches any moment within it. Picking a date in a filter no longer asks for a time you did not want to specify; filtering to an exact instant is still available through the API. - **Conditional row style rules on a date column coloured nothing.** A rule like "Created At — greater or equal — a date in August" saved without complaint and then never fired, whatever date you chose, because the comparison was being done on the *text* of the stored timestamp: "2026-08-20T09:15" sorts before "8/17/2026" for the same reason "2" sorts before "8". Dates, times and timestamps are now compared as moments, on the same whole-day basis as the filters above, and the value box offers a date picker so a rule and a filter written the same way now agree. **Existing rules that hold a hand-typed date need the date re-picked** — 8/17/2026 and 17/8/2026 are the same day written by different people, and guessing which half is the month would silently colour by the wrong day. - **Several column types were being compared as the wrong kind of thing.** In row style rules: a file or image condition could never match, because the cell holds a file reference and the rule was comparing against the words "[object Object]"; a rule naming a dropdown option by the label shown in the grid did not match the value stored underneath it; whole-number columns that Postgres reports as `smallint` were compared as text, so 10 was "less than" 9; and a truth value written as `t` or `y` meant *false* here while meaning true on the server. In filters: a price column was compared through a rounded floating-point value, so "equals 19.99" could miss the row holding 19.99; `double precision` and `real` columns were sent as text and refused the query outright; and a filter on a `uuid` column failed for the same reason. All corrected, and each one now has a test naming the failure it replaced. ## 2026-08-23 ### Added - **Row styles can set the text colour, not just the background.** The setting existed in the data model and the grid already knew how to draw it, but the dialog never had a control for it, so it was unreachable unless you called the API directly. There are now eight text colours beside the eight backgrounds, each shown as the letter A in the colour it applies. Like the backgrounds they carry a separate light and dark value rather than a single fixed one, so a rule set up in daylight still reads at night — and each was checked for contrast against white, against its own pastel background and against the dark surface, so no combination of the two palettes produces text you cannot read. - **A post on conditional row styles.** [Why your green rule never fires](https://schemastack.io/blog/row-styles) — why two overlapping rules give you the wrong colour unless you order them narrowest first, what a condition can target (including formula columns, which compare as numbers even when they don't declare a numeric type, and relationship columns, which compare the label you can read rather than the key underneath it), and the four places the in-grid styling engine quietly disagrees with the same filter sent to the API. Written after the two files that decide every row's colour turned out to have no tests between them; they have 49 now. - **A post on owning the list rather than renting it.** [The list that isn't in your email tool](https://schemastack.io/blog/own-the-list) — why signups are better held in a database you own, with the email tool downstream of it rather than in charge of it, and what that changes the day you switch providers. - **A post on formula columns.** [The column that isn't there](https://schemastack.io/blog/formula-columns) — writing an expression gives you a column that is computed on every query and stored nowhere, so nothing can go stale and nothing needs migrating. It covers what you can write, why the validator masks string literals before checking them, how aggregating across a relationship works, and that every schema sync used to delete these columns until two days ago. - **A post on relationship columns.** [The other table's data, without writing the join](https://schemastack.io/blog/relationship-columns) — how a foreign key becomes the related row's name, a count of the rows on the far side, or several values at once, and what happens when a lookup path is wrong. It also admits that filtering a relationship column was returning an error until two days ago. - **Two posts on the Zapier integration.** [Your database, wired to 7,000 apps](https://schemastack.io/blog/zapier-integration) covers what the integration does — triggers when a row is added or changed, actions to create and update rows, a search — and is honest that nothing is copied anywhere: every trigger reads and every action writes the database you already own. [Polling is not real-time](https://schemastack.io/blog/polling-is-not-realtime) explains why a change appears in the grid instantly and in a Zap some minutes later, what a poll actually reads, and which tool to reach for when that difference matters. ### Fixed - **A conditional style rule with an empty comparison value coloured rows anyway.** A style rule is saved as soon as you pick a column, so a rule you had not finished writing was already being evaluated against every row. With the value left blank, *equals* matched every row holding a zero and *greater than* matched every row holding any number at all — an empty value was being read as the number zero. An unfinished condition now matches nothing, which is the safer of the two options when the result is something you can see. Found by writing the tests for the post above. - **The Zapier connection screen offered help that went nowhere.** All three links beside the API key and slug fields pointed at a domain that does not resolve, and one of them at a page that has never existed — so anyone setting up the integration was sent nowhere at exactly the point they needed instructions. One had been broken since the production host changed months earlier. Fixed and published as integration version 1.5.1. Found because writing the post above meant setting the integration up the way a new user would. ### Changed - **Fewer third-party requests from your browser, and crash reports no longer leave our domain.** The app was loading a performance-monitoring SDK that sent data to a third-party host on every page. It has been removed — it never produced anything usable, because the host is on tracker blocklists and most of our users run one. Error reports, which are worth keeping, now travel through `schemastack.io` instead of a third-party domain: same information, one fewer party your browser talks to, and it works whether or not you block trackers. Analytics on the public site has worked this way for a while; the app's error reporting simply had not caught up. ### Fixed - **Crash reports were arriving unreadable, when they arrived at all.** Two separate problems. Reports from anyone running tracker protection never left the browser at all, which in practice meant almost none of them did — the project had not received a single report since it was set up. And those that would have arrived pointed into minified code, naming a line in a bundled file rather than the code that failed, because the build published no source maps. Both fixed: reports are sent through our own domain, and readable positions are uploaded at build time. The maps themselves are never published, so nothing extra is served to you and the source stays private. - **Real-time updates did not come back after a laptop had been asleep.** Leaving a workspace open overnight left "Reconnecting to real-time updates…" on screen and nothing arriving. Three separate faults, all needing a gap in time to appear, which is why none of them showed up in ordinary use. The connection dies during sleep without reporting an error and without any timer running, so the app still believed it was connected and every reconnect trigger skipped — it now treats a connection that has been silent longer than the heartbeat window as dead regardless of what it thinks. The access token expires during the night, and the streams bypass the layer that normally notices that and refreshes, so every retry re-sent the same dead token forever — a stream now refreshes on the spot, and stops with a clear state if the session is genuinely over. And a wake raises two reconnect triggers at once, so a second attempt would start while the first was still connecting, and the older one would then close the newer one and start the cycle again — attempts are now identified so an outdated one cannot interfere. ## 2026-08-22 ### Fixed - **Row-level security was not enforced when reading a single record by id.** Fetching one row by its id does not pass through the filter that narrows list queries, so a separate check confirms the row belongs to the caller. That check looked the value up by the database column name — but generated entities expose columns under their field name, so `tenant_id` is reached through `getTenantId`. For every column whose names differ, which is every snake_case column, the check found no method and **allowed the row through**. An end-user of an application using external identity could therefore read any row whose id they could guess, including other users' rows. The check now resolves the field name from the entity's own metadata, and refuses the row when it cannot establish that the row matches instead of allowing it. Both halves matter: the lookup makes the check work, and failing closed means a future mismatch is a refusal rather than an opening. - **Filtering the grid by a relationship column returned an error.** The value was converted to the type the cell displays — the related row's label, so text — while the filter is applied to the numeric key underneath, and the database refused the mismatch. What you saw was "An unexpected error occurred" with nothing naming the problem. Fixed, along with a second case in the same place: a value that cannot be used for a column at all, like a word in a filter on a number, also produced that error. It now tells you which value and which type it could not use. Every column type and every operator is now covered by tests — 90 of them — because a filter that fails on one type and works on another is the kind of thing that only shows up when somebody hits it. - **Row-level security never worked on a normally named column, and did not constrain writes.** A policy is written against a database column — `owner_id` — while everything that consumes it speaks the entity's field name — `ownerId`. Because the two were never translated, three things were broken at once. Listing records answered a server error rather than a filtered list. Creating a record looked for the policy's column in the request body, never found it, and let a caller set that field to whatever they liked. Updating a record likewise never noticed the field, so it could be changed to another user's value. Reading one record by id was the case fixed earlier the same day. The translation now happens once, and a policy naming a column the entity does not have is refused rather than quietly producing an unfiltered query. If you have row-level security configured, it is worth re-testing now that it works — and worth knowing that until today, listing was failing loudly rather than leaking. - **Any organisation member could change the organisation's subscription tier.** The code documented itself as owner/admin only and enforced nothing. It never reached another organisation — the organisation comes from the caller's own token — but it let a member act above their level. Now restricted to owners and admins. - **An MCP tool asked for six fields that do not exist.** `update_entity_api_config` advertised `enabled`, `allowRead`, `allowCreate`, `allowUpdate`, `allowDelete` and `maxPageSize`. The configuration has none of those — it has default expansion, expansion depth, default fields, expandable relationships, filterable fields and a rate limit. Unknown fields are silently ignored, so an agent following the description sent six values, changed nothing, and was told it succeeded. The description now names the real fields. - **The filtering reference documented an error that does not happen.** A filter value that cannot be converted to the column's type was described as returning `400`; it actually returns `200` with an empty result. The page now says so, and flags it as a defect rather than a feature. A filter on an unknown *field* does correctly return `400` — that part was accurate. - **Database views were listed as not imported.** They are imported and readable, just not editable, and drift checking treats them as tables. Moved to partially supported with the real behaviour described. ### Changed - **Two blog posts corrected.** [The REST API you didn't write](https://schemastack.io/blog/instant-rest-api) and [Everything you can ask the API for](https://schemastack.io/blog/filtering-and-sorting) both showed filter syntax as `filter[field][operator]=value`. The real syntax is `filter[field.operator]=value`, and the bracket form returns `400`. The second post also listed a misspelled operator as failing silently; it returns `400` too. Corrected, and the verification note on that post now records that its examples were run against a live API rather than inferred from the query-layer tests. ### Added - **Four more posts on the blog, and every topic now has one.** [Your spreadsheet, in a real database](https://schemastack.io/blog/csv-import) on CSV import and what happens to the rows that don't fit; [Everything you can ask the API for](https://schemastack.io/blog/filtering-and-sorting), the full query surface including the three places it guesses where it should refuse; [Sometimes the right answer is no](https://schemastack.io/blog/migration-dry-run) on estimating what a migration will lock before running it; and [Half our readers aren't people](https://schemastack.io/blog/written-for-machines) on why every page here has a Markdown twin. ### Fixed - **The CSV import size limit is now enforced, not just documented.** The import guide said files up to 100 MB were supported and nothing checked it, so a larger file would begin parsing and fail somewhere in the middle. The file is now refused when you choose it, with its size and the limit in the message. Found while fact-checking the blog post about CSV import, which is the point of writing them that way. ## 2026-08-21 ### Improved - **Connecting an AI client no longer starts with hunting for a workspace UUID.** Give Claude, ChatGPT or any MCP client `https://schemastack.io/mcp` — nothing after it — and the consent screen now lists the workspaces you can reach and lets you pick one. Naming the workspace in the URL still works and still skips the list, which is what you want when writing setup instructions for other people. The list is only ever what you could already open: an organisation owner or admin sees every workspace in the organisation, everyone else sees the ones they are a member of, and a workspace in maintenance mode is offered to administrators only. Picking from the list grants nothing by itself — the choice goes back through exactly the same check as a workspace named by the client, so it is who decides that changed, not what gets verified. ### Fixed - **Workspace storage settings were readable by anyone with an account, and couldn't be saved by anybody.** The four S3 storage-configuration endpoints documented themselves as owner/admin only and enforced nothing, so any signed-in user who knew a workspace's ID could read another workspace's storage settings — bucket, region, endpoint and access key ID, though never the secret, which is write-only. The same endpoints now require workspace administrator access. Saving a configuration was separately broken for everyone: every attempt failed with a server error, which is why no workspace in this deployment has ever had one saved. Both had the same cause. The save path reloads the workspace immediately after writing, and without a permission check nothing had loaded the workspace's organisation first, which made the database layer fail on the reload. The missing check was hiding a broken endpoint, and the broken endpoint was hiding the missing check. Fixed independently of each other, so neither depends on the other to work. - **A consent flow that could have handed out access to the wrong workspace.** When an administrator registers an OAuth2 application, the application carries the workspace it was registered for — and consent took that as settling the whole question, never checking whether the person signing in could reach that workspace at all. Anyone with an account and a client ID could therefore have approved a connection to a workspace they had nothing to do with and been handed a working token for it; client IDs are not secrets. **Nobody was affected: no OAuth2 application had ever been registered in this deployment, so no authorization code and no token was ever issued through the affected path, and there is nothing for you to check or revoke.** We found it while building the workspace picker above, confirmed the defect end to end against a test database, and confirmed against production that it was never reachable there. Fixed: consent now verifies the signed-in person's access on both paths, not just the one where the workspace is named in the request. Applications that register themselves — the route the AI connector uses — were never affected, because that path always checked. - **An AI agent with read-only access could reset a workspace's schema.** Four MCP tools that rebuild schema metadata — reset, import, sync and view-column sync — checked that the credential was valid but never checked what it was allowed to do. Any enabled key could run them, including read-only ones, and including the OAuth tokens that are capped below schema level precisely so this can't happen. They now require full access, and a workspace that is locked or read-only refuses them outright. Nobody's data was ever at risk — these tools rewrite SchemaStack's metadata, not your tables — but view configuration, validation rules and formula columns were. Reading drift is still allowed at read-only, because looking changes nothing. - **Schema sync was deleting formula columns.** Computed columns exist only in SchemaStack — there is no database column behind them — and the documentation said sync preserves them. Two of the three code paths agreed; the one that decides what to delete did not, so every sync quietly removed them along with their validation rules. Fixed. - **Imported database views reported drift forever.** Importing reads tables and views; drift checking read only tables, so any imported view looked permanently "removed from the database" and the quick check never came back clean. All three now agree on what they are looking at. - **CSV import now validates every row, and tells you which one failed.** Imported rows were written without the validation a typed-in cell gets: required fields, maximum lengths, email formats and cross-field rules all went unchecked until the database itself objected. And because rows were written 500 at a time in one statement, a single bad row failed all 500 and the error blamed the first row of the batch. Each row is now validated first, rejected rows are reported individually with their row number and skipped, and the rest of the file imports. ## 2026-08-20 ### Added - **SchemaStack has a blog — and everything on it is verified before it publishes.** One feature per post at [schemastack.io/blog](https://schemastack.io/blog), each carrying a public stamp saying when its claims were last run against the real product and by what — a test suite, the production synthetics, or a hand-run session. The build refuses to publish a post without that trail. Four posts at launch: [the agent connector](https://schemastack.io/blog/agent-connector), [the spreadsheet that builds your schema](https://schemastack.io/blog/spreadsheet-builds-schema), [guest links](https://schemastack.io/blog/guest-links) and [external identity with row-level security](https://schemastack.io/blog/external-idp-rls). There's an [RSS feed](https://schemastack.io/rss.xml), and every post is also published as Markdown for AI assistants, like the rest of the site. - **Add SchemaStack to Claude or ChatGPT with a URL** — hosted AI clients have nowhere to paste an API key, so they sign in instead. Give the connector `https://schemastack.io/mcp?workspace=` and the rest happens on its own: the endpoint answers an unauthenticated request with a pointer to its discovery documents, the client registers itself, your browser opens the SchemaStack consent screen, and approving hands it an access token. Nothing to configure and no key to copy. The consent screen names the application, the workspace and what it is asking for — and says plainly when an application registered itself rather than being added by an administrator, because anyone can register under any name. - **MCP accepts an OAuth 2.0 access token, not just an API key** — an application acting for a person who signs in can now drive MCP with the same access token the Workspace API takes, instead of needing a workspace key of its own. Point it at `https://schemastack.io/mcp?workspace=`; the token already says which workspace it is for, and if the parameter disagrees the request is refused, so a token for one workspace cannot be aimed at another. An access token is capped lower than a key on purpose: `workspace:read` grants Read-Only and `workspace:write` grants Data-Only, never Full — consenting to a scope called "write" means letting an application write records, not letting it drop a column on a database you own. Schema changes still require an `mcp_` key. The workspace access level remains the ceiling, and disabling an OAuth2 client now cuts off its MCP access immediately rather than leaving its live tokens working until they expire. - **A machine-readable catalog of the programmable surfaces** — [/.well-known/api-catalog](https://schemastack.io/.well-known/api-catalog) lists the Workspace API and the MCP server in the format described by RFC 9727, each with its documentation, its credential guide and a health endpoint. For the Workspace API it points at the OpenAPI 3.0 description, which every workspace has been publishing all along at `/api/v1/{orgSlug}/{workspaceSlug}/_openapi` — generated from that workspace's own schema, no credential required, and now discoverable rather than something you had to be told about. The catalog is announced in a `Link` header on every page of the site. ### Improved - **The website now says what SchemaStack actually is** — the homepage led with data ownership and left the two things people choose SchemaStack for in the small print. It now leads with the point: using the spreadsheet builds real SQL schema, with previewed migrations, in a database you own. The AI-agent connector shipped this week has its own section, agencies get one describing client delivery (organisation per client, guest links, the client's own login provider with row-level security), and an honest feature-for-feature table compares SchemaStack with Airtable, NocoDB/Baserow and Directus at a glance. - **A Directus comparison page** — Directus is the closest thing to SchemaStack and the comparison people most deserve to read, so [it now exists](https://schemastack.io/vs-directus): candid about Directus's maturity, GraphQL and automation flows, and clear about where SchemaStack differs — schema built from the spreadsheet with migration dry-runs, and agent access with consent and instant revocation. - **Revoking an application's access now takes effect immediately** — access tokens are not stored anywhere, so revoking a grant used to stop the next token being issued while the one the application already held kept working until it expired, up to an hour. Revoking now records the moment it happened, and a token from before that moment is refused on both MCP and the Workspace API. This closes the gap for applications that added themselves, which a workspace administrator cannot disable because the application does not belong to their workspace. Reusing a stolen refresh token cuts off its access tokens the same way. - **An MCP request without a credential is now refused outright** — it used to answer `200` with an error inside the response, which also meant the full tool list was readable by anyone who asked. It now returns `401` with a pointer to how to authenticate, which is both what the protocol expects and one less thing given away. - **Revoking an application's access no longer reaches other workspaces** — with applications that belong to no single workspace, "revoke sessions" would have cut off that application everywhere it was used. Revoking now affects only the workspace whose administrator asked. ### Fixed - **Signing in with an application that has no workspace of its own now works** — exchanging an authorization code, and later refreshing it, read the organisation of the workspace being granted. That detail used to arrive by luck: it was loaded on the way to the application's own workspace, which was always the same one. For an application that registered itself there is no such workspace, so nothing loaded it and the exchange failed outright. Found by testing the flow end to end rather than in pieces. - **OAuth 2.0 was never documented** — the Workspace API reference covered API keys and external identity providers, but not the OAuth flow, despite it having shipped. [API authentication](https://docs.schemastack.io/api/authentication.md) now covers registering an application, the endpoints, what each scope grants on the API and on MCP, and how to revoke access. The roadmap still listed it as planned; that entry is gone. - **The MCP security notes described the wrong credential** — the AI integration guide said MCP authenticated with "the same JWT token you use for the REST API" and that your account roles governed what an assistant could do. Both predated MCP API keys: access is granted by an `mcp_` key or an OAuth token, a browser session token is not accepted at all, and the workspace MCP access level is the ceiling. The page now says so, and warns that a key is a workspace credential rather than a personal one. ## 2026-08-19 ### Added - **The public site answers in Markdown** — every page on schemastack.io now has a Markdown twin: append `.md` to the path, or send `Accept: text/markdown`. A new [llms.txt](https://schemastack.io/llms.txt) indexes every page, the documentation and the machine interfaces in one file. An AI assistant asked about SchemaStack no longer has to reconstruct the answer from a marketing page's HTML — and the FAQ answers, which are collapsed in the browser and therefore absent from the HTML, are included in full. - **The documentation is published as Markdown too** — every page on docs.schemastack.io is now available at its own URL plus `.md`, indexed in [llms.txt](https://docs.schemastack.io/llms.txt), and available in full as a single file at [llms-full.txt](https://docs.schemastack.io/llms-full.txt). VitePress rendered the Markdown to HTML and discarded it; since the source is already the format AI assistants want, it is now published alongside. Links between pages point at the Markdown versions, so an assistant following a reference stays in Markdown rather than being dropped back into HTML. - **Credential instructions for AI agents** — [/.well-known/auth.md](https://schemastack.io/.well-known/auth.md) states which credential each surface takes (an `mcp_` key for MCP, an `sk_live_` key or OAuth 2.0 token for the Workspace API), that none of them are self-service, and that MCP access is disabled per workspace until an administrator raises it. An agent that reads it first stops retrying a `403` that will never succeed. - **Content signals in robots.txt** — the public site now declares explicitly that automated systems may index it, cite it and train on it. The declaration covers only what the site serves; customer data lives in the customer's own database and was never reachable from here. ### Fixed - **Unknown addresses return a real 404** — every unmatched path answered with the landing page and a success status, so a mistyped URL looked like a working page, and crawlers read the site as having a large number of duplicate homepages. Unmatched paths now return a proper 404 page. Every linked page and every app route is unaffected. ### Improved - **API and MCP responses point at their own documentation** — responses from `/api/*` and `/mcp` carry a `Link` header naming the relevant reference page and the credential guide, so a client that lands on an error has somewhere to go without a web search. - **The landing page FAQ is a proper disclosure** — each question now reports whether its answer is open, so a screen reader announces the state instead of leaving it implicit in a rotating chevron. ## 2026-08-17 ### Added - **Create another organisation from the UI** — the option existed in the API but was unreachable: the page redirected you to the dashboard once you already belonged to an organisation. It now lives at the bottom of the organisation switcher in the toolbar, and that switcher is visible even when you only have one organisation. ### Fixed - **Workspace names no longer collide with other people's** — workspace addresses are checked per organisation, but the database required them to be unique across every organisation. So a name could be confirmed as available and then fail with "An unexpected error occurred", because an organisation you cannot see had already used it. - **Workspaces no longer hold database connections after being deleted** — a deleted workspace kept its connection pool open indefinitely. On a managed database, which is shared across an organisation and capped at five connections, a few deletions could exhaust it and leave the remaining workspaces unable to load data. - **A workspace at its connection limit now says so** — it previously surfaced as "An unexpected error occurred". The API returns a clear, retryable error instead. - **Two workspaces can share a managed database again** — connection pools were sized larger than the database itself allowed, so a second workspace on the same managed database was always refused. ## 2026-08-16 ### Fixed - **Resending a workspace invitation works again** — the Resend button on a pending member in Workspace → Members called an endpoint that did not exist and always failed. Resending from Organisation settings was unaffected. - **Invitation links no longer point at an unreachable address** — the link returned when inviting someone was built from the server's internal bind address, producing `https://0.0.0.0:8443/...`. The link in the invitation email was always correct; only the API response was affected. ### Removed - **Invitation links are no longer returned in API responses.** The link contains the invitation token, which is the proof that someone controls the invited email address — and for an invitee who does not yet have an account, that token is enough to create the account and set its password. Returning it to whoever sent the invitation defeated the email check. The token now only ever reaches the invitee by email. ## 2026-08-15 ### Added - **Paste a connection string when connecting a database** — the database form now takes the connection string your provider gave you and fills in the fields itself: host, port, database, username, password and SSL mode. Connection URIs (`postgresql://…`, `mysql://…`), JDBC URLs and `libpq` keyword form (`host=… dbname=…`) are all understood, and any parameters we don't recognise are kept rather than dropped. Available both when creating a workspace and when editing its database settings. - **Number and date display formats in the properties panel** — the formatting options that previously had to be set through the API are now editable in the UI: number format (currency, percent or grouped number) with decimals, prefix and suffix on Integer and Decimal columns, and a date format on Date columns. ### Improved - **Display options sit with the other display settings** — casing, prefix/suffix, number and date formats and the Select options editor have moved out of the Schema Settings section, which warns that changes need confirmation, and into Display Settings, where they belong: they save immediately and never touch your database. The formatting controls are tucked behind an "Advanced" toggle, since most columns never need them. - **MCP setup instructions now work** — the connection guide gave a placeholder hostname and asked for the wrong kind of credential, so following it exactly could not succeed. It now shows the real endpoint, the `mcp_` API key (created from Workspace → MCP in the admin app) and the full list of 57 tools, up from the 18 previously documented. - **Connecting your database has its own guide** — a new page covering the accepted connection string formats, what each field maps to, how `sslmode` translates, where to find the string for common providers, and why a direct connection is preferable to a pooled one. ## 2026-08-14 ### Added - **Preview column changes before applying them (API)** — a new `preview_column_change` MCP tool answers "would this change require a database migration?" without touching anything: metadata-only changes return `migrationRequired: false`, schema-affecting ones return the full impact analysis (estimated duration, row count, read/write blocking, SQL preview). - **Conditional formulas** — computed columns can now use standard SQL `CASE WHEN … THEN … ELSE … END` expressions, the vendor-portable conditional (previously only MySQL's `IF()` worked). Formulas are validated structurally (balanced CASE/END, no stray fragments) and against your own database before saving. This makes computed columns the full answer for custom display transforms — e.g. `CASE WHEN maiden LIKE '%née%' THEN maiden ELSE CONCAT('(née ', maiden, ')') END` — evaluated server-side so the grid, API, exports, and sorting all agree. - **Select options editor** — columns using the Select widget now have an Options section in the column properties panel: add, edit, and remove value/label pairs directly in the UI. - **Date display formats** — date columns can render as "16 Apr 2014" (medium) or "04-16-2014" (US) via a widget option, instead of the raw stored value. Display-only. - **Number display formats** — decimal and integer columns can carry a display format in their widget options: currency (thousands grouping, `10,300.00`), percent (`14%`), or a generic number format with configurable decimals, prefix, and suffix. Formatting is display-only — stored values and editing are unchanged. - **Composite cells via display groups (API)** — columns can be grouped into one cell (e.g. invoice number + date) by setting the same `displayGroup` through the column-update API; grouping was previously only reachable from the UI. - **Aggregate columns** — a column can now summarize related records: count them, sum/average a field, or join them into one cell with a custom per-record expression, a row filter, and a separator. Example: "all sent invoices, formatted like `APT-00017/2025 3 Mar 2025`, newest first, separated by semicolons" — computed in your database by a server-generated query, so the API, exports, and grid all agree. - **Required fields without database changes** — a new REQUIRED constraint makes a field mandatory on every write (UI, API, MCP) while the database column stays nullable. Useful when legacy data predates the rule or the column carries a database default: new writes must provide the value, old rows stay valid, and no migration runs. - **Form visibility separate from table visibility** — each column now has a "Form visibility" setting (Same as table / Always in forms / Never in forms), so a field can be hidden in the grid but still editable in the create/edit form — or shown in the grid but kept out of forms. - **Cross-entity formulas** — computed columns can reference a directly related record's fields (`category.name`, `customer.discount`) and the value is joined in server-side. One relationship hop, to-one relationships; collections still use relationship display columns. - **Text display options** — text columns can render with a casing transform (UPPERCASE / lowercase / Title Case) and a prefix/suffix, configured in the column properties panel. Display-only: stored values and editing are unchanged; multi-value relationship chips keep their raw labels. - **Column list search and paging (API)** — `list_columns` accepts `nameFilter`, `limit`/`offset` (with a total-count envelope), and `compact=true` for a slim per-column summary — a 130 KB response on a large view becomes under 1 KB. - **Rename a column's API key** — the key a column uses in API payloads (`dataKey`) can now be changed explicitly, e.g. to replace an imported hash name like `fk9fdd0d…Venue` with `assessmentVenue`. Saved filters, presets, and style rules are unaffected (they reference columns internally by ID); external API consumers see the new key. ### Improved - **AI message limits now persist** — the daily AI chat allowance per member is counted from stored chat history, so it survives service restarts and applies consistently. When the limit is reached, the API returns a clear message with your limit — or connect your own AI via MCP for unlimited use. - **Grouped cells show formatted values** — columns merged into one cell (display groups) now render each member through its display options: select labels, Yes/No, currency, and date formats apply inside the group ("Yes, 14 Mar 2025" instead of "1, 2025-03-03"). - **Formulas can contain any text in quotes** — string literals like `'SA Price with surcharge'` no longer trip the SQL-keyword safety check (words like "with" or "set" inside quotes were wrongly rejected); unterminated quotes are now caught with a clear error. - **Readable relationship names on import** — schemas whose foreign-key constraints carry auto-generated names (Doctrine `FK_`, MySQL `*_ibfk_N`, …) now get relationships named after the join column (`enrolment_id` → `enrolment`) instead of the hash, so API field names come out readable from day one. - **Validation errors are readable over the API** — error messages now list the actual violations ("vatPercentage: VAT percentage is required") instead of a bare "HTTP 400 Bad Request". ### Fixed - **Invitations must be accepted before access** — an invited member could previously reach organisation data before accepting the invitation; only active memberships grant access now. - **Editing cells on views with linked columns works again** — views showing columns from related tables (via relationships) rejected every cell edit with false "field is required" errors for the related fields. Related display columns are no longer validated as if they were part of the row being written. - **Columns with database defaults are validated again** — a column with a database default previously skipped all validation rules, so out-of-range values slipped in and clearing the field silently stored NULL (database defaults don't re-apply on updates). - **Computed columns keep their settings when updated** — updating a formula column dropped display group, position, and other display settings from the request; they now persist like on any other column. - **Per-column role permissions are enforced on cell edits** — explicit column-level write permissions were only checked for bulk actions; single cell edits now respect them too. ## 2026-08-13 ### Added - **Select fields** — a column can now present a fixed set of labeled options. Configure `widgetOptions.options` (value + label pairs) on a column and the grid shows the label ("African" instead of `african`), while cell editing and the Add Row form offer a dropdown. Selects are purely presentational: they work on top of text, integer, and yes/no columns **without changing the column's database type** — switching an existing column to a select never triggers a migration. ### Fixed - **Formula columns now work on MySQL workspaces** — the create-time validation probe sent PostgreSQL-quoted SQL to every database vendor, so MySQL rejected every formula regardless of content (with an error confusingly labeled as coming from PostgreSQL). The probe now quotes identifiers per vendor and error messages name "the database" instead. - **Formula columns that return text are no longer mis-detected as yes/no** — an expression like `IF(year <= 2020, CONCAT('APC', id), CONCAT('apt', id))` was classified as boolean because it contains a comparison, and every value came back as "false". Comparisons inside a function call are now recognized as condition arguments, not the result. - **Changing a column's widget no longer corrupts its stored metadata** — switching widgets applied the new widget's create-time storage defaults (type, length, nullable) to the existing column's metadata, silently desyncing it from the real database schema. Storage defaults now apply only when creating a column. - **Yes/no detection at import is now based on your data** — MySQL reports `tinyint(1)` columns as boolean even when they hold values like months (1–12). Import now samples each boolean-looking column and keeps it numeric when out-of-range values exist, so a checkbox can never overwrite real data with 0/1. - **Schema drift checks work over MCP/API keys** — the quick and full drift checks crashed with an internal threading error when called through an API key. - **Row color rules can be saved through the API** — the update-view API silently ignored the `styleRules` field; rules saved through it now persist. - **Workspace responses include their views again** — API and MCP workspace lookups with `includeViews` always returned an empty list even though the views were loaded. - **Schema changes work over MCP keys** — any column change that needed a migration crashed with an authentication threading error when requested through an MCP key. ### Improved - **Clear warnings when MCP access is off** — creating an MCP key while the workspace's MCP access level is Disabled now warns in the key dialog (before and after creation) and shows a banner over the key list, instead of silently producing a key that every call rejects. - **API tool documentation matches reality** — several MCP tool descriptions had drifted from the actual accepted fields (bulk selection modes, column position and select options, view style rules, preset column overrides). All are now in sync. ## 2026-06-14 ### Added - **Public demo workspace at schemastack.io** — the marketing site now links straight into a fully editable demo. Sign in with `demo@schemastack.io` / `demodemo` (or use the "Try our live Acme Store demo →" button to skip typing the email). You land in the same editor the real product uses, with every affordance visible (Add Row, cell edit, properties panel, filter presets, import mappings). Attempts to save are intercepted with a friendly "Demo workspace — changes aren't saved. Sign up at schemastack.io to create your own workspace." snackbar instead of a generic permission error. Curators editing the demo from a separate non-demo account are unaffected; their changes go live for visitors on the next reload. ### Improved - **Closed write-side gaps for read-only-by-identity users** — three service-layer write paths that previously had no permission check beyond org membership are now gated: creating/updating/deleting filter presets, creating/updating/deleting CSV import mappings, and adding/removing/changing workspace members (the last as defense-in-depth — the org-admin check already blocked non-admins). For normal users this is invisible; it only matters for accounts the backend treats as read-only. ## 2026-05-13 ### Fixed - **A second formula column on the same view no longer disappears from query results** — adding two formula columns whose display names would map to the same internal field name (e.g. "Margin %" and "Margin", both lowercased to `margin`) caused the second one to silently vanish from row data. The database-key disambiguator was renaming the second column's row-JSON key (`margin` → `margin_2`) but the SQL builder was still using the underlying column's field name (`margin`) for the SELECT alias, so PostgreSQL produced two columns both aliased `margin` — one shadowed the other in the row JSON and any `ORDER BY` over them was reported as ambiguous. The SQL builder now uses the disambiguated `dataKey` directly. - **Boolean formula columns are no longer mis-detected as decimal** — a formula like `status = 'pending' AND ordered_at < CURRENT_DATE - 3` is a boolean expression, but the formula-type heuristic only looked at arithmetic operators, saw the `-` in `CURRENT_DATE - 3`, and classified the whole column as a number (rendered with a DECIMAL widget). It now checks for comparison operators and `AND`/`OR`/`NOT` keywords before arithmetic, so the same formula is correctly inferred as `BOOLEAN`. ## 2026-05-12 ### Fixed - **Read-only members can now drag columns to reorder them** — viewer-role users couldn't drag column headers in a view, even though column order is purely a per-session UI state that isn't sent to the server (the only way order ever persists is by saving a preset, which already has its own permission check). The drag was being gated by the same workspace-write check that gates real schema changes. Drag now works for every role; the persistence steps (saving as the view default, or saving as a shared preset) keep their existing role checks. View-tab reordering is still admin/editor only — that one does write to the workspace. - **Read-only members no longer see write affordances they can't actually use** — until now, viewers (read-only members) saw the Add Row button, the cell-edit popover, the bulk-edit / bulk-delete bar, and the full column- and view-management menus. Clicking any of them would either silently do nothing or surface a generic permission error from the server. The UI is now role-aware: data-write affordances (Add Row, bulk edit/delete, cell edit) are hidden for read-only members; schema-management affordances (column properties / rename / delete / hide, view rename / duplicate / delete, add column, row styles) are hidden for everyone below admin. Read-only members still see the data, sort columns, drag them locally, save private presets, and export — i.e. everything that doesn't write to the workspace. ## 2026-05-11 ### Improved - **Formula columns are now validated against PostgreSQL before they're saved** — when you create a computed/formula column, the platform now (a) blocks a few more SQL constructs in the regex check (`CASE WHEN`, `ARRAY[…]`, square brackets, `VALUES`, unbalanced parentheses), and (b) round-trips the formula through your workspace's own database via `EXPLAIN` so any formula PostgreSQL won't accept is rejected at create time with the database's own error message attached. Previously, a malformed formula could pass the regex but produce invalid SQL downstream, breaking every read on the table until each formula column on that table was removed. - **Workspace API now invalidates its metadata cache on every column / view / relationship change** — workspace-api caches each workspace's entity model (up to 30 seconds for raw metadata, up to 60 minutes for the dynamic Hibernate SessionFactory) to avoid round-tripping to the metadata database on every request. Until now there was no signal from the metadata service to invalidate either cache on change, so a deleted/renamed column could remain in the projection for up to 30 seconds — long enough to leave a view's reads broken after a bad-formula incident. workspace-api now subscribes to the existing `workspace-events` RabbitMQ exchange and invalidates both caches immediately when a column/view/relationship event arrives. - **A failing formula no longer breaks the entire view's reads** — formula columns are evaluated by a single projection query alongside the rest of the row read. If that projection throws (e.g. a formula references a column that's been renamed), the formula columns now return `null` for that read and the rest of the row comes through normally, instead of the whole API call failing with `500`. - **Formula columns that call any SQL function outside a small allowlist no longer break reads on the view** — the formula rewriter qualifies bare identifiers as base-table columns (so `rating` becomes `"review"."rating"`). It used a hardcoded keyword allowlist to skip function names like `COALESCE` / `CONCAT` / `ROUND`, but every other function — `REPEAT`, `LPAD`, `RPAD`, `REGEXP_REPLACE`, and so on — got the same column treatment, producing `"review"."repeat"(...)` and a PostgreSQL `schema "review" does not exist` error. The rewriter now detects a function call by checking whether the identifier is immediately followed by `(`, so any SQL function works without needing to extend the allowlist. - **Smaller queries on views with formula columns** — internal cleanup. The formula path used to pre-create `LEFT JOIN`s for every M2O/O2O relationship on the entity the moment any formula column was present, even if the formula didn't reference any of them. JOINs are now created on demand, only for relationships the formula actually traverses. - **Clearer error response for syntax-level database errors** — when a database call returns a PostgreSQL 42xxx error (syntax error, undefined column, undefined function, etc.), the API now responds with `400 Bad Request` carrying the original `sqlState` and PostgreSQL message, instead of a generic `500` with "A database error occurred." ## 2026-05-02 ### Fixed - **Composite-primary-key tables stop responding after some hours of uptime** — the workspace API caches a runtime-built data model per workspace, and the model is rebuilt when the cache evicts an entry (typically after periods of inactivity). For tables with multi-column primary keys, the rebuild collided with leftover state from the previous build and started returning 500 for every subsequent request to that workspace. Fixed by giving each rebuild fresh, isolated state. Composite-PK tables now stay responsive across cache evictions, schema syncs, and long uptimes. ### Improved - **Workspace API backend errors are now forwarded to the error tracker** — previously, unhandled exceptions in the workspace API (the service backing every CRUD request) were logged locally but never sent to error tracking, so we couldn't see them without manually checking server logs. They now flow through the same error tracker as the rest of the platform. No change to behaviour or response payloads; this is a visibility fix that helps us spot regressions faster. ## 2026-04-28 ### Fixed - **Rows in composite-primary-key tables now open correctly** — clicking a row in a view backed by a table with a multi-column primary key (e.g. a `course_year` table keyed on `course_location_id` + `year`) returned 400 instead of opening the row. The single-row API rejected the URL because the camelCase column keys it received from the frontend didn't match the snake_case names it was looking up internally. Lookup now accepts both conventions, so rows in composite-PK tables open normally for view, edit, and inline-edit flows. ### Improved - **Softer confirmation when deleting metadata-only columns** — deleting a relationship display column or a formula/computed column no longer shows the "DELETE"-typing confirmation with the harsh red warning. These deletes only remove the column metadata; the underlying foreign-key column data stays in the database, and formula columns never had stored data to begin with. The dialog now reflects that with a single OK/Cancel, an info-coloured (rather than destructive-red) message, and the line "No data will be deleted." Regular scalar columns (which DO drop the underlying database column) keep the stricter red confirmation. ## 2026-04-26 ### Improved - **More frequent off-site backups of platform metadata** — orgs, users, schema definitions, billing state, and other platform metadata are now dumped to S3 every six hours (was: daily), retained for a week, with a documented restore procedure. Per the no-vendor-lock-in design, your own data still lives on **your** Postgres database — those databases are not part of these backups, and you remain responsible for backing them up. This change is about how quickly we can recover the platform side in the unlikely event of an issue with our infrastructure. - **30-day log retention across the platform** — internal log aggregation now keeps 30 days of history and survives container redeploys. Practical effect when you report an issue: we can investigate well beyond "what's in the container's memory right now," correlate across services, and trace what happened during recent deployments. ## 2026-04-24 ### Improved - **Faster production error detection** — uncaught exceptions on both the frontend and backend are now forwarded to an error-tracking service in production, so regressions surface without waiting for users to report them. No change to behaviour; dev builds are unaffected. - **Gzip compression on API responses** — Traefik now compresses application responses on the edge (SSE streams are excluded so real-time events still flow immediately), reducing payload sizes on large view reads and metadata endpoints. ### Added - **One-command rollback for deployments** — the deploy runner supports `run.sh rollback`, which brings the previously-active blue/green color back up from existing images and swaps traffic over without rebuilding. ## 2026-04-20 ### Added - **View search in the toolbar** — a new search box (right of the Help menu, under the hamburger menu on mobile) finds columns and cell values in the current view. Results open in a split dialog with two sections: **Metadata** for matching tables/columns (click to jump to the header and open its properties panel) and **Row Data** for matching cell values anywhere in the view (click to open the matched column's properties panel; if the row is already on screen, it's scrolled to and the cell pulses). Row cards offer **Copy value** and **Filter by value** quick actions — the filter is added as a temporary chip that only becomes permanent if you save the current preset. Keyboard: ↑/↓ to move between results, Enter to activate, Esc to close, and `Cmd/Ctrl+K` anywhere to focus the search input. Row-data search is server-side (case-insensitive substring across all columns), capped at 50 matches with a "refine your search" hint when more exist. ### Improved - **Stronger regression coverage on MCP and relationship-picker code paths** — internal test additions guard against bugs that previously slipped through: the MCP HTTP endpoint is now exercised end-to-end with real API keys (closing the gap where every test authenticated via JWT and missed MCP-key-specific failures), and the relationship picker's widget-type inference plus the OWNED inline editor's date input rendering are now covered by Playwright tests. ### Removed - **Dead MCP JAX-RS auth filter** — an unused `McpApiKeyAuthFilter` (which never fired because MCP endpoints are Vert.x routes, not JAX-RS) has been deleted. Auth still runs as before via `McpAccessGuard.requireAuth()` invoked from each tool — no behavior change. ## 2026-04-19 ### Added - **Computed/Formula widget surfaces in the Add Column dropdown** — the Formula widget type was previously hidden because it was misclassified as a relationship type. It now appears under a new "Computed" category alongside other widgets. - **Stable per-view column data key** — every ViewColumn now carries a persisted `dataKey` that's the single source of truth for the row-JSON key, SQL alias, filter rules, presets, and frontend state. Two columns over the same path (e.g. a "Roles" chips column and a "Role Count" column) now get distinct keys (`rolesName`, `rolesName_2`) instead of overwriting each other in row data. ### Improved - **Owned-mode inline editor pre-fills with the related row's data** — opening a relationship cell in OWNED mode now fetches the linked entity's current values (e.g. the author's name/email/bio when editing a book's author inline) before the form renders, so saving sends a complete payload instead of empty NOT NULL fields. - **Owned-mode save updates every field in the nested form** — the backend used to update only one column from the OWNED payload (and could pick the wrong one, e.g. setting the PK). It now matches each form field to the terminal entity's columns and updates them all in order, skipping the PK. - **Numeric widget consolidation** — the redundant "Number" widget (functionally a duplicate of "Decimal" — both stored as NUMERIC) is removed. Existing scalar columns map cleanly: integer types → Integer, decimal/float types → Decimal. ### Fixed - **Wrong PK in cell-edit URLs** — opening a popover for a row with a non-trivial primary key sometimes called the API with `/0` instead of the actual PK. The backend's `primaryKeyColumns` response now derives PK aliases from the schema directly instead of looking them up in a column map that an M:N relationship column with `displayField="id"` could overwrite. - **"Hidden FK column not found for relationship: …" 500 on relationship saves** — when the relationship's internal name didn't match the FK column's camelCase alias (e.g. constraint-named relationships like `users_company_id_foreign`), saving the relationship cell failed with a 500. The backend now matches the rel column to its hidden FK by the underlying snake_case column name. - **Schema-sync OWNED mode crash on relationship-property resolution** — the property resolver iterated a lazy collection on canonical view columns without fetching it, causing `LazyInitializationException` during cell saves. Now uses the `isRelationshipColumn` flag, which doesn't trigger the lazy load. ## 2026-04-19 (earlier) ### Added - **Relationship picker — drill into nested entities** — you can now expand a nested relationship (e.g. `Enrolment ▸ personalPhysicalAddress`) and pick a column from the leaf entity (`Address.street`) as the display field. Nested levels lazy-load on expand, so the picker stays fast on dense schemas and handles cyclic relationships safely. - **Relationship picker — drill through association entities (rich junctions)** — when a 1:N target is junction-shaped (a table that bridges two other entities, with or without extra columns), the picker now lets you keep walking through it to reach the other side. Example: from an Enrolment view, drill `invoiceEnrolments ▸ invoice ▸ invoice_number` to display invoice numbers per enrolment. Aggregate functions (Count, Chips, Sum, Avg, Min, Max, Concat) apply at any depth where a collection hop appears in the path. - **Relationship picker — Concat aggregate** — added a "Concatenated (joined string)" option to the display-mode dropdown for collection-valued relationships. Same SQL underpinnings as Chips (`string_agg` / `GROUP_CONCAT`), but rendered as a single delimited string rather than chip widgets. - **Relationship picker — Edit Mode in Add Column dialog** — restores the dropdown that was previously available, letting you pick the cell's edit behavior (Not Editable / Reference / Owned / Association) at column-creation time instead of going to the properties panel afterwards. ### Improved - **Picker — full-path collection detection** — the aggregate-mode dropdown now appears whenever a 1:N or N:N hop sits anywhere in the relationship path (top-level OR nested), not just at the top level. - **Picker — Widget Type filtered by column type** — the Widget Type dropdown now only shows widgets compatible with the selected column's database type (e.g. a YEAR column won't offer Email or Image). The list also accepts an "Inherit" option so the backend infers the default — matching whatever the source view uses. - **Picker — junction detection covers rich junctions** — previously only pure junctions (PK = composite of FKs) were detected. Now also matches association entities (surrogate PK + composite UNIQUE on the FK pair, with extras), so the through-target M:1 surfaces under their 1:N back-pointer. - **Picker — alphabetical ordering** — top-level rels in Existing Relationships and the entire Other Entities list are sorted alphabetically. Within each entity expansion: PK pinned at top, scalar columns alphabetized, then nested relationships alphabetized. - **Picker — cycle guard catches all path lineage** — drilling like `Administration ▸ Enrolment ▸ Category ▸ Enrolment` is suppressed because the second Enrolment lands on the same row as the first. Previously only direct back-pointers to the view's source entity were filtered. - **Picker — N:1 chip** — added the missing N:1 chip alongside 1:1 / 1:N / N:N for visual consistency. - **Picker — clearer relationship labels** — auto-generated relationship names (e.g. `fkC04d5114a1c904b3` from hashed FK constraints) now derive a readable label from the underlying FK column name (`personal_physical_address_id` → "Personal Physical Address"). Collection rels (1:N / N:N) use the relationship's own name rather than the back-pointer FK column. ### Fixed - **Inline-edit popover prefills correctly for nested relationship columns** — when toggling Owned mode on a relationship column that targets a nested field (e.g. `Enrolment ▸ Address ▸ address1`), the form now uses the column's actual JSON key (`fk…Address1`) for prefill instead of the bare leaf name. Existing columns with the wrong saved value can be fixed by clearing `inlineEditFields` in the column properties. - **API rejects FK-to-FK target columns** — the backend now refuses to create a new relationship pointing at a column that is itself a foreign key. Previously the picker filtered these out client-side, but MCP/API callers could bypass the guard. - **Schema sync correctly classifies composite-PK FK columns as MANY_TO_ONE** — previously, every column in a composite primary key was marked individually unique, so a foreign key whose column happened to be part of a composite PK (e.g. `book_authors_link.book_id` in `PRIMARY KEY (book_id, author_id)`) got classified as ONE_TO_ONE. That suppressed the reverse 1:N back-pointer on the parent (e.g. `books → bookAuthorsLinks`), making rich-junction tables unreachable from the parent view's relationship picker. Re-syncing the workspace now creates the back-pointers correctly and the multi-hop drill-through works. ### Improved - **Relationship picker — clearer existing-relationship tree** — multiple foreign keys to the same table (e.g. `personalPhysicalAddress` and `companyPhysicalAddress`, both → `Address`) are now distinguishable: each row shows the relationship name with the target entity as a chip. Foreign-key id columns are no longer offered as display values — drill into the relationship and pick a real field instead. - **Relationship picker — Display Field dropdown removed for existing relationships** — clicking a leaf column in the tree is the display field, so the redundant follow-up dropdown is gone. The dropdown still appears for new "Other Entities" relationships, where it controls the optional display column alongside the new FK. - **Relationship picker — cleaner "Other Entities" expansions** — non-junction entities now show only their PK/unique columns (the valid FK targets). Nested-relationship drill-down is reserved for actual junction entities, where it backs the M2M through-path flow. ## 2026-04-17 ### Improved - **Relationship columns are editable by default** — newly-added relationship columns now default to editable (dropdown picker that changes the foreign key) instead of read-only. The three edit modes — Reference (change FK), Owned (edit the related row inline), and Association (pick or create) — are exposed on the add-column flow. Read-only is still available as an explicit opt-in. - **Widget type is inferred for relationship columns** — when you add a relationship column without specifying a widget, SchemaStack now picks a sensible default from the underlying database column's type (same inference used by schema sync). You can still override in the Properties Panel. - **AI/MCP `add_relationship_column` tool** — now accepts `readonly` and `relationshipEditMode` parameters, with documentation describing each mode. `widgetType` is optional — omitted, it's inferred. ### Fixed - **MCP-originated relationship column delete** — removing a relationship column through the MCP interface no longer throws a threading error mid-transaction; the delete completes cleanly. - **Widget Type selector is editable for relationship columns** — the properties panel no longer blocks changing a relationship column's widget (previously disabled with "Foreign key columns cannot change type," which incorrectly applied to display widget changes). - **"Relationship data not loaded" message replaced with live loading state** — the properties panel now shows a spinner while relationship metadata is loading, and only displays an error banner if the load actually fails. ### Changed - **Add Column dialog split into two tabs** — the dialog now has a **Normal column** tab and a **Relationship column** tab, cleanly separating scalar columns from relationship-backed columns. The Relationship widget option was removed from the Normal tab's widget dropdown. Existing relationship columns with `widgetType: RELATIONSHIP` keep working as before. ## 2026-04-16 ### Added - **Hide/unhide views** — views can now be hidden from the tab bar and restored from the properties panel - **Hide/unhide columns** — columns can now be hidden from the spreadsheet and restored from the properties panel - **MCP filter preset tools** — AI assistants can now create, update, and delete filter presets via the MCP interface ### Improved - **E2E test runner** — `--grep` flag now correctly filters to specific tests; fully isolated PostgreSQL container per run - **E2E test helpers** — API helper methods now use correct backend endpoints for column listing and updates - **View visibility SSE events** — hiding/unhiding views broadcasts real-time updates to all connected users - **Auth resource cleanup** — streamlined login endpoint code ### Fixed - **View hidden state persistence** — hidden views are now correctly saved and restored across sessions ## 2026-04-14 ### Added - **Login button on website** — the brand website navbar now has a "Log In" button alongside the "Get Started Free" CTA - **Image column support in E2E seeds** — books view now has sample cover images for testing ### Improved - **Dashboard layout** — organization description no longer overlaps the Settings button - **E2E test coverage** — expanded from 212 to 1160+ tests across 24 spec files, covering all spread and admin app features - **E2E test stability** — added cleanup hooks to mutation specs, fixed flaky filter/sort/M2M tests, improved login timeouts - **E2E infrastructure** — isolated messaging and metadata database for tests (no longer shared with dev) ### Fixed - **SERIAL column type handling** — `SERIAL`, `SMALLSERIAL`, and `BIGSERIAL` PostgreSQL types are now correctly parsed as integers in the data layer ## 2026-04-12 ### Improved - **Formula column filters** — formula columns that produce numeric results (e.g., `price * quantity`) now show the correct filter operators (greater than, less than, etc.) instead of text-only operators - **Notification snackbars** — redesigned with a neutral gray card, white inner content area tinted by type (green for success, red for error, amber for warning), with dark mode support - **Schema drift dialog** — clean centered layout when schema is in sync, replacing the old message box - **CSV import column matching** — improved column mapping dialog with better auto-detection - **Login dialog accessibility** — fixed Angular content projection warning for button icons ### Fixed - **Dark mode consistency** — forced dark mode now matches browser-detected dark mode exactly across all components, chips, row colors, and Material styles - **Filter evaluator type detection** — numeric formula results are now correctly compared as numbers in client-side filter evaluation ## 2026-04-11 ### Added - **Conditional Row Styles** — color and format rows based on filter conditions. Create rules like "status = active → green background" with support for background colors, bold, italic, and strikethrough. Rules are priority-ordered (first match wins) and saved as part of filter presets, so shared presets include their visual formatting - **Live preview in row styles dialog** — conditional formatting rules now apply to the data table in real-time while editing, so you can see the effect before saving ### Improved - **Dark mode for conditional row colors** — the 8 preset row background colors now properly adapt to dark mode in both "Match Browser" and forced dark theme - **Readonly cell tinting** — readonly cells on conditional-colored rows now darken the row color instead of always showing a blue tint ### Fixed - **Column width stability on infinite scroll** — column widths no longer jump when loading additional rows via infinite scroll - **Drift detection false positives for unique indexes** — columns marked as unique no longer trigger spurious "index added" drift warnings - **Drift detection false positives for timestamp defaults** — auto-generated `now()` defaults on timestamp columns no longer reported as drift ## 2026-04-10 ### Added - **CSV Import** — import CSV files into any view with a Sequel Ace-inspired field mapping dialog. Supports auto-mapping by column name, record browsing to verify mappings, and saved mapping presets for repeated imports - **Bulk insert endpoint** — new `POST /api/data/bulk/{viewUuid}/insert` for inserting up to 1000 rows per request, with per-row error reporting - **Import mapping presets** — save and load CSV-to-view column mapping configurations. Supports PRIVATE and SHARED visibility, same pattern as filter presets ### Improved - **Tab bar scroll** — active tab now scrolls into view on page load when there are many tabs - **Tab chevron sizing** — dropdown chevron properly contributes to tab width instead of overlapping - **Properties panel elevation** — panel now has a subtle box-shadow for better visual separation - **Properties panel drag handle** — thicker green bar on hover, only opens panel on drag (not click), drag-to-close supported - **Relationship picker** — circular references (current entity) hidden in both existing and available entity trees; empty junction table children no longer shown - **Widget type for relationships** — FK columns now show "Relationship" in the widget type dropdown instead of empty - **Insert column dialog** — title split into "Insert Column" heading with position as subtitle - **Preset dialog spacing** — reduced vertical gap between search input and filter/sort chips ### Fixed - **Hardcoded PK column assumptions eliminated** — all relationship query builders now use actual PK column names from metadata instead of assuming "id". Missing metadata throws clear errors instead of silently producing wrong SQL - **Target entity metadata cached on relationships** — `targetTableName` and `targetPkColumn` stored during sync/creation, eliminating runtime DB lookups in query builder - **Display mode switching** — changing between Count/Values/Aggregate now sends a single API call instead of two, fixing stale `displayField` being sent - **Guest token access recording** — fixed session lifecycle error in `GuestTokenAuthFilter` by chaining `recordAccess` into the reactive pipeline - **Dashboard broadcast stability** — set read-only flush mode to prevent `StaleObjectStateException` during concurrent entity deletions ## 2026-04-09 ### Added - **OneToMany relationship columns** — display child record counts or aggregated values (chips) from related tables. Supports rollup functions: Count, Sum, Average, Min, Max - **Relationship type labels** — discovery tree shows cardinality badges (1:1, 1:N, N:N) for each relationship - **Relationship search filter** — filter entities by name in the relationship picker for large schemas - **Clear FK value** — new "Clear selection" option in the relationship cell editor to set a foreign key to NULL - **Display mode switching** — toggle between Count and Values (chips) for M2M and OneToMany columns after creation - **Rollup aggregate functions** — Sum, Average, Min, Max aggregations for OneToMany columns ### Improved - **M2M column properties** — simplified panel hides irrelevant schema settings and referential actions - **M2M real-time sync** — association toggles now broadcast to other users via SSE - **UUID support in M2M editor** — multi-select editor handles both numeric and UUID primary keys - **Junction entity detection** — relationship picker auto-detects junction tables and hides PK columns - **Column position management** — all column creation paths (regular, formula, relationship, M2M) now use the position manager for correct insertion ordering ### Fixed - **Schema sync cascade failure** — sync now uses isolated sessions per view, preventing one view's failure from cascading to all others (25P02 fix) - **Schema drift false positives** — synthetic M2M columns excluded from drift detection - **M2M column deletion** — proper Hibernate cascade cleanup prevents OptimisticLockException - **Stale column backfill** — sync no longer tries to create ViewColumns for columns deleted in the same transaction - **Primary key detection** — relationship display columns no longer collide with PK columns in query metadata, fixing incorrect rowId resolution for all cell editors - **Relationship options on MySQL** — FK value lookup now uses vendor-aware SQL, fixing the "selected" indicator in relationship dropdowns - **M2M toggle returns full row** — association changes now return the complete updated row, ensuring computed columns refresh correctly ## 2026-04-08 ### Added - **Many-to-many relationships** — link records across entities with a new "Multiple links" option in the relationship picker. Creates a join table automatically — no manual schema setup needed - **M2M chips display** — many-to-many columns show linked records as compact colored chips in the spreadsheet grid - **M2M association endpoints** — new API endpoints for managing many-to-many associations (add/remove links, fetch options with search and pagination) - **AI usage counter** — daily AI message usage shown in the chat title bar (e.g. 2/3), resets daily per member - **Chat history** — conversation persists in the browser per workspace, survives page reloads. Use `/clear` to reset - **Chat help button** — `?` icon in chat input shows available commands (`/clear`, `/help`) ### Improved - **Column position SSE** — moving a column now broadcasts all affected positions to other users, fixing visual ordering glitches in real-time - **Schema Advisor rate limits** — daily AI message limits per member by plan: Free (3/day), Pro (20/day), Enterprise (unlimited). When limits are reached, users are guided to connect their own AI client via MCP for unlimited access - **Workspace status banners** — consistent purple Design mode color across admin and spread apps, with dark mode support. Status changes from admin now update the spread app banner in real-time - **Mobile banner layout** — status and connection banners now have proper padding and icon sizing on narrow screens - **Floating panels** — shared drag handle and close button styles between Activity and Chat panels, uppercase titles matching table headers ### Fixed - **Workspace status SSE** — changing workspace status in admin now immediately updates the banner in the spread app (was filtered as own-event) - **Column update persistence** — relationship edit mode and other display properties now correctly saved via schema detection path ## 2026-04-07 ### Added - **Schema Advisor** — AI-powered schema assistant built into the spreadsheet interface. Inspects your schema, suggests improvements, creates tables/columns/indexes/constraints, and manages relationship columns through natural conversation. Only schema metadata is sent to the AI — your actual data stays private. [Learn more](https://docs.schemastack.io/app/schema-advisor.md) - **Column search API** — fuzzy search for columns by name or display name across all views in a workspace, with trigram similarity matching for typo tolerance - **"Start with sample data" workspace option** — new template-based workspace creation that provisions a managed database pre-loaded with the Acme Store demo dataset (categories, products, customers, orders). Available from the create workspace wizard - **Database indexes documentation** — new public docs page covering index creation, listing, deletion, schema import, and best practices - **Relationship lookup columns via MCP** — add columns from related entities (including multi-hop relationships) directly through AI assistants ### Improved - **Workspace detail header** — "Open Workspace" replaced with a clean "Open in SchemaStack" link; Settings button uses gradient styling aligned with card padding - **Roadmap updates** — moved database indexes, computed columns, migration impact prediction, MCP server, and Zapier integration from roadmap to "Available Now" - **Floating panels** — activity and chat panels share a consistent draggable window design with title bars - **Properties panel** — dragging the resize border now opens the panel if it was collapsed - **Email verification** — first input auto-focuses for immediate paste support ### Fixed - **MCP column creation** — fixed `BlockingOperationNotAllowedException` when adding relationship columns via MCP (JWT identity resolution blocked on IO thread) - **Column update SSE** — UI-only column changes (readonly, edit mode, position, display name) now broadcast real-time to other users - **Column update persistence** — relationship edit mode, sortable, apiVisible, and other display properties now correctly saved via the schema detection update path - **Relationship column positions** — new relationship/lookup columns now get a valid position instead of null - **Workspace slug reuse** — deleted workspaces and organisations free up their slug for reuse ## 2026-04-06 ### Improved - **Zapier integration** — new row and updated row triggers now automatically detect common timestamp column names so triggers work regardless of your column naming convention. Supported names: - **New Row:** `created_at`, `createdAt`, `created`, `inserted_at`, `insertedAt`, `inserted`, `insert_at`, `insertAt`, `date_created`, `dateCreated`, `creation_date`, `creationDate`, `created_date`, `createdDate`, `added_at`, `addedAt`, `added` - **Updated Row:** `updated_at`, `updatedAt`, `updated`, `modified_at`, `modifiedAt`, `modified`, `update_at`, `updateAt`, `date_modified`, `dateModified`, `date_updated`, `dateUpdated`, `modified_date`, `modifiedDate`, `updated_date`, `updatedDate`, `last_modified`, `lastModified`, `last_updated`, `lastUpdated`, `changed_at`, `changedAt` - **Zapier "Find Row" search** — search field is now a dropdown populated from your table's columns instead of freeform text - **Zapier deduplication** — updated row trigger no longer fires on newly inserted rows, and correctly re-triggers on each update - **Bulk export downloads** — export files are now stored in S3, fixing download failures when the processor and API run on separate containers - **Export download reliability** — download links no longer disappear on transient errors; only expired exports are removed from the toolbar - **Timestamp behavior** — columns with "On update" auto-set now get an initial timestamp on insert (previously NULL via both the Data Platform and the Workspace API) ### Fixed - **Migration tracking** — fixed stale migration locks between services causing "migration already in progress" errors - **Timestamp behavior migrations** — dry-run no longer corrupts auto-generation state, preventing subsequent migrations from being silently skipped - **Add row form** — columns with auto-set timestamp behavior are now hidden from the form (they're auto-generated) - **Workspace API filters** — invalid filter values (e.g., text in a UUID field) now return empty results instead of a 500 error - **Workspace API stability** — fixed crash when tables have foreign key columns that share a primary key (e.g., shared-PK inheritance patterns), and tables without a primary key (e.g., Liquibase changelog tables) no longer break the API for the entire workspace - **Status page reliability** — health checks no longer report false "down" status when services are deployed individually on different blue/green colors ## 2026-04-05 ### Added - **Column rename** — rename database columns directly from the column properties panel in Schema Settings ## 2026-04-04 ### Added - **Outbound webhooks** — configure webhook endpoints on any view and send selected rows to external services with a single click. Includes HMAC-SHA256 payload signing, automatic retries with exponential backoff, and a delivery log for tracking each request - **"Send to webhook" bulk action** — select rows in the Data Platform, choose "Send to webhook" from the bulk action menu, and pick which configured endpoint to send to - **Zapier integration** — new native Zapier app lets you automate workflows: trigger on new/updated rows, create or update rows from other apps, and search for rows by field value - **Webhook delivery log** — view the status, response code, duration, and retry count for each webhook delivery attempt directly in the view properties panel - **Row-level security (RLS)** — per-view policies that filter data based on JWT claims, so external users only see and modify their own rows (e.g., `customer_id = {jwt.sub}`) - **External identity provider support** — workspace owners can now configure an external OIDC provider (Auth0, Clerk, Firebase) so end-users can authenticate with their own accounts and call the workspace API directly without a SchemaStack account - **Passwordless database authentication** — workspaces can now connect to databases using IAM, peer, or certificate-based auth without requiring a password - **Processor test suite** — comprehensive integration tests for column creation types, column drop/default, foreign key operations, index operations, table creation, and error handling ### Improved - **Schema sync** — improved composite foreign key extraction, drift detection, and schema hashing for more reliable sync - **Schema repair** — diagnose now detects partially missing view columns (not just fully empty views) and repair backfills them - **Repair display** — repair report now shows the exact missing column names instead of just a count - **Column properties panel** — improved relationship picker and column schema options UI - **Migration warning** — clearer migration impact warnings in the column properties panel ### Fixed - **Missing view columns after failed task completion** — when an internal message was lost during column creation, the view column was never created. Schema sync and repair now detect and fix these orphaned column metadata rows - **Member limit** — settings page now shows the correct member limit from your subscription tier instead of a hardcoded value - **Query performance** — fixed Hibernate in-memory pagination (HHH90003004) on organisation queries; memberships are no longer loaded entirely into memory ### Removed - **SQL Server vendor option** — removed from workspace creation (PostgreSQL and MySQL only) - **Push notifications** — removed non-functional push notification settings (email preferences remain) ## 2026-04-03 ### Added - **OAuth2 Authorization Code + PKCE** — workspace API now supports OAuth2 token-based access for SPAs and mobile apps, alongside existing API keys - **OAuth2 client management** — new "OAuth2" tab in workspace settings to register and manage OAuth2 clients with redirect URIs and scopes - **OAuth2 consent page** — when a third-party app requests access, users see a consent screen showing the app name, workspace, and requested permissions before approving - **API key expiration** — API keys can now be created with an optional expiry date ### Improved - **CORS handling** — workspace API now allows all origins for token-authenticated requests, following industry standard (Stripe, Supabase); CORS configuration removed from settings since Bearer tokens provide the security, not origin restrictions - **Multiple foreign keys to same table** — workspace API now correctly handles tables with multiple FKs pointing to the same target table ## 2026-04-02 ### Added - **Composite foreign key support** — full stack support for multi-column foreign keys: sync extracts them, DDL generates `FOREIGN KEY (a,b) REFERENCES target(x,y)`, ByteBuddy generates `@JoinColumns` annotations - **Composite PK relationship picker** — when a target table has a composite primary key, the picker shows checkboxes with a "Select all PK columns" shortcut for multi-column FK creation - **Composite PK bulk operations** — bulk delete, update, and export now support tables with composite primary keys using `(col1,col2) IN ((?,?),(?,?))` syntax ### Improved - **Input validation** — all REST endpoints now validate request bodies; invalid input returns clear 400 errors instead of server errors - **Security headers** — added X-Content-Type-Options, X-Frame-Options, X-XSS-Protection, and Referrer-Policy headers on all responses - **Password reset security** — response is now identical whether the email exists or not, preventing account enumeration - **CORS security** — workspace API CORS headers are now set via a dedicated servlet filter - **Error messages** — internal error details no longer leak in API responses; 500 errors return generic messages - **JDBC timeouts** — all database connections and queries now have timeouts to prevent indefinite hangs - **File upload protection** — oversized uploads are rejected before reading into memory ### Fixed - **Bulk operations with composite PKs** — scalar IDs on composite PK tables correctly use the first PK column instead of failing with a cast error - **Column creation positioning** — inserting a column at a specific position now correctly shifts existing columns using the position manager ### Removed - **Deprecated relationship endpoint** — removed `POST /api/views/{uuid}/relationship-columns` and MCP `add_related_column` tool; use `POST /api/columns` with `relationshipPath` instead - **Unused dependencies** — removed Apache Camel (unused), dead code, deprecated consumer class, unused repository methods ## 2026-04-01 ### Added - **Database view support** — database views (CREATE VIEW) are now imported during schema sync as read-only views; all columns are marked non-editable - **Generated column support** — columns defined as `GENERATED ALWAYS AS (...)` are automatically detected and marked as read-only, preventing insert/update errors - **Column comments as display names** — if your database columns have comments (`COMMENT ON COLUMN ...`), they're used as the default display name instead of converting from snake_case - **PostgreSQL ENUM support** — enum types are detected with their allowed values extracted from the database - **PostgreSQL array type support** — array columns (`TEXT[]`, `INTEGER[]`) are imported and displayed as text - **Database compatibility guide** — new documentation page listing all supported and unsupported database features for PostgreSQL and MySQL ### Improved - **Schema sync reliability** — new per-view transaction architecture ensures one table failing doesn't break the entire sync; each table's success or failure is independent - **FK cascade rule syncing** — foreign key cascade actions (ON DELETE CASCADE, SET NULL, etc.) are now properly synced and kept up to date when changed in the database - **Widget type detection** — expanded to cover 30+ database types including JSONB, UUID, INTERVAL, MONEY, TIMESTAMPTZ, DATETIME, MEDIUMTEXT, and more - **Column positioning** — creating a column at a specific position now correctly shifts existing columns using the position manager - **Auto-generation type syncing** — changes to auto-generation (e.g., switching from SERIAL to IDENTITY) are now detected and synced - **Native image build speed** — removed unused Apache Camel dependency and excluded large SDK JARs from resource scanning - **Native image compatibility** — comprehensive reflection config for all DTOs ensures correct serialization in native builds ### Fixed - **ViewColumn generation on sync** — views imported by the old sync that were missing ViewColumns now get them automatically on re-sync - **Relationship columns on fresh import** — FK columns now correctly create both a hidden FK ViewColumn and a visible relationship ViewColumn with the RELATIONSHIP widget - **Column ordering** — ViewColumn positions now follow database column order (JDBC ordinal position) instead of arbitrary ID order - **Password reset security** — response is now identical whether or not the email exists, preventing user enumeration ### Removed - **Old sync engine** — removed the monolithic SchemaImportService (~2,600 lines) and SchemaSyncService wrapper, replaced by the new per-view sync architecture - **Deprecated consumer** — removed unused TaskCompletionConsumer (functionality merged into TaskCompletionBusinessLogicConsumer) - **Dead code cleanup** — removed unused repository methods, debug print statements, and unused messaging DTOs ## 2026-03-31 ### Added - **Per-view sync architecture** — schema sync now uses independent transactions per table; if one table fails, others still sync successfully - **Relationship widget type** — unified column creation with inline relationship configuration in the properties panel - **Formula/computed columns** — virtual columns evaluated at query time using SQL expressions, created via `POST /api/columns` with a `formula` field - **Unified column creation API** — relationship columns and regular columns now use the same `POST /api/columns` endpoint ### Improved - **Relationship picker** — embedded inline in the column properties panel with source column auto-hidden when active - **Column creation dialog** — enhanced with widget type selector, reference picker, and formula editor components - **Migration warning component** — new UI component showing impact warnings before schema changes ### Fixed - **FK cascade action syncing** — foreign key ON DELETE/ON UPDATE rules are now properly extracted from the database and synced to column metadata - **ViewColumn generation** — fresh imports now correctly generate ViewColumns for all columns - **Relationship ViewColumns** — fresh import creates both hidden FK and visible relationship ViewColumns with correct widget type ## 2026-03-28 ### Fixed - **Schema sync column changes** — syncing a workspace after columns are added or removed in the database now works correctly; previously this could cause internal errors due to Hibernate cascade conflicts - **Schema drift false positives** — composite unique constraints (e.g. multi-column unique indexes) no longer incorrectly report individual columns as having unique constraint changes - **Feedback screenshot upload** — fixed threading error that prevented screenshot uploads; upload now runs on a worker thread pool - **Feedback dialog reset on error** — when a screenshot upload fails, the drag area now resets instead of showing a broken preview ### Improved - **Feedback screenshot storage** — screenshot metadata is now stored as a single JSON field (matching the image widget format) instead of two separate columns - **Feedback screenshot preview** — the dialog now shows a server-confirmed preview image instead of a local base64 preview, ensuring the upload actually succeeded - **Avatar upload buttons** — "Upload Avatar" and "Use This Image" buttons now use flat green style consistent with the rest of the app ## 2026-03-27 ### Fixed - **Warning notifications** — warning snackbar no longer appears transparent over the toolbar - **Schema drift detection** — composite unique indexes no longer cause false-positive drift; FK cascade actions are now synced correctly - **Feedback screenshots** — large screenshots are now compressed client-side before upload, preventing timeouts ### Added - **Foreign key cascade rules** — configure ON DELETE and ON UPDATE actions (Cascade, Set Null, Restrict, Set Default) per foreign key column in the column properties panel under "Referential Actions" - **Default preset per view** — set a personal default preset that auto-loads when opening a view (pin icon in preset dialog), each user has their own independent default - **Schema import preserves cascade rules** — importing an existing database now detects and stores both ON DELETE and ON UPDATE rules from FK constraints ## 2026-03-26 ### Added - **Schema repair tool** — new Repair tab in workspace settings diagnoses and fixes metadata inconsistencies (orphaned entities, missing view columns) - **Add Column from empty view** — views with no columns now show an "Add Column" button, and the view tab menu includes an "Add Column" option - **API Docs & Sandbox in Help menu** — the spread app Help menu now links to API documentation and Swagger sandbox - **Feedback screenshot drag & drop** — the feedback form now supports dragging and dropping images in addition to clicking to upload - **Composite primary keys** — the workspace API now supports entities with composite primary keys (comma-separated in URL path) ### Improved - **Timestamp behavior changes** — changing a column's auto-set timestamp behavior now shows the unified orange migration warning box instead of a separate inline banner - **Real-time connection stability** — switching between view tabs no longer shows a false "Connection was interrupted" warning - **Migration conflict handling** — when a column update conflicts with an in-progress migration, a "Force Cancel Migration" action lets you cancel and retry - **Workspace API validation** — auto-generated columns (timestamps, UUIDs) and columns with default values are no longer required in POST requests - **Workspace API type support** — added support for 25+ SQL types (SMALLINT, SERIAL, JSONB, TIMESTAMPTZ, BYTEA, etc.) across entity generation, validation, filtering, and OpenAPI docs - **OpenAPI documentation** — Swagger docs now show nullable indicators, readOnly markers, default values, and correct types for UUID/composite primary keys ### Fixed - **UUID primary key CRUD** — creating, reading, updating, and deleting records in tables with UUID primary keys now works correctly - **Auto-generated timestamp columns** — columns with `DEFAULT CURRENT_TIMESTAMP` are no longer included in INSERT statements (lets the database default apply) - **Null value updates** — setting a nullable field to null via PUT request now works instead of being silently ignored - **Bulk operations with UUID PKs** — bulk update and delete now correctly handle UUID and composite primary keys - **Filtering on UUID/date columns** — filtering by UUID, DATE, and TIMESTAMP columns no longer causes type mismatch errors - **Dark mode badge colors** — validation rules, filter badges, and other mint/blue badges now display with subdued colors in dark mode instead of bright green - **Dark mode consistency** — fixed sidebar going dark while main content stayed light when OS dark mode is active - **Add Row with UUID primary key** — creating records in views with UUID primary keys no longer fails with "Field 'uuid' is required" - **Schema drift on UUID PK views** — creating a view with a UUID primary key no longer triggers false "Column type changed" drift detection - **Schema sync data loss** — syncing a workspace no longer deletes view columns from other workspaces (critical fix) - **Double data load on column add** — adding a column no longer triggers two redundant data queries causing a table flicker - **Workspace Overview link** — the avatar menu in the spread app now correctly links to the current workspace instead of the organization slug - **Database settings form validation** — credential fields now properly update validation state when pre-filled from saved settings - **Test cleanup** — added missing `workspace_activity_log` table to test cleanup, fixing cascade failures in metadata tests ### Security - **Authorization audit** — added view-level permission checks to relationship endpoints, bulk action endpoints, and removed unused unscoped query methods ## 2026-03-25 ### Improved - **View deletion UX** — deleting a view now instantly removes the tab and navigates away, with a confirmation dialog instead of a browser prompt - **View creation UX** — creating a view shows a pulsing tab with spinner while the backend processes, and a loading indicator in the main content area - **MCP key badges** — changing the workspace MCP access level now immediately updates the effective access badges on all keys - **SSL certificate validation** — the "Test Connection" button now includes uploaded SSL certificates in the test, and the CA certificate field is required when using VERIFY_CA or VERIFY_FULL mode - **SSE reconnect awareness** — a subtle banner appears after a real-time connection interruption, offering a one-click refresh to sync any missed changes ### Fixed - **UUID primary key on view creation** — creating a view with a UUID primary key type now correctly generates the primary key column without needing an extra flag - **Read-only table after view creation** — views created with a primary key are no longer incorrectly marked as read-only - **Constraint SSE events** — constraint changes (add, update, toggle, delete) now broadcast on the view stream and include column name and constraint type for richer activity messages - **Richer real-time events** — column created events now include widget type and database name; view member/guest events include the view name; preset events include the preset name; API key revoked events include the key name - **Dark mode** — the "Your Canvas Awaits" empty state and delete confirmation dialog warning text now display correctly in dark mode - **Real-time event data** — SSE events now use flat data access consistently, fixing "undefined" values in activity messages and key creation notifications ## 2026-03-24 ### Added - **Database indexes** — create, list, and delete single or multi-column indexes (including unique indexes) on views via the new Indexes API - **FK cascade rules** — foreign key columns now support ON DELETE actions (CASCADE, SET NULL, RESTRICT, SET DEFAULT) that are stored in metadata and applied during schema generation - **Index import** — importing an existing database schema now detects and preserves database indexes (composite and non-unique) as metadata ### Improved - **Unique index DDL generation** — unique indexes are now generated as `CREATE UNIQUE INDEX` instead of regular indexes in the schema processor - **Schema import accuracy** — composite unique indexes are no longer incorrectly flagged as single-column unique constraints during import ## 2026-03-23 ### Added - **Views usage card on dashboard** — the organisation dashboard now shows a Views stat card with usage bar, matching the existing Workspaces, Members, and Storage cards ### Improved - **Dashboard handles unlimited plan limits** — stat cards now hide the usage bar when a plan limit is unlimited (null) instead of showing "0 of 0" ### Fixed - **"undefined B" storage display** — the Storage stat card on the Plan & Billing page no longer shows "undefined B" when storage is zero or not reported - **Properties panel dark mode on "match browser" theme** — the column properties panel in the spread app now correctly uses dark background when the browser is set to dark mode (previously only worked with explicit dark theme toggle) - **Plan & Billing page crash for new orgs** — fixed "can't access property 'length' of undefined" error when the workspace list is not returned by the API ## 2026-03-22 ### Added - **Demo video scripts** — automated Playwright-based screen recordings for admin and spread app demos, with narration text files and reset scripts for reproducible recordings - **YouTube demo video embedded on website** — the landing page hero section now shows the product demo video instead of a placeholder, and "Watch Demo" buttons scroll to it ### Improved - **Create workspace now includes Connection Security settings** — SSL/TLS mode, connection timeout, and certificate upload options are now available when configuring a database during workspace creation, matching the existing workspace settings form - **Workspace links fixed in production** — "Open Workspace" and "Open in SchemaStack" links on the workspace overview page now correctly route to the spread app - **Pro plan pricing reduced** — Pro plan price lowered from $29/month to $19/month - **Dark mode input borders** — cell edit popover and dialog form fields now use subtle border styling instead of bright primary-color outlines in dark mode - **Loading bar no longer shifts login form** — the progress bar on the login page now overlays the page (matching the authenticated layout) instead of pushing content down - **Bulk action bar styling** — column select dropdown is now compact with proper font weight - **Cell edit autofocus** — opening the cell edit popover now automatically focuses the first input field - **Boolean bulk edit default** — boolean fields now default to "false" in bulk edit mode instead of null, preventing validation errors ### Fixed - **Validation error messages now show details** — API validation errors (e.g. "Description cannot contain < or > characters") now display the specific field error instead of just "Validation failed" - Registration no longer briefly flashes a red error box on success before navigating to the verification page - DECIMAL widget icon now renders correctly (was using non-existent Material icon name) - Schema drift dialog expansion panels no longer show dark borders in light mode - **Organisation description with newlines** — saving a description containing line breaks no longer triggers a false "cannot contain < or >" validation error. Newlines are now automatically converted to spaces - **Relationship columns positioned correctly on sync** — when a new workspace is synced, relationship display columns (e.g., "Category") now appear in the same position as the foreign key column in the database schema, instead of being appended at the end - **Database cascade cleanup** — fixed missing ON DELETE CASCADE constraints across the schema, preventing orphaned rows after workspace or view deletion. Reset scripts are now simpler and more reliable ## 2026-03-20 ### Improved - **Filter preset visibility simplified** — the three visibility options (Private, Team, Public) have been reduced to two: **Private** (only you) and **Shared** (anyone with access to the view). The "Public" option was redundant since view access already requires workspace membership. Existing Team and Public presets are automatically migrated to Shared ## 2026-03-18 ### Added - **Composite primary key support** — tables with multi-column primary keys now support full CRUD operations (view, edit, insert, delete). Composite key values are passed as JSON objects in both the REST API and SSE events - **Read-only mode for tables without primary keys** — tables that have no primary key can now be viewed and exported, but write operations (edit, insert, delete) return a clear error explaining that a primary key is required - **Export file retention** — bulk exports now include an expiration timestamp (default 24 hours). The download link and SSE event show when the file expires so you know how long it remains available - **Scroll-to-warning button** — when a pending schema migration warning is scrolled out of view in the properties panel, a floating orange pill button appears to quickly jump back to it ### Improved - **Admin dark mode background** — the admin app now uses a subtle dark blue gradient (matching Tailwind's gray-950/gray-900) across all pages in dark mode, replacing the flat background - **Shared login dark mode styles** — both spread and admin login pages now share the same dark mode card styling (emerald gradient tint, green border, green-tinted shadow, dark input backgrounds) via a shared SCSS mixin - **SSE reconnection banner no longer flashes on page load** — the "Reconnecting to real-time updates" banner is now suppressed during the initial SSE connection and only appears on actual reconnection attempts ### Fixed - Filtering on date and timestamp columns now works correctly — previously returned a type coercion error when using date filters like `shipped_at > 2025-11-01` - Bulk operations (delete, update, export) now work correctly on tables with UUID primary keys — previously failed with a type conversion error - Bulk delete/update that completely fails (e.g. due to a foreign key constraint) now correctly reports as failed instead of succeeded. The error response includes per-row details explaining why each row could not be processed - Spread login card width now matches admin at 448px - "Forgot password?" link in spread login now renders at 14px as intended ## 2026-03-17 ### Added - **Migration impact preview with SQL highlighting** — when a schema change requires a database migration, the properties panel now shows the exact SQL operations that will be executed, with syntax-highlighted queries, impact level classification, and a list of affected foreign key references with read/write impact badges - **Real-time migration progress** — during long-running schema migrations, the system now reports real-time progress via SSE (`view.column.schema.progress` events). For PostgreSQL 12+, progress is derived from `pg_stat_progress_alter_table` with actual rows-processed counts. For MySQL and older PostgreSQL, time-based progress estimation is used instead. Progress includes phase info (e.g. "Rewriting table", "Building index"), percentage complete, and elapsed time - **API protection during migrations** — the REST API and MCP tools now return `503 Service Unavailable` with a `Retry-After` header when a blocking schema migration is in progress, instead of hanging on database locks. The error response includes `blocksReads`, `blocksWrites`, and `retryAfterSeconds` so clients know exactly what's blocked and when to retry - **Data-driven duration estimates** — migration duration predictions are now refined over time using historical data from your workspace. After enough migrations, the system replaces static assumptions with actual measured ms/row rates specific to your database hardware - **Row count estimation for migrations** — migration impact predictions now use real approximate row counts from the workspace database (`pg_stat_user_tables` for PostgreSQL, `information_schema.TABLES` for MySQL), making duration estimates far more accurate for large tables - **Migration duration tracking** — the system now records actual migration durations and compares them to estimates. Over time, this historical data refines future predictions — your workspace's actual hardware performance replaces static assumptions - **MySQL storage engine detection** — impact analysis now detects whether a MySQL table uses InnoDB, MyISAM, or another engine, and adjusts the impact classification accordingly. Non-InnoDB tables are classified as fully blocking since they lack Online DDL ## 2026-03-16 ### Added - **Migration impact prediction** — when a schema change requires a database migration (column type change, adding NOT NULL, etc.), the system now predicts the impact: whether it will be instant, brief, or blocking, whether reads and/or writes are affected, and the estimated duration. This is vendor-aware — the same operation can have different impacts on PostgreSQL vs MySQL (InnoDB vs MyISAM). Impact data is included in the 202 API response and in the `view.column.schema.changing` SSE event ### Improved - SSE event type naming has been reorganised — the event prefix now directly determines which stream carries it: `view.*` events go to the view stream only, `workspace.*` events go to the workspace stream, and `organization.*`/`dashboard.*` events go to the org stream. Data events are now prefixed `view.data.*` and view lifecycle events are now `workspace.view.*` - Migration prediction now correctly detects **all** database type changes (e.g. VARCHAR → TEXT) — previously, types in the same family were incorrectly skipped. Only true aliases (e.g. VARCHAR ↔ CHARACTER VARYING) now bypass migration - **Faster initial load** — non-core UI components (properties panel, activity feed, filter panel, bulk action bar) are now lazy-loaded, reducing the initial bundle size - Migration info popover is now readable in both light and dark mode — text colors are hardcoded to a dark theme for consistent contrast - Migration rule descriptions are now more informative — each rule shows context-aware detail text (e.g. "Not applicable for VARCHAR type" instead of generic "No change") - Widget type changes now correctly trigger migration detection — changing a widget from STRING to NUMBER (or any change that implies a different DB type) is properly routed through schema change detection instead of being silently applied as a metadata-only update - Column migration prediction is now fully accurate for all PostgreSQL and MySQL type variants — the backend correctly distinguishes between true type aliases (no migration) and different types (migration required) - All 16 widget types (STRING, TEXT, EMAIL, URL, PHONE, NUMBER, INTEGER, DECIMAL, BOOLEAN, DATE, DATETIME, SELECT, MULTI\_SELECT, FILE, IMAGE, UUID) now have complete widget configurations — creating columns with any widget type works without errors - Schema processor now handles cross-vendor type mapping for all common types — MySQL-specific types (MEDIUMTEXT, TINYINT, ENUM, SET, YEAR) are correctly translated when targeting PostgreSQL, and PostgreSQL-specific types (INTERVAL, INET, BYTEA, JSONB, SERIAL) are translated when targeting MySQL ### Fixed - Fixed `currentDbType` returning widget type names (URL, EMAIL, PHONE) instead of actual database types (VARCHAR) in the column properties response - Fixed widget-type-only changes (without advanced options) bypassing schema change detection entirely — VARCHAR→INTEGER type changes were applied without generating the required database migration - Fixed NUMBER widget mapping to INTEGER instead of NUMERIC — now correctly maps to NUMERIC to match the widget's default precision/scale settings - Fixed FILE and IMAGE widget DB type mapping — now correctly maps to TEXT (matching their widget configuration) instead of VARCHAR ## 2026-03-15 ### Added - Image columns now support a **URL mode** (`imageMode: "url"` in widget options) — set this when your column contains plain image URLs instead of uploaded files. Thumbnails are generated on first view and cached weekly. Failed URLs are remembered to avoid repeated fetch attempts - Signed thumbnail URLs for secure access without requiring authentication headers on image tags - Organisation description is now included in the selected organisation response, so dashboard and settings pages can display it without an extra API call ### Improved - SSE events now include `originClientId` and `userId` across all event types — enables reliable echo prevention so your own changes don't trigger duplicate UI updates - Organisation settings SSE events now send only the changed fields instead of the full organisation object, and use the organisation slug as entity identifier ## 2026-03-14 ### Improved - Organisation member role changes and removals now broadcast real-time SSE events — other admin users see member updates instantly without refreshing - All SSE streams now enforce membership checks at connection time — workspace stream requires workspace membership (or org admin), organisation stream requires org membership, and task completions require authentication - SSE event type naming is now consistent — all column events use the `metadata.view.column.*` prefix for a uniform hierarchy - SSE event payloads now follow a uniform structure — create events include the new entity under `data.entity`, update events include only changed fields under `data.changes`, and delete events include identifiers only - Workspace slug can now be updated via the workspace settings endpoint - Workspace status change responses now include the `previousStatus` field - View permissions dialog now updates in real time — member and guest changes by other users appear instantly via SSE - When an admin syncs the database schema, other users viewing the spreadsheet see a warning banner prompting them to refresh for the latest columns and data - Other admin clients now silently update schema status metadata (view count, last checked, last synced) without showing disruptive alerts ### Added - View-level SSE stream (`/sse/view/{orgSlug}/{workspaceSlug}/{viewUuid}/stream`) — only users with a view open receive view-scoped events like cell edits, column changes, and bulk operations, with permission checked at connection time - Real-time notification when view access flags (addable, editable, exportable) change — other users see updates instantly - Real-time notification when a schema sync or drift check completes — other users on the workspace page see updated sync status, drift results, and last-checked timestamps without refreshing - Real-time notification when organisation subscription plan or status changes (e.g. upgrade, cancellation) ### Fixed - Row insertion now correctly checks the view's "addable" flag — previously it checked "editable" instead, allowing row inserts on views that had editing enabled but adding disabled - Workspace database config updates now persist correctly — previously the update response showed the right values but stale data could appear in subsequent reads - Login and signup pages now respect your saved dark mode preference - New workspaces now appear at the top of the dashboard list instead of the bottom ## 2026-03-13 ### Added - Real-time dashboard updates — usage stats and recent activity now push automatically via SSE when workspaces, views, or members change, so the dashboard always shows current data without refreshing - Dedicated database config endpoints (`GET/PUT/DELETE /api/workspaces/{uuid}/database-config`) — manage database connection settings independently from the workspace, consistent with storage, API, and MCP config endpoints - Standalone database connection test (`POST /api/database-config/test`) — validate database credentials before creating a workspace - Workspace list and detail responses now include a `databaseName` field for quick reference without loading full config ### Fixed - Filter presets now apply their saved filters and sort order when loading a view — previously only column layout was applied, so reloading a page with a preset would show unfiltered data ### Improved - Token refresh endpoint is more resilient — no longer returns intermittent 500 errors when organisation role is missing from the token ## 2026-03-12 ### Added - About, Careers, and Status pages on the public website - System health endpoint (`GET /api/status`) — check the status of all backend services with latency measurements - "Manage Account" and "Workspace Overview" links in the spread app user menu for quick navigation to the admin app - Recent organisation/workspace autocomplete on the spread login form, powered by localStorage history - API documentation now links to the MCP integration guide and mentions per-workspace Swagger UI and OpenAPI spec endpoints ### Improved - Spread login form redesigned to match the admin login (Material card, OAuth buttons, form field icons) - Admin and website footers unified with the same four columns: Product, Resources, Company, Legal - Footer links now point to real pages instead of placeholders - "Contact Us" footer link now uses the correct support email address - API documentation landing page (`/api/`) no longer returns a 404 ### Removed - "Remember me" checkbox removed from both login forms (was not functional) ## 2026-03-11 ### Added - Billing email address — set an optional billing email on your organisation that's used for invoices and payment communications, separate from your primary organisation email - Country selector — organisation country is now selected from a searchable dropdown of standardized ISO countries instead of free-text input - Public countries endpoint (`GET /api/countries`) — returns all available countries with ISO codes for use in dropdowns - GDPR consent tracking — registration, invitation acceptance, and OAuth first login now require explicit acceptance of terms of service and privacy policy, with optional marketing consent - Billing history — view past invoices with order numbers, amounts, and download links on the Plan & Billing page - Cancel subscription confirmation — cancelling a subscription now requires explicit confirmation via a dialog ### Improved - Checkout now pre-fills your billing address (country, postal code) and tax number when starting a subscription - Customer records at the payment provider now include your organisation's city and country - Existing free-text country values have been automatically migrated to standardized country codes - Billing integration — real API endpoints for subscription, checkout, cancel, invoices, and billing portal replace mock data - Invoice styling — order numbers, teal avatars, hover states, and consistent box-shadow across billing history - Eliminated M3 default tertiary green — all success/active states now use the app's proper teal/green palette in both light and dark mode - Organisation email is now required and cannot be left blank ## 2026-03-10 ### Added - Billing and subscription management — subscribe to a plan, view your current subscription, cancel or resume, and access the billing portal directly from the app - Provider-agnostic payment architecture — payment processing works through LemonSqueezy today with built-in support for switching to Stripe or other providers in the future - Webhook-based subscription sync — plan changes, cancellations, and payment events are automatically reflected in your account via secure webhook processing ### Improved - Dark mode styling across the admin app — plan cards, alert banners, empty states, input fields, and member tables now render correctly in dark mode - Workspace settings button shows a label on desktop and an icon-only cogwheel on mobile - Pages now scroll to the top when navigating between routes - Brand logo in the toolbar now links to the dashboard - Fixed duplicate workspaces appearing briefly after creating a new workspace - Fixed workspace delete failing with "Method Not Allowed" when the workspace was loaded from the list endpoint - Data platform app is now mobile responsive — hamburger menu with sidebar navigation, compact toolbar with filter and preset controls, and the properties panel overlays content instead of squeezing it on small screens - Tapping outside the properties panel on mobile now closes it - View permissions dialog hides the source column on mobile for a cleaner layout, and shortens the "Add Member" button to "Add" - Real-time workspace events (create, update, delete, status change) now include echo prevention so the originating browser tab won't show redundant notifications ## 2026-03-08 ### Added - Sign in with Google or GitHub — use your existing account to log in or register with one click, no password needed - Security alert emails — you now receive email notifications when sensitive account actions occur: password changes, email changes, two-factor authentication toggles, and API/MCP key operations - New device login detection — when you log in from a device you haven't used before, you'll receive a security alert email with device and IP details - Known devices tracking — SchemaStack remembers your devices and only alerts on truly new ones - Send Feedback link in the admin footer — quickly share feedback without leaving the app ### Improved - New organisations now land on the workspaces page instead of an empty dashboard, making it easier to create your first workspace - Error messages on login and registration pages now clear automatically when you navigate between pages - Organisation settings button on the dashboard is now only visible to admins and owners - More consistent and reliable error responses across all API endpoints — errors now always return a JSON `{"error": "..."}` format with correct HTTP status codes - MCP `add_column` tool now accepts widget types (STRING, EMAIL, NUMBER, DATE, etc.) instead of raw database types — matches the same experience as the UI - Feedback screenshots are now stored in S3 with auto-generated thumbnails instead of inline base64 in the database - Auto-login after email verification — you're signed in immediately after confirming your code, no extra login step needed - Auto-login after accepting an invitation — new and existing users are signed in and taken straight to the organisation - Email verification now uses a 6-digit code instead of a clickable link — enter the code directly in the app without leaving the page - Verification codes expire after 10 minutes with a maximum of 5 attempts for added security - Resend verification is rate-limited to one request per 60 seconds to prevent spam ## 2026-03-07 ### Added - API key rotation — regenerate the secret for a workspace API key or MCP API key while keeping its name, permissions, access mode, view scopes, and expiration intact. The old key stops working immediately - Organisation-level SSE stream — a single real-time connection per organisation delivers workspace lifecycle events, member changes, config updates, and API/MCP key events to the admin app - Real-time notifications when workspaces are created or deleted within your organisation - Real-time notifications when organisation members are added, updated, or removed - Real-time notifications for workspace configuration changes (API keys, MCP keys, MCP config, API config, storage config) - Rich onboarding empty state when you have no workspaces yet — includes feature highlights, benefits overview, and a quick-start button - Welcome screen when a workspace has no views, with a one-click "Create View" button - MCP (Model Context Protocol) support — AI clients like Claude Desktop can now interact with your workspaces through the standard MCP protocol - Per-workspace MCP access control with four levels: Disabled, Read-Only, Data-Only, and Full - 18 MCP tools covering workspace browsing, view/column management, data querying, record creation/editing, and constraint management - New MCP config endpoints to manage per-workspace access settings - MCP API keys — dedicated API keys for AI agents to connect via MCP without a browser session. Create, list, and revoke keys per workspace - Per-key MCP access control — each MCP API key can have its own access level (Read-Only, Data-Only, Full) independent of the workspace default, and can be scoped to specific views with per-view roles (Viewer, Editor, Admin) ### Improved - Slug fields now show a live URL preview (e.g. `schemastack.io/my-org/my-workspace`) instead of generic hint text when creating organisations, workspaces, and views - Platform upgraded to Quarkus 3.30 for improved performance and compatibility with the MCP server extension ## 2026-03-06 ### Added - Create your own organisation after registration — no invitation required - Check organisation slug availability before creating an organisation - Check workspace slug availability within an organisation - Check view slug availability within a workspace - Schema options (nullable, unique, length, precision, scale, default value) can now be set directly when creating a new column - Timestamp auto-set behavior (created_at, updated_at) configurable in both the add-column dialog and column properties panel ### Improved - New SVG logo replaces the text lettermark across the entire app for a sharper, scalable brand identity - SVG favicons added for both admin and app - Dialog widths standardized across all dialogs for a more consistent experience - Column type selector now shows icons alongside type names for easier scanning - Workspace overview shows a prominent call-to-action when no workspaces exist yet - All documentation and help links now point to [docs.schemastack.io](https://docs.schemastack.io) ## 2026-03-05 ### Improved - File uploads and thumbnail generation moved to dedicated JVM worker for better performance with large files and improved reliability - Replacing a file or image in a cell now automatically deletes the old file from storage — no orphaned files left behind ## 2026-03-04 ### Added - Column constraints (e.g., NOT_BLANK, MAX_LENGTH, EMAIL, PATTERN) can now be sent inline when creating a column, removing the need for separate API calls - Default value support in advanced column options - Visibility and access controls (API visible, sortable) can now be set when creating a column ### Improved - Default values are now validated against the column type before saving — invalid combinations (e.g., `now()` on a boolean) are rejected with a clear error message - Default value input now shows type-aware placeholders, contextual hints, and quick-insert chips for common defaults (e.g., `true`/`false` for checkboxes, `now()` for timestamps) ## 2026-03-03 ### Added - Owner crown badge on member tables across organization, workspace, and view permissions - Inline "Pending" badge for invited members in the organization members list - Joined date shown on all member rows (e.g., "Joined Mar 2026") - Summary stat cards below member tables: Active Members, Pending Invites, and Admin Users - Reusable alert banner component with warning, error, info, and success variants - "Current Usage" overview section and "Usage by Workspace" breakdown on the Plan & Billing page - View column properties panel with description editing ### Improved - Access mode dialog redesigned with colored icon avatars, capability chips, "Selected" badge, and blue info box - Dialog headers updated with larger icons, bolder titles, and subtle gradient in dark mode - Consistent green/red capability chips across the app with proper dark mode colors - Blue info boxes now have explicit light and dark mode styling - Form field backgrounds now properly adapt to dark mode - Gray borders unified across the app for a cleaner look - Plan & Billing page redesigned: active plan card is visually elevated with teal-green border and badges, Enterprise card has a dark premium look with golden crown - Consistent elevation styling across header cards, member tables, and plan cards - Standardized lighter gray for secondary/metadata text across all admin pages - Typography utility classes (`.mat-display-large` through `.mat-label-small`) now correctly generated in compiled CSS - Member avatars updated to 40px rounded squares for a modern look - Workspace overview tabs no longer stretch to full width ## 2026-03-01 ### Added - Widget options framework: per-widget-type configurable settings stored as JSONB on view columns - DATETIME auto-set timestamps: configure columns to auto-fill `CURRENT_TIMESTAMP` on insert, update, or both — applied at the database level via `DEFAULT` and triggers - S3 storage backend: configure per-workspace S3 credentials (AWS S3, MinIO, DigitalOcean Spaces, Backblaze B2) for file and image storage instead of local filesystem - S3 connection testing: verify bucket access before saving configuration - Smart file mapping: map existing S3 files to FILE/IMAGE columns using path templates with row-level expressions (`${value}`, `${row.column_name}`, `${workspace.uuid}`) - Entity-level default file path template: set a fallback S3 template for all FILE/IMAGE columns in an entity - Column-level S3 path template: override the entity default for individual columns - Presigned URLs enabled by default: file downloads redirect directly to S3, reducing API bandwidth - Thumbnail presigned URLs served from platform S3 for faster image previews - `hasStorageConfig` field on workspace membership endpoint so the frontend knows when S3 is configured - Proxied mapped file downloads: seamless download of template-resolved S3 files through the existing file download endpoint - File path templates configurable at 3 levels: workspace (admin), view, and column (IMAGE/FILE columns only), with collapsible variable reference and live preview - Shared widget components for file upload and simple inputs, reused across cell-edit popover and add-row dialog - IMAGE/FILE upload support in the add-row dialog ### Fixed - Column schema migrations (widget options, type changes, constraints) now correctly use the actual database column name instead of the display name - SQL identifiers in schema migrations are now properly quoted, preventing errors with column names containing spaces or special characters ### Improved - New columns created through the UI now use sanitized database column names (e.g., "Created at" → `created_at`) instead of storing the display name - Renamed column-level `s3PathTemplate` to `filePathTemplate` for consistency across all levels (column, entity, workspace) - Managed files (uploaded through SchemaStack) now always resolve correctly even when a file path template is configured on the column - File path template changes require explicit "Apply" instead of auto-saving, preventing interference with in-progress uploads - Warning shown when changing path templates that existing files will not be moved - Storage configuration check now uses the workspace member endpoint instead of a separate API call ### Security - S3 credentials encrypted at rest using AES-256/GCM (same encryption as database credentials) - S3 secrets are write-only in the API — never returned in responses - Path traversal protection in S3 template resolution ## 2026-02-28 ### Added - Real-time SSE updates for workspace events: status changes, settings updates, member changes, and database configuration now sync instantly across all open tabs and users - Real-time view access updates: changes to view permissions (addable, editable, exportable) are reflected immediately for all collaborators - Real-time view member and guest link changes are broadcast to all connected users - Plan limit enforcement: workspace, view, and member creation is now blocked when your subscription tier limit is reached, with clear error messages showing the current count and maximum allowed - Subscription tiers (Free, Pro, Enterprise) with configurable plan limits for workspaces, views, members, API calls, and storage - Usage tracking: monthly request counts, row reads/writes, and storage are recorded per workspace - Usage & Subscription API: view current plan, usage summary, and per-workspace breakdown via `/api/subscription` - Rate limiting for the workspace API: per-minute burst limits and monthly request quotas, with standard `X-RateLimit-*` response headers - Per-workspace and per-entity rate limit overrides configurable from the admin app - SSL/TLS support for external database connections: configure SSL mode (Disable, Prefer, Require, Verify CA, Verify Full), upload CA and client certificates for secure connections to cloud-managed databases (AWS RDS, Azure, DigitalOcean, Google Cloud SQL) - Connection timeout setting for external database connections - Categorized connection test errors (Network, Auth, SSL, Timeout) for clearer troubleshooting - Workspace-level API settings: configure max expand depth and CORS allowed origins per workspace - Per-entity API settings: manage default expand, field selection, expandable relationship whitelists, and filterable field whitelists from the admin app - Column API visibility: hide sensitive columns from REST API responses while keeping them visible in the admin UI - Public access for entities: configure unauthenticated access (None, Read Only, or Read & Write) per entity - Dynamic CORS: per-workspace origin restrictions replace the previous allow-all default - Workspace-level max expand depth acts as fallback when entity-level is not configured ### Security - Upgraded encryption from AES/ECB to AES/GCM for database credential and 2FA secret storage (existing encrypted values are automatically decrypted via backward-compatible fallback) - SSL certificate temp files are now cleaned up when workspace metadata is evicted from cache instead of accumulating until shutdown ### Improved - Workspace API URLs now use dashed slugs (e.g. `/column-metadata`) matching org and workspace URL conventions - Swagger/OpenAPI sandbox lists entities with dashed slug URLs - SSE connection status banners now show when real-time connection is lost or reconnecting - Offline detection banners in both Admin and Data Platform apps - Confirmation dialogs for all destructive actions (replaces browser confirm prompts) - SSE reconnection uses jitter to prevent thundering herd on server restarts ### Fixed - Production environment detection now uses Angular's built-in `isDevMode()` - CI/CD pipeline build paths and linting - Console logging stripped from production builds ### Fixed - SSE disconnect banner now matches the height of other status banners - View permissions toggles (addable, editable, exportable) no longer break after the first change - Workspace status changes now update the status banner in real time - Retry logic for newly created views no longer silently drops the final error --- # Security Source: https://docs.schemastack.io/security SchemaStack is built with security at its core. This page describes how we protect your account, your metadata, and your data. ## Your Data Stays Yours SchemaStack never stores your business data. We connect to **your** database and only store metadata — entity definitions, view configurations, and permissions. Your data remains in your infrastructure, under your control. For details, see [Data Ownership](https://docs.schemastack.io/guide/data-ownership.md). ## Infrastructure ### Encryption in Transit All connections between your browser, our API, and your database are encrypted with TLS. API requests are served exclusively over HTTPS. ### Encryption at Rest Sensitive data stored by SchemaStack is encrypted before it reaches the database: - **TOTP secrets** are encrypted with AES-256-GCM - **Database credentials** for workspace connections are encrypted at rest and masked in API responses - **Backup codes** are hashed with BCrypt (one-way, cannot be reversed) ### Hosting SchemaStack runs on AWS infrastructure in the EU (Frankfurt, `eu-central-1`). Services are deployed across managed compute (Lambda, Fargate) with no shared tenancy. ## Authentication ### Passwords Passwords are hashed using **BCrypt** with a work factor of 12. Each password is salted individually. Plaintext passwords are never stored or logged. ### JSON Web Tokens (JWT) API authentication uses short-lived, signed JWT tokens issued over RSA key pairs. Tokens carry scoped claims — including organization context — and are validated on every request. ### Two-Factor Authentication (2FA) Users can enable TOTP-based two-factor authentication using any standard authenticator app. During setup, 10 single-use backup codes are generated and stored as BCrypt hashes. TOTP secrets are encrypted with AES-256-GCM before storage. ### OAuth Single Sign-On Sign in with **Google** or **GitHub**. OAuth tokens are used only for identity verification and are never persisted. ### New Device Alerts When a login occurs from an unrecognized device, a security alert email is sent with device type, IP address, and approximate location. ## Session Management Each login creates a tracked session with device information, IP address, and timestamps. You can view all active sessions and revoke any session instantly from your account settings. Revoked sessions are terminated immediately. ## Access Control SchemaStack enforces role-based access control (RBAC) across four levels: | Level | Roles | Purpose | | --- | --- | --- | | Organization | Owner, Admin, Member | Control over billing, members, and workspaces | | Workspace | Admin, Editor, Viewer, Member | Schema management and data access | | View | Admin, Editor, Viewer | Fine-grained view permissions | | Column | Read, Write, Hidden | Per-column visibility and edit control | Permissions are evaluated from the most specific level up. A view-level role overrides the workspace-level role, allowing both promotions and restrictions. Every API request is checked against the user's role and membership before processing. ### View Settings Each view has three flags that control what non-admin users can do with data: | Setting | Effect when enabled | | --- | --- | | **Addable** | Editors can insert new rows | | **Editable** | Editors can edit, delete, and bulk-update rows | | **Exportable** | Editors and Viewers can export data | View Admins bypass these settings entirely. When a setting is disabled, the corresponding actions are blocked at the API level — not just hidden in the UI. | Action | Admin | Editor | Viewer | | --- | --- | --- | --- | | View data | always | always | always | | Add rows | always | requires Addable | denied | | Edit / delete rows | always | requires Editable | denied | | Bulk update / delete | always | requires Editable | denied | | Export | always | requires Exportable | requires Exportable | ### Multi-Organization Isolation Users who belong to multiple organizations see only the data for the organization selected in their current token. Cross-organization data access is blocked at the API layer and covered by automated security tests. ## API Security ### API Keys Workspace API keys use a `sk_` prefix and are generated from 256 bits of randomness. Only a SHA-256 hash is stored — the plaintext key is shown once at creation and cannot be retrieved afterward. Keys support optional expiration dates, read-only or read-write scopes, and can be rotated or revoked at any time. ### Input Validation All API inputs are validated using Jakarta Bean Validation before reaching business logic. Database queries use parameterized statements (Hibernate ORM and JDBC PreparedStatement) throughout — raw string concatenation is never used in queries. ### Rate Limiting Request rates are configurable per subscription tier, per workspace, and per entity. Body size limits are enforced at the HTTP layer. ### CORS Cross-origin requests are restricted to explicitly configured origins per workspace. Preflight requests are handled automatically. ### Webhook Verification Inbound webhooks (payment provider callbacks) are verified using HMAC-SHA256 signature validation before processing. ## Account & Data Protection ### Email Verification Account registration requires email verification via a 6-digit code with a 10-minute expiry and a maximum of 5 attempts. ### Password Reset Password resets use single-use, time-limited tokens delivered by email. Tokens expire after 1 hour. ### Account Deletion Account deletion follows a two-step confirmation flow: password verification followed by email confirmation. A 7-day grace period allows recovery before permanent deletion. ## Privacy & Compliance ### GDPR - **Data separation** — your business data never enters SchemaStack infrastructure, simplifying data processing agreements. - **Consent tracking** — all consent actions (terms of service, privacy policy, marketing) are logged with timestamp, IP address, and policy version. - **Right to deletion** — account deletion is self-service with a clear grace period. - **Data residency** — your data stays wherever your database is hosted. SchemaStack metadata is stored in the EU. ### Audit Logging Consent changes, session events, and administrative actions are recorded with full context for compliance purposes. ## Responsible Disclosure If you discover a security vulnerability, please report it to **support@schemastack.io**. We take all reports seriously and will respond promptly.