Data Service API

The Data Service API provides a powerful, flexible system for managing dynamic data structures using Frictionless Table Schema. It follows a hierarchical structure: App → DataSource → DataTable.

Overview

The Data Service enables you to:

• Create Apps: Top-level containers for organizing data
• Define DataSources: Collections of tables with specific provider types (PostgreSQL, JSONB, MongoDB)
• Manage DataTables: Define table structures using Frictionless Table Schema
• Validate Schemas: Automatic validation of table schemas
• Track Operations: Audit trail for all schema operations

Architecture

graph TD
    A[App] -->|has many| B[DataSource]
    B -->|has many| C[DataTable]
    C -->|defines| D[Frictionless Schema]
    C -->|materializes to| E[Physical Table]
    B -->|uses| F[Provider: PostgreSQL/JSONB/MongoDB]
    C -->|tracked by| G[SchemaOperation]

Key Features

The Data Service provides enterprise-grade capabilities for data management:

• ✅ CRUD Operations: Complete Create, Read, Update, Delete API for all tables
• ✅ Advanced Querying: 20+ filter operators, full-text search, sorting, pagination
• ✅ Aggregations: SQL-like GROUP BY, HAVING, and aggregate functions (SUM, AVG, COUNT, etc.)
• ✅ Relationships: Foreign keys, JOIN support, automatic relationship population
• ✅ Hierarchies & Graphs: Organizational charts, network traversal with BFS/DFS algorithms
• ✅ Schema Migrations: Automatic schema evolution with Alembic, zero-downtime updates
• ✅ Multiple Index Types: B-tree, GIN, Hash, BRIN, unique, composite, partial indexes
• ✅ Authorization: Policy-based access control with Cerbos, row-level security
• ✅ Multiple Providers: Choose between FlatTable (normalized), JSONB (flexible), or MongoDB (planned)
• ✅ Full-Text Search: PostgreSQL text search with ranking and relevance
• ✅ Bulk Operations: Efficient batch inserts, updates, and deletes
• ✅ Soft Delete Support: Mark records as deleted without removing them
• ✅ Audit Trail: Track all schema and data changes
• ✅ Tenant Isolation: Complete data separation per organization

Key Concepts

App

A top-level container that groups related data sources. Each app has:

• Name & Slug: Human-readable and URL-safe identifiers
• Description: Optional documentation
• Multiple DataSources: Can contain different provider types

DataSource

A collection of tables using a single provider type:

• Provider Types:
  • flat_table: Traditional PostgreSQL tables with full schema enforcement and Alembic migrations
  • jsonb: PostgreSQL JSONB storage with flexible schema-less structure (id + data JSONB)
  • mongodb: MongoDB collections (planned - not yet implemented)
• Provider Config: JSON configuration for provider-specific settings
• Isolated: Each datasource is independent
• Schema Management: Each provider handles schema changes differently:
  • flat_table: Uses Alembic to generate ALTER TABLE migrations
  • jsonb: Metadata-only updates (physical table structure never changes)
  • mongodb: Schema-less (no physical migrations needed)

DataTable

Defines a table structure using Frictionless Table Schema:

• Name: Used in API URLs (e.g., /datatables/users)
• Schema: Frictionless Table Schema (JSON format)
• Physical Table Name: Auto-generated (e.g., myapp_users)
• Materialization: Can be created as actual database tables
• Schema Hash: SHA256 hash for cache invalidation

Base URL Structure

All Data Service endpoints follow this app-centric pattern:

/api/apps/                                                    # App management
/api/apps/{app_slug}/datasources/                            # DataSource management
/api/apps/{app_slug}/datasources/{datasource_slug}/datatables/   # DataTable management
/api/apps/{app_slug}/datatables/                             # Cross-datasource queries

Quick Start

1. Create an App

POST /api/apps/
Content-Type: application/json
Authorization: Bearer YOUR_ACCESS_TOKEN

{
  "name": "My Application",
  "description": "My awesome application"
}

Response:

{
  "id": 1,
  "name": "My Application",
  "slug": "my-application",
  "description": "My awesome application",
  "datasource_count": 0,
  "table_count": 0,
  "created_at": "2024-10-30T12:00:00Z"
}

2. Create a DataSource

POST /api/apps/my-application/datasources/
Content-Type: application/json
Authorization: Bearer YOUR_ACCESS_TOKEN

{
  "name": "Users Database",
  "slug": "users-db",
  "provider_type": "flat_table",
  "description": "User data storage"
}

3. Create a DataTable

POST /api/apps/my-application/datasources/users-db/datatables/
Content-Type: application/json
Authorization: Bearer YOUR_ACCESS_TOKEN

{
  "name": "users",
  "description": "User accounts table",
  "json_schema": {
    "fields": [
      {
        "name": "id",
        "type": "integer",
        "constraints": {
          "required": true,
          "unique": true
        }
      },
      {
        "name": "email",
        "type": "string",
        "format": "email",
        "constraints": {
          "required": true
        }
      },
      {
        "name": "name",
        "type": "string",
        "constraints": {
          "required": true
        }
      },
      {
        "name": "created_at",
        "type": "datetime"
      }
    ],
    "primaryKey": ["id"]
  }
}

Features

Schema Validation

All DataTable schemas are validated using Frictionless Table Schema specification:

• Field Types: string, integer, number, boolean, datetime, date, time, year, etc.
• Constraints: required, unique, minimum, maximum, pattern, enum
• Foreign Keys: Reference other tables
• Primary Keys: Define unique identifiers

See Frictionless Table Schema Guide for details.

Cross-DataSource Queries

Query all tables across all datasources for an app:

GET /api/apps/my-application/datatables/

Filter by datasource:

GET /api/apps/my-application/datatables/?datasource=users-db

Filter by materialization status:

GET /api/apps/my-application/datatables/?is_materialized=true

Statistics & Analytics

Get comprehensive statistics:

# App-level stats
GET /api/apps/my-application/stats/

# DataSource-level stats
GET /api/apps/my-application/datasources/users-db/stats/

# Cross-datasource stats
GET /api/apps/my-application/datatables/stats/

Soft Delete

All resources support soft deletion (marked as inactive):

# Delete app (also inactivates all datasources and datatables)
DELETE /api/apps/my-application/

# Delete datasource
DELETE /api/apps/my-application/datasources/users-db/

# Delete datatable
DELETE /api/apps/my-application/datasources/users-db/datatables/users/

Schema-Only Operations

Get just the Frictionless schema without full table details:

GET /api/apps/my-application/datasources/users-db/datatables/users/schema/

Rate Limiting

All endpoints are rate-limited for security:

• Standard Limit: 100 requests per minute
• Burst Limit: 10 requests for write operations
• Authentication: Rate limiting per authenticated user

Tenant Isolation

All Data Service operations are tenant-scoped:

• Data is completely isolated per tenant
• No cross-tenant data access
• Automatic tenant detection from domain/subdomain

Common Use Cases

The Data Service is designed for a wide range of applications:

Content Management Systems (CMS)

• Blog platforms: Posts, comments, authors, categories with relationships
• Documentation sites: Articles with hierarchical organization
• Publishing workflows: Draft → Review → Published status management
• Features: Full-text search, soft delete, audit trails

E-Commerce Platforms

• Product catalogs: Products, categories, inventory with JSONB for flexible attributes
• Order management: Orders, line items, fulfillment tracking
• Customer analytics: Aggregations for sales reports, customer segmentation
• Features: Complex filtering, aggregations, relationship population

Analytics Dashboards

• Business intelligence: Real-time aggregations with GROUP BY and HAVING
• KPI tracking: Time-series data with date-based grouping
• User analytics: Activity tracking, event logging, funnel analysis
• Features: Aggregations, date functions, efficient querying

Social Networks

• User profiles: Users with flexible metadata (JSONB provider)
• Connections: Graph traversal for friend networks, followers
• Content feeds: Posts with likes, comments, shares
• Features: Graph queries, relationship population, hierarchies

Organizational Management

• Employee directories: Hierarchical org charts with manager relationships
• Department structures: Tree queries for organizational hierarchies
• Access control: Row-level security based on department/role
• Features: Hierarchy queries, authorization policies, graph traversal

IoT & Time-Series Data

• Sensor data: High-volume inserts with efficient bulk operations
• Device management: Flexible schemas for various device types (JSONB)
• Aggregated metrics: Hourly/daily summaries with date_trunc aggregations
• Features: Bulk operations, BRIN indexes, efficient aggregations

Project Management

• Task tracking: Tasks with statuses, assignments, dependencies
• Milestone planning: Date-based filtering and grouping
• Team collaboration: Authorization for department-based access
• Features: Advanced filtering, authorization, relationships

Next Steps

Getting Started

• Quick Start Guide - Build your first app in 10 minutes
• Schema Guide - Learn Frictionless Table Schema format
• API Endpoints Reference - Complete endpoint listing

Core Guides

• CRUD Operations - Creating, reading, updating, deleting data
• Querying & Filtering - Advanced queries with 20+ operators
• Aggregations - SQL-like GROUP BY and aggregate functions
• Relationships - Foreign keys and relationship population

Advanced Topics

• Hierarchies - Tree structures and organizational charts
• Graph Traversal - Network queries with BFS/DFS
• Authorization - Policy-based access control with Cerbos
• Migrations - Schema evolution with Alembic
• Indexes - Creating and managing database indexes
• Data Imports - Bulk data loading from CSV/JSON

Reference

• Admin Interface - Using Django admin for management

Examples

Complete Workflow

See the API Endpoint Reference for comprehensive examples of:

• CRUD operations for all resources
• Schema updates and validation
• Data validation against schemas
• Error handling and responses
• Filtering and pagination