Data Layer constraints on Project relationships

Project Constraints and Validation

The Inkeep Agent Framework implements multiple layers of validation to ensure that all agents and resources are associated with valid projects.

Database-Level Constraints

Foreign Key Constraints

All tables that reference projects include foreign key constraints to ensure referential integrity:

FOREIGN KEY (tenant_id, project_id)
REFERENCES projects(tenant_id, id)
ON DELETE CASCADE

This ensures that:

• No data can be inserted for non-existent projects
• Deleting a project cascades to remove all associated resources
• Data integrity is maintained at the database level

Tables with Project Constraints

The following tables have foreign key constraints to the projects table:

1. agent - Agent definitions (top-level Agents)
2. sub_agents - Sub Agent configurations
3. sub_agent_relations - Relationships between Sub Agents
4. tools - Tool configurations
5. context_configs - Context configurations
6. external_agents - External agent references
7. And more...

Runtime Validation

CLI Validation

The Inkeep CLI validates project existence before pushing agents:

// CLI checks if project exists
const existingProject = await getProject(dbClient)({
  scopes: { tenantId, projectId },
});

if (!existingProject) {
  // Prompt user to create project
  // ...
}

Data Access Layer Validation

The core package provides validation utilities for runtime checks:

import { validateProjectExists } from "@inkeep/agents-core";

// Validate before any operation
await validateProjectExists(db, tenantId, projectId);

Wrapped Operations

Data access functions can be wrapped with automatic validation:

import { withProjectValidation } from "@inkeep/agents-core";

const validatedCreateAgent = withProjectValidation(db, createAgent);
// Now automatically validates project before creating agent

Type-Safe Scope Helpers

The data access layer uses type-safe scope helpers to enforce consistent project scoping across all queries. This prevents accidental data leakage between tenants and projects.

ScopedTable Pattern

All DAL queries use scope helper functions instead of manual eq() WHERE clauses:

import { projectScopedWhere, tenantScopedWhere } from './scope-helpers';

// Instead of manual eq() calls:
// .where(and(eq(table.tenantId, tenantId), eq(table.projectId, projectId)))

// Use type-safe scope helpers:
.where(projectScopedWhere(agents, { tenantId, projectId }))

Available Scope Helpers

Security Benefits

• Compile-time safety: TypeScript enforces that all required scope fields are provided
• Consistent scoping: All queries automatically include the correct tenant/project filters
• Reduced errors: Prevents accidentally omitting scope conditions

Implementation Details

Schema Definition

export const agents = sqliteTable(
  "agent",
  {
    tenantId: text("tenant_id").notNull(),
    projectId: text("project_id").notNull(),
    // ... other columns
  },
  (table) => [
    primaryKey({ columns: [table.tenantId, table.projectId, table.id] }),
    foreignKey({
      columns: [table.tenantId, table.projectId],
      foreignColumns: [projects.tenantId, projects.id],
      name: "agent_project_fk",
    }),
  ]
);

Enabling Foreign Keys in SQLite

SQLite requires explicit enabling of foreign key constraints:

PRAGMA foreign_keys = ON;

This should be set when creating the database connection:

const client = createClient({
  url: dbUrl,
  authToken: authToken,
});

// Enable foreign keys
await client.execute("PRAGMA foreign_keys = ON");

Error Handling

When a constraint violation occurs, appropriate error messages guide users:

CLI Error

⚠ Project "my-project" does not exist
? Would you like to create it? (Y/n)

Database Error

Error: Project with ID "my-project" does not exist for tenant "my-tenant".
Please create the project first before adding resources to it.

Best Practices

1. Always validate at multiple levels: Database constraints, runtime validation, and UI validation
2. Provide clear error messages: Help users understand what went wrong and how to fix it
3. Offer solutions: When a project doesn't exist, offer to create it
4. Use transactions: Ensure atomicity when creating projects and related resources
5. Test constraints: Verify that constraints work as expected in tests
6. Use scope helpers: Always use projectScopedWhere and related helpers instead of manual eq() clauses for consistent data isolation

Migration Guide

For existing databases without constraints:

1. Backup your database before applying migrations
2. Check for orphaned data: Find resources referencing non-existent projects
3. Clean up orphaned data or create missing projects
4. Apply foreign key constraints using migrations
5. Enable foreign key enforcement in your database connection

-- Find orphaned agents
SELECT a.* FROM agent a
LEFT JOIN projects p
  ON a.tenant_id = p.tenant_id
  AND a.project_id = p.id
WHERE p.id IS NULL;

Benefits

• Data Integrity: Prevents orphaned data and maintains consistency
• Clear User Experience: Users are guided to create projects when needed
• Easier Debugging: Constraint violations are caught early
• Simplified Cleanup: Cascading deletes remove all related data
• Better Documentation: Constraints document relationships in the schema