Deployment Modes in Ed-Fi ODS / API and related tooling (e.g. Year-Specific ODS Configuration, District-Specific ODS Configuration, Instance-Year-Specific ODS Configuration) allow various database partitioning to support the isolation, scaling and data management needs of implementations. See Multi-Tenancy Support in the Ed-Fi Tech Stack for details on how multi-tenancy is supported in the current technology.  

In Q4 2022, ODS API Feature Enhancements SIG met to discuss database partitioning in the Ed-Fi ODS API. SIG identified the following key requirements to better support emerging needs:

• As a collaborative hosting many districts, I need to be able to partition data collection on to different database servers.
• As a vendor, the data partitioning strategy my API host choses should be transparent to the API client. i.e. API routes should not change based on the chosen data partitioning strategy.
• As a manage service provider, I need to be able to host separate Admin and Security database per tenant, so that I can manage and administer them separately.
• As a manage service provider, I need better flexibility on how I apply database partitioning (e.g. one ODS per year, one ODS per district per year, one ODS per tenant).
• As an API host, I would like to link API client to a specific ODS instance, currently out of the box year specific deployment allows API client to access all school year.  
• As a state, I would like to keep the backward compatibility to show the school year in the route to the vendors and to be able to configure API client for multiple school year instances.

While the discussion in the SIG focused on how to normalize the various API "modes" into a generic route structure, this document proposes an alternative approach that addresses the identified requirements by introducing multitenancy as a first-class concept into the API. It will utilize tenant-specific configuration of API clients and ODS instances stored in the EdFi_Admin database rather than variations in API routes that provide the context for runtime connection string manipulation strategies. The following sections describe the proposed changes: 

API Client and ODS Instance Configuration

The connection strings for the ODS will now be managed in the EdFi_Admin database, while the connection string for Admin and Security databases will continue to be maintained in appsettings. The new database artifacts to support this are shown in the image below. Each ODS instance entry will now also contain its connection string. Each API client will be associated with 1 or more ODS instances. This association between each API client and the ODS instance(s), provides the required context and allows elimination of route segments to identify ODS instance. 

Support for an API client to connect to multiple ODS instances is intended primarily to provide backwards compatibility with deployments that allow a single API client to connect to multiple year-specific ODS databases using different API routes. While the underlying goal of this design is to eliminate all contextual values from the out-of-the-box API routes, in this usage additional context will be required. The OdsInstanceContext table must be used to provide the metadata needed for the API to delineate which of the ODS instances to use based on runtime API request context. A unique constraint will be added containing the OdsInstanceId, ContextKey and ContextValue columns.

OdsInstanceContext records would be added as follows:

The value of ContextKey defined here would need to match a custom base route segment applied by the host to the API configuration in the appsettings.json file. The API will support a new configuration value under ApiSettings which could be set as follows:

{
    ...
    "ApiSettings": {
        "ContextualBaseRouteSegment": "{schoolYear:range(2000,2099)}",
        ...

Multi-tenancy in the Ed-Fi ODS API

Before discussing the technical details of designing for multi-tenancy, it would be good to first establish a shared understanding of what that means to the Ed-Fi ODS API and the Admin App. In the context of this discussion, each tenant should have an administrator account that is able to create and manage API clients and ODS instances for the tenant, but they should not be able to see or manage another tenant's resources.

When designing databases for multitenancy, there are 3 main approaches:

1. Segment tenants at the database level (each tenant has their own database)
2. Segment tenants at the schema level (there is a shared application database, but each tenant has its own schema containing identical table structures)
3. Segment tenants at the record level (there is one shared application database, and each table is modified to contain a "TenantId" column used by the application to filter data appropriately)

The design of the EdFi_Admin database necessitates the use of the first approach listed above – one EdFi_Admin database per tenant. The implication of this for supporting multi-tenancy in the API is that each request must supply the tenant context so that the API can connect to the correct EdFi_Admin database to perform API client authentication (for issuing and storing access tokens) and then subsequently to validate the access token and obtain API client details.

Single Tenant Deployments (default, matching current behavior)

When the API is not configured for multi-tenancy, the API will be operating in "single tenant" mode and exhibit the following behavior:

• Routes will no longer be configured with any of the API mode-specific contextual values currently used for building the ODS connection strings.

• API will use single configured connection strings for the EdFi_Admin and EdFi_Security databases, as shown below:

{
    ...
    "ConnectionStrings": {
        "EdFi_Admin": "Server=(local); Database=EdFi_Admin; Trusted_Connection=True; Application Name=EdFi.Ods.WebApi;",
        "EdFi_Security": "Server=(local); Database=EdFi_Security; Trusted_Connection=True; Persist Security Info=True; Application Name=EdFi.Ods.WebApi;",
        ...
    },

NOTE: The EdFi_Ods connection string entry will no longer be present in the appsettings.json configuration file as a literal or templated connection string. This information will now be managed in the EdFi_Admin database and resolved based on the OdsInstanceId associated with the authenticated API client.

The New "Multitenancy" Feature

Multi-tenant behavior will be activated by enabling the new "Multitenancy" feature in the API configuration as follows:

{
    ...
    "ApiSettings": {
        ...
        "Features": [
            {
                "Name": "Multitenancy",
                "IsEnabled": true
            },
            ...

Once enabled, all routes for the API will be configured with a base segment of {tenantId}.  Additionally, a new configuration section will be supported for specifying the tenant-specific connection strings, as follows:

{
    ...
    "ApiSettings": {
        ...
    },
    "Tenants": {
        "grandbend": {
            "ConnectionStrings": {
                "EdFi_Admin": "Server=(local); Database=EdFi_Admin_grandbend; Trusted_Connection=True; Application Name=EdFi.Ods.WebApi;",
                "EdFi_Security": "Server=(local); Database=EdFi_Security_grandbend; Trusted_Connection=True; Persist Security Info=True; Application Name=EdFi.Ods.WebApi;"
            }
        },
        "northridge": {
            "ConnectionStrings": {
                "EdFi_Admin": "Server=(local); Database=EdFi_Admin_northridge; Trusted_Connection=True; Application Name=EdFi.Ods.WebApi;",
                "EdFi_Security": "Server=(local); Database=EdFi_Security_northridge; Trusted_Connection=True; Persist Security Info=True; Application Name=EdFi.Ods.WebApi;"
            }
        } 
    },
    ...
}

API client authentication requests in a multi-tenant API are depicted in the image below:

Subsequent requests to data management resources in a multi-tenant API would then use the tenant-specific Admin database to authenticate the access token:

The API client details resolved from the access token will provide the associated OdsInstanceId enabling the API to use the correct ODS connection string (loaded and cached separately from the appropriate EdFi_Admin database) for queries while servicing the request.

Ed-Fi ODS "Derivatives"

There are certain API requests that would be served not using the primary ODS, but some copy (or "derivative") of it. Current use cases around this are for supporting GET requests using a read-replica of the ODS database for offloading read workloads, and the use of the snapshots feature for providing processing isolation during change processing by API clients.

For these types of derivative ODS databases, we'll add the OdsInstanceDerivative table, shown below:

The DerivativeType will be unconstrained at the database level, but practically speaking the only two supported derivative types at this time would be "ReadReplica" and "Snapshot". A unique constraint will be added containing the OdsInstanceId and DerivativeType columns.

Open for Consideration:

Do field use cases need support for providing multiple snapshots for change processing?  
At present,  Ed-Fi ODS API allows hosts to maintain more than one snapshot (logged in changes.Snapshot table ) and allows API clients to choose the snapshot for change processing using the Snapshot-Identifier HTTP header. Is there a need for provisioning multiple snapshots? If single snapshot is sufficient, snapshot provisioning could be simplified by eliminating the changes.Snapshot table and providing full (untemplated) snapshot connection string in OdsInstanceDerivative table. Snapshot usage then would be reduced to yes/no.

Providing Feedback

If you have feedback on this design approach, drop a comment at the bottom of this page or email Vinaya Mayya