Database Schema (D1)
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The SecretForge AI database architecture utilizes Cloudflare D1, a native SQLite-based relational database, to manage metadata, audit trails, and system configurations. The schema is designed to support a multi-tenant environment, handling user authentication, organization structures, and the lifecycle of encrypted secrets across various deployment environments (dev, staging, prod).
This schema serves as the central source of truth for the Cloudflare Workers API, ensuring data integrity through relational constraints and providing an audit trail for all secret operations. While actual secret values are stored in an encrypted format, the D1 database manages the critical metadata required for discovery, rotation, and compliance validation.
Sources: packages/api/schema.sql:1-5, README.md:38-57, SYSTEM_EVALUATION.md:364-375
Entity Relationship Diagram
The following diagram illustrates the relationships between core entities including users, organizations, encrypted secrets, and their associated audit logs.
This diagram represents the relational structure defined in the SQL schema, highlighting how users interact with organizations and secrets.
Sources: packages/api/schema.sql:1-210
Core Authentication and Tenancy
The schema implements a multi-tier subscription model and organizational hierarchy to manage access control and billing.
Identity Management
Users and Organizations are the primary entities for identity. Users can belong to multiple organizations through the organization_members table, which assigns specific roles such as 'owner', 'admin', 'member', or 'viewer'.
| Table | Field | Type | Description |
|---|---|---|---|
users | tier | TEXT | Subscription level: free, pro, team, enterprise |
users | password_hash | TEXT | Securely hashed password for authentication |
organization_members | role | TEXT | RBAC role within the organization context |
api_keys | key_hash | TEXT | Unique hash of the API key for system access |
Sources: packages/api/schema.sql:7-50, packages/api/src/schemas/validation.ts:13-15
API Key Management
The api_keys table stores metadata for keys used to access the SecretForge API programmatically. It includes a scopes field stored as a JSON array to restrict key capabilities.
Sources: packages/api/schema.sql:53-66, packages/api/src/schemas/validation.ts:54-58
Secret Metadata and Lifecycle
Secret management is handled through a metadata-first approach where encrypted values are stored alongside environment and project identifiers.
Secret Storage
The secrets table tracks the existence of sensitive values across different environments. A unique constraint on (name, project, environment) ensures that secret identifiers do not collide within a specific deployment context.
CREATE TABLE IF NOT EXISTS secrets (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
project TEXT NOT NULL,
environment TEXT NOT NULL CHECK(environment IN ('dev', 'staging', 'prod')),
tags TEXT NOT NULL DEFAULT '[]', -- JSON array
value_encrypted TEXT NOT NULL,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
UNIQUE(name, project, environment)
);
Sources: packages/api/schema.sql:69-81, PHASE1_IMPLEMENTATION.md:38-51
Audit and Compliance
Every interaction with a secret is logged in the audit_logs table, capturing the action type (created, retrieved, rotated, deleted, validated), the performing user, and the source IP address. Compliance results for frameworks like SOC2 or GDPR are stored in compliance_validations.
Sources: packages/api/schema.sql:87-100, packages/api/schema.sql:140-149
System Configuration and Automation
The database includes specialized tables for service providers, automated rotation, and event-driven notifications.
Service Configurations
The service_configs table acts as a registry for supported third-party providers. It defines authentication types (bearer, api_key, oauth2) and default scopes for services like Stripe, OpenAI, and AWS.
Sources: packages/api/schema.sql:103-112, packages/api/schema.sql:155-163
Webhooks and Deliveries
SecretForge supports event-driven integration through the webhooks table. When events like secret.rotated or compliance.violation occur, the system records the delivery attempt in webhook_deliveries to ensure reliability.
Sources: packages/api/schema.sql:178-204, packages/api/src/schemas/validation.ts:101-115
Data Flow: Secret Rotation
The following sequence shows how the database tracks an automated rotation event.
This flow illustrates the interaction between the rotation logic and the state stored in D1.
Sources: packages/api/schema.sql:128-137, packages/api/src/schemas/validation.ts:119-125
Database Triggers
To maintain data integrity and automated timestamping, the schema utilizes SQLite triggers to update updated_at fields across the users, organizations, and user_preferences tables whenever a record is modified.
Sources: packages/api/schema.sql:207-224
Summary
The D1 Database Schema provides the relational backbone for SecretForge AI. It effectively separates sensitive encrypted data from system metadata while enabling complex features such as multi-tenant organizations, automated rotation scheduling, and comprehensive audit logging. By leveraging SQLite's relational capabilities within the Cloudflare edge environment, the schema ensures low-latency access to secret metadata with robust consistency.
Sources: README.md:40-58, SYSTEM_EVALUATION.md:377-385