App Config Storage

Storage Strategy

The full app definition (including all pages, panes, navigation, and variable declarations) is stored as JSON in a single column. Key metadata fields are promoted to indexed columns for efficient querying:

-- AIExtension database: AppDefinitions table (conceptual)
CREATE TABLE AIExt_AppDefinitions (
    Id            BIGINT          PRIMARY KEY,
    TenantId      NVARCHAR(100)   NOT NULL,       -- Indexed for tenant scoping
    AppId         NVARCHAR(100)   NOT NULL,       -- Indexed for lookup
    AppName       NVARCHAR(255)   NOT NULL,       -- For listing without deserializing
    IsActive      BIT             NOT NULL DEFAULT 1,
    CreatedAt     DATETIME2       NOT NULL,
    CreatedBy     NVARCHAR(100)   NOT NULL,
    UpdatedAt     DATETIME2       NOT NULL,
    UpdatedBy     NVARCHAR(100)   NOT NULL,
    DefinitionJson NVARCHAR(MAX)  NOT NULL,       -- Full JSON definition
    UNIQUE (TenantId, AppId)
);

Why JSON Column for the Full Definition

App Studio app definitions are deeply nested and evolve frequently. Storing the full definition as JSON avoids schema migration cost for every new widget type, new config field, or new action type. The service layer serializes/deserializes the JSON — callers work with typed C# objects:

// Service layer handles serialization transparently:
public async Task GetAppAsync(string tenantId, string appId)
{
    var entity = await _db.AppDefinitions
        .Where(a => a.TenantId == tenantId && a.AppId == appId && a.IsActive)
        .FirstOrDefaultAsync();

    if (entity == null) return null;

    // Deserialize JSON → typed AppDefinition object
    return JsonSerializer.Deserialize(entity.DefinitionJson);
}

public async Task SaveAppAsync(string tenantId, AppDefinition app, string userId)
{
    var json = JsonSerializer.Serialize(app);
    // ... upsert with the serialized JSON
}

Promoted Columns for Listing

The AppName, TenantId, and IsActive columns are promoted out of the JSON so that listing APIs can run without deserializing the full definition for every row:

// Fast listing — no JSON deserialization
public async Task> GetAppsAsync(string tenantId)
{
    return await _db.AppDefinitions
        .Where(a => a.TenantId == tenantId && a.IsActive)
        .Select(a => new AppSummary
        {
            AppId = a.AppId,
            AppName = a.AppName,
            UpdatedAt = a.UpdatedAt,
            UpdatedBy = a.UpdatedBy
        })
        .ToListAsync();
    // DefinitionJson NOT loaded — efficient list query
}

Pages Storage

Pages (AppPages) are stored both embedded within the full DefinitionJson AND in a separate AIExt_AppPages table for individual page-level operations (get single page, update single page without replacing the full app):

-- AIExt_AppPages — for granular page operations
CREATE TABLE AIExt_AppPages (
    Id            BIGINT          PRIMARY KEY,
    TenantId      NVARCHAR(100)   NOT NULL,
    AppId         NVARCHAR(100)   NOT NULL,
    PageId        NVARCHAR(100)   NOT NULL,
    Route         NVARCHAR(500)   NOT NULL,       -- Indexed for route matching
    RequiredRoles NVARCHAR(MAX),                  -- JSON array of role strings
    PageJson      NVARCHAR(MAX)   NOT NULL,       -- Full page config JSON
    UNIQUE (TenantId, AppId, PageId)
);