Cross-Package Database Table Access

[KaveeshaPiumini]

Problem

There are a few places in the backend where a package directly accesses tables that are conceptually owned by another domain/package. This weakens module boundaries and increases coupling between persistence layers.

Cross-domain table access identified

1. Theme management querying the APPLICATION table

Theme delete flow checks whether a theme is still referenced by applications.

Code:

// queryGetApplicationsCountByThemeID retrieves the count of applications using a theme.
queryGetApplicationsCountByThemeID = dbmodel.DBQuery{
	ID:    "THQ-THEME_MGT-08",
	Query: "SELECT COUNT(*) as total FROM APPLICATION WHERE THEME_ID = $1 AND DEPLOYMENT_ID = $2",
}

References:

• https://github.com/asgardeo/thunder/blob/main/backend/internal/design/theme/mgt/store_constants.go#L74-L78
• https://github.com/asgardeo/thunder/blob/main/backend/internal/design/theme/mgt/service.go#L243-L251

2. Layout management querying the APPLICATION table

Layout delete flow does the same check for layout references.

Code:

// queryGetApplicationsCountByLayoutID retrieves the count of applications using a layout.
queryGetApplicationsCountByLayoutID = dbmodel.DBQuery{
	ID:    "LAQ-LAYOUT_MGT-08",
	Query: "SELECT COUNT(*) as total FROM APPLICATION WHERE LAYOUT_ID = $1 AND DEPLOYMENT_ID = $2",
}

References:

• https://github.com/asgardeo/thunder/blob/main/backend/internal/design/layout/mgt/store_constants.go#L74-L78
• https://github.com/asgardeo/thunder/blob/main/backend/internal/design/layout/mgt/service.go#L244-L255

3. Entity management querying group tables

The entity package directly reads GROUP_MEMBER_REFERENCE and joins GROUP to resolve direct and transitive memberships.

Code:

// QueryGetGroupCountForEntity is the query to get the count of groups for a given entity.
QueryGetGroupCountForEntity = model.DBQuery{
	ID:    "ASQ-ENTITY_MGT-13",
	Query: `SELECT COUNT(*) AS total FROM GROUP_MEMBER_REFERENCE WHERE MEMBER_ID = $1 AND DEPLOYMENT_ID = $2`,
}

// QueryGetGroupsForEntity is the query to get groups for a given entity with pagination.
QueryGetGroupsForEntity = model.DBQuery{
	ID: "ASQ-ENTITY_MGT-14",
	Query: `SELECT G.ID, G.OU_ID, G.NAME FROM GROUP_MEMBER_REFERENCE GMR ` +
		`INNER JOIN "GROUP" G ON GMR.GROUP_ID = G.ID AND GMR.DEPLOYMENT_ID = $4 AND G.DEPLOYMENT_ID = $4 ` +
		`WHERE GMR.MEMBER_ID = $1 AND GMR.DEPLOYMENT_ID = $4 ` +
		`ORDER BY G.NAME LIMIT $2 OFFSET $3`,
}

Code:

// QueryGetTransitiveGroupsForEntity retrieves all groups an entity belongs to, including groups
// inherited through nested group membership, using a recursive CTE.
QueryGetTransitiveGroupsForEntity = model.DBQuery{
	ID: "ASQ-ENTITY_MGT-15",
	Query: `WITH RECURSIVE transitive_groups AS (
		SELECT GMR.GROUP_ID
		FROM GROUP_MEMBER_REFERENCE GMR
		WHERE GMR.MEMBER_ID = $1 AND GMR.DEPLOYMENT_ID = $2
		UNION
		SELECT GMR.GROUP_ID
		FROM GROUP_MEMBER_REFERENCE GMR
		INNER JOIN transitive_groups tg ON GMR.MEMBER_ID = tg.GROUP_ID
		WHERE GMR.MEMBER_TYPE = 'group' AND GMR.DEPLOYMENT_ID = $2
	)
	SELECT G.ID, G.OU_ID, G.NAME
	FROM transitive_groups tg
	INNER JOIN "GROUP" G ON tg.GROUP_ID = G.ID AND G.DEPLOYMENT_ID = $2
	ORDER BY G.NAME`,
}

References:

• https://github.com/asgardeo/thunder/blob/main/backend/internal/entity/store_constants.go#L111-L142
• https://github.com/asgardeo/thunder/blob/main/backend/internal/entity/store.go#L763-L826

Why this is a concern

At the moment, some packages are directly reading tables that are primarily owned by other domains. That makes the package boundary less clear. This becomes a concern for a few reasons:

1. Ownership becomes unclear

   When a package reads another package’s tables directly, it is harder to tell which package actually owns that data and its access rules.

   For example, theme deletion currently depends on application storage details instead of asking application-owned logic:

   // Check if theme is used by any applications
   count, err := ts.themeMgtStore.GetApplicationsCountByThemeID(id)
   if err != nil {
   	ts.logger.Error("Failed to check applications using theme", log.String("id", id), log.Error(err))
   	return &serviceerror.InternalServerError
   }
   
   if count > 0 {
   	return &ErrorThemeInUse
   }

   Reference:
   https://github.com/asgardeo/thunder/blob/main/backend/internal/design/theme/mgt/service.go#L243-L251

2. Schema changes can leak across domains

   If the APPLICATION table or group membership tables change, code in theme/layout/entity may also need to change even though those packages do not own those tables.

   That means a storage change in one domain can unexpectedly break another domain.

3. Business rules get tied to table structure

   A domain should ideally depend on another domain’s behavior, not on its raw table shape.

4. Refactoring becomes harder

   If this pattern grows, moving responsibilities later becomes more expensive because multiple packages start depending on each other’s tables directly.

Possible solution path

1. Introduce a neutral membership-read component or interface for group membership queries. Let entity delegate to that component first, and remove group-membership read responsibilities from entity.

2. Move count applications by theme/layout logic behind a dedicated cross-domain query interface.

3. Else update the Theme/Layout logic so that it should not directly query APPLICATION before theme/layout deletion.

Open questions for the community

1. Should we enforce a rule that only the owning package can access its tables directly?
2. For cross-domain read requirements, should we prefer service/interface calls or a dedicated read-model/query layer?
3. For the entity and group case, should group membership be considered part of the group domain, or a shared domain that deserves its own package?
4. Is it acceptable for update validation logic in theme/layout that depend on application-owned persistence details, or should that dependency be inverted?
5. Are there other known cross-domain DB accesses that should be included in the same cleanup effort?