ORM: Eloquent Capsule | Engines: SQLite 3.35+ (default), MySQL 5.7+ / MariaDB 10.4+ Column types: cross-engine only — TEXT + $casts for JSON payloads, no engine-specific types for JSON payloads. (The one native json column is agent_prompt_templates.variables.) Schema management: DatabaseSchemaInstaller — versioned, component-aware, idempotent (see app/Core/DatabaseSchemaInstaller.php).

Tables

Table	Model	Purpose
users	Spora\Models\User	Managed by delight-im/auth. Spora adds created_at/updated_at, plus profile fields (name, date_of_birth, about_me, height_cm, weight_kg).
users_2fa, users_audit_log, users_confirmations, users_otps, users_remembered, users_resets, users_throttling	—	delight-im/auth auxiliary tables. Do not modify.
agents	Spora\Models\Agent	One agent per user. Stores identity, llm_driver_config_id (nullable FK), recipe_id, system_prompt, max_steps, allow_followup, retry_after_minutes, max_retries. Updated in spora-core PR #209: agents.user_id is dropped in favour of agents.principal_id (NOT NULL, FK to principals.id with ON DELETE RESTRICT). Ownership is now principal-scoped, not user-scoped — a single agent may be owned by a user-principal or by a group-principal. agents.principal_id cannot be deleted while the agent exists; controllers surface a structured 409 (PrincipalHasDependentsException) with agent_ids so the operator can transfer or delete the dependents first via POST /api/v1/agents/{id}/transfer.
tool_configurations	Spora\Models\ToolConfiguration	Global per-tool settings. One row per tool class. settings is encrypted via SecurityManager. All access via ToolConfigService only.
agent_tools	Spora\Models\AgentTool	Junction: which tools are enabled per agent. Per-operation approval lives in agent_tool_operation_overrides.
agent_tool_overrides	Spora\Models\AgentToolOverride	Per-agent credential overrides. Merged on top of global settings by ToolConfigService.
agent_tool_operation_overrides	Spora\Models\AgentToolOperationOverride	Per-agent per-operation enablement/approval flags. enabled and default_requires_approval are nullable 3-state — never cast to boolean.
tasks	Spora\Models\Task	One record per agent run. Status lifecycle: QUEUED → RUNNING → COMPLETED / FAILED / PENDING_APPROVAL ⇄ RUNNING / CANCELLED, RUNNING → AWAITING_SUB_AGENTS → RUNNING (sync) | QUEUED (worker) (added in spora-core PR #196 — suspends the parent while every spawned sub_agent child terminates), and RUNNING → ABORTED / AWAITING_SUB_AGENTS → ABORTED (added in spora-core PR #207 — user-initiated quiescent state, resumable). DB default PENDING, but the orchestrator always sets QUEUED or RUNNING on create. CANCELLED is set by TaskService::cancelRetryChain (app/Services/TaskService.php:250). ABORTED is set by Orchestrator::abort (app/Agents/Orchestrator.php) and stamped with data.aborted_at; Orchestrator::continue() clears aborted_at on resume and accepts {COMPLETED, FAILED, ABORTED, RUNNING} as valid sources (a RUNNING source auto-aborts and writes an abort_marker history row before re-prompting). parent_task_id enables follow-up lineage; retry columns (retry_of_task_id, retry_count, retry_after) from migration 0042. Book-keeping for the multi-child resume gate lives in data.spawned_sub_task_ids (live child ids) and data.sub_agent_expected_count (set after each spawn so the gate can compare against the live count at the batch boundary). Both pending_state and data are MEDIUMTEXT. error_code/error_message from migration 0017.
tool_calls	Spora\Models\ToolCall	Audit log of every tool invocation. Stores proposed_arguments, human_description (frozen at creation), approved_arguments, result. Status: PENDING → APPROVED / REJECTED (set on execution). Per-call rejection (migration 0062) adds rejected_at (timestamp), rejected_by (FK to users.id, onDelete SET NULL), and reject_reason (TEXT) — populated by Orchestrator::resume() when an entry in the per-call decision list has decision: 'reject'. The task-level reject() path does not write these columns. approvedBy() / rejectedBy() relations both target Spora\Models\User.
task_history	Spora\Models\TaskHistory	Append-only LLM conversation history. Ordered by sequence. content nullable (assistant tool-call messages have no text). content_blocks (JSON) holds the structured blocks (text, thinking, redacted_thinking, tool_use) — supersedes the old reasoning column. summarized_sequence_range records compaction (migration 0045).
usage	Spora\Models\Usage	1:1 with task_history. Per-message token accounting (input_tokens, output_tokens, reasoning_tokens, cached_tokens, cache_creation_tokens, cache_read_tokens) + provider, raw_usage and driver_meta_info. Added by migration 0061.
llm_driver_configurations	Spora\Models\LLMDriverConfiguration	Per-principal LLM driver configs (principal_id nullable FK to principals.id after migration 0067; was user_id before). principal_id IS NULL marks the global default config (is_global = true) — LLMDriverConfiguration::validateGlobalXor enforces the XOR. settings is encrypted JSON. is_default and is_global flags. context_window and max_tokens_output override defaults.
notifications	Spora\Models\Notification	User notification inbox. type in {task_completed, task_failed, pending_approval, scheduled_run_completed}. data is MEDIUMTEXT and carries {task_id, agent_id} (plus run_id for scheduled_run_completed). read_at marks read state.
agent_prompt_templates	Spora\Models\AgentPromptTemplate	Reusable prompt templates per agent. Stores prompt_template with Mustache vars, variables (native JSON), max_steps override.
scheduled_runs	Spora\Models\ScheduledRun	Scheduled or one-shot task triggers. Stores cron_expression or run_at, template_id FK, next_run_at precomputed. scheduled_runs_next holds one concrete due execution per row. All timestamp columns (run_at, next_run_at, last_run_at, created_at, updated_at) are stored in UTC; the timezone column is the user-chosen IANA id (UTC if omitted) used as the reference frame for cron_expression evaluation and run_at anchoring, not a storage format. UTC timestamps mean the worker can compare due_at (on the sibling scheduled_runs_next table) against wall-clock UTC on any host regardless of the OS timezone — see Worker deployment → Cron Mode.
scheduled_runs_next	Spora\Models\ScheduledRunNext	One row per concrete scheduled execution. Status enum: PENDING → CLAIMED → DONE / SKIPPED. Atomic claim: UPDATE scheduled_runs_next SET status = 'CLAIMED' WHERE status = 'PENDING' AND due_at <= $now LIMIT 1. Unique (scheduled_run_id, due_at) prevents duplicates. next_run_at on scheduled_runs is a cached derivative updated whenever a new PENDING row is inserted. All timestamp columns (due_at, claimed_at, completed_at, created_at, updated_at) are stored in UTC.
user_locations	Spora\Models\UserLocation	Per-user saved locations (e.g. for location-aware tool settings).
tool_user_settings	Spora\Models\ToolUserSetting	Per-principal tool settings overrides (NOT NULL principal_id FK to principals.id after migration 0067; was user_id before). Merged between global and agent overrides by ToolConfigService.
principal_preferences	Spora\Models\PrincipalPreference	Per-principal LLM-driver preference (preferred_llm_config_id FK). One row per principal. Renamed from user_preferences in migration 0067 — agents and tool_user_settings now key on principal_id instead of user_id, so the row’s natural key is the principal id, not the user id. A group principal can carry its own preferred_llm_config_id that differs from any member’s user-principal.
principals	Spora\Models\Principal	Unified ownership pointer (added in migration 0067). type enum ∈ {user, group}; exactly one of user_id / group_id is non-null. UNIQUE(user_id) and UNIQUE(group_id) prevent duplicates. One row is bulk-inserted per user during migration 0067 (idempotent via the unique index + INSERT OR IGNORE); PrincipalService::ensureUserPrincipal() materialises the caller’s row on demand if missing. agents, tool_user_settings, principal_preferences, and llm_driver_configurations all FK into this table — it is the single ownership axis across the application.
groups	Spora\Models\Group	Operator-owned container (added in migration 0067). name (≤ 120), description (≤ 500), created_by_user_id FK to users (RESTRICT on delete). Groups materialise a 1:1 group-principal on creation (PrincipalService::materialiseGroupPrincipal); deleting the group cascades into the group-principal via ON DELETE CASCADE.
group_memberships	Spora\Models\GroupMembership	Junction: group_id × user_id with role enum ∈ {owner, admin, member} (default member). UNIQUE(group_id, user_id) prevents duplicate rows. group_id FK is CASCADE on delete; user_id FK is CASCADE on delete.
group_pictures	Spora\Models\GroupPicture	1:1 with groups (migration 0068). Mirrors agent_pictures: either an archetype avatar (archetype / variant_key / palette_key) or an uploaded image (media_asset_id FK with NULL ON DELETE). The XOR invariant is enforced in GroupPictureService, not at the DB level. Migration 0069 backfills a default row for every existing group (collaborative / null / slate).
mail_templates	Spora\Models\MailTemplate	Editable email templates. Rendered with {{var}} placeholders.
memories	Spora\Models\Memory	Per-agent (or global) persistent memory entries. content is LONGTEXT.

Mail templates

Migration 0065_rename_body_text_to_body_in_mail_templates.php renames the stored body_text column to body without changing its SQL type or contents. body now contains CommonMark Markdown rather than a pre-rendered plain-text alternative. body_html is an optional trusted HTML shell and may contain the {markdown_html} injection token.

There is no stored body_text field after migration 0065. Plain text is derived from the rendered Markdown at send or preview time.

Key Decisions

Nullable TINYINT columns must NOT use Eloquent boolean cast — (bool) null === false collapses “use class default” into “OFF”, breaking three-state semantics. agent_tool_operation_overrides.enabled and default_requires_approval both rely on this.

TEXT not JSON columns — SQLite has no JSON type. TEXT + $casts is the only cross-engine approach for SQLite, MySQL 5.7, and MariaDB 10.4. (The one exception is agent_prompt_templates.variables, a native json column — kept narrow on purpose.)

pending_state is MEDIUMTEXT — full conversation history at high step counts exceeds MySQL TEXT’s 65,535-byte cap. (tasks.data and notifications.data are also MEDIUMTEXT for the same reason.)

human_description stored at creation — frozen so approval UI stays correct even after a plugin is removed or updated.

Both tool_name and tool_class stored in tool_calls — tool_name is what the LLM uses; tool_class is what PHP uses to instantiate. Both needed for unambiguous resolution and audit.

Schedule timestamps are UTC — scheduled_runs.run_at, next_run_at, last_run_at, created_at, updated_at and every column on scheduled_runs_next (due_at, claimed_at, completed_at, created_at, updated_at) are stored in UTC. The scheduled_runs.timezone column is the IANA reference frame the scheduler uses to evaluate cron_expression and anchor offset-less run_at strings — not a per-row storage format. The worker pins UTC internally before any due_at <= $now comparison (see Worker deployment), so a shared-host operator never has to configure their PHP date.timezone ini to match their schedule.

Migration order (on disk): users (+ auth) → agents → tool_configurations / agent_tools / agent_tool_overrides → tasks → tool_calls → task_history → usage → llm_driver_configurations → notifications → agent_prompt_templates / scheduled_runs → scheduled_runs_next → agent_tool_operation_overrides → user_locations / tool_user_settings / user_preferences / mail_templates / memories (plus assorted column-altering migrations interleaved). The version number in the stamp hash is the highest numbered filename.

Migration 0067 (introduce_principals_and_groups) is forward-only. The down() method is a no-op: the user_preferences → principal_preferences rename, the bulk-inserted user-principal rows, and the FK swap on agents cannot be losslessly reversed. Operators who need to roll back must restore from a backup taken before the upgrade. The migration runs the column swap outside any transaction so SQLite’s PRAGMA foreign_keys = OFF actually takes effect — without that, the table rebuild would cascade-delete every dependent row (tasks, task_history, tool_calls, agent_tools, the override tables, scheduled_runs, scheduled_runs_next, agent_prompt_templates, agent_pictures, usage). The pragma state is read back after each OFF / ON and the migration throws if the pragma was silently ignored.

Migrations 0068 / 0069 are also forward-only (creating / backfilling group_pictures); both have no-op down() methods.

Agent ownership transfer: an agent’s principal_id is mutable via POST /api/v1/agents/{id}/transfer. agents.principal_id FK uses ON DELETE RESTRICT — deleting a principal surfaces a structured 409 from the controller so the operator can re-target the orphan agents first; the controller response carries agent_ids and reassign_endpoint: /api/v1/agents/{id}/transfer.

Database Schema Installer

All database tables are created and upgraded automatically by Spora\Core\DatabaseSchemaInstaller during application boot. This mechanism wraps Laravel’s Migrator but is optimized for Spora’s zero-config, plugin-heavy environment:

• Auto-derived core version: The core schema version is derived at runtime by scanning database/migrations/ for the highest-numbered file. There is no CORE_VERSION constant to bump — adding a new migration file (e.g. 0050_new_feature.php) automatically increments the effective version.
• O(1) Hot Path Cache: install() is called on every application boot. To prevent executing multiple database queries per request, it computes a composite version hash (core_v49|plugin-a_v2|plugin-b_v1) and compares it to a local filesystem stamp (storage/.schema_stamp). If the hash matches, the installer returns immediately (0 DB queries).
• Component isolation: Each plugin (and the Core itself) has a tracked version in the schema_versions table. Plugins declare a static schemaVersion() in their manifest — see the Plugin system page.
• Migration file format: Migrations should use zero-padded numbers (e.g. 000001_create_table.php), an anonymous class pattern (return new class extends Migration {}), and directly use Capsule::schema() instead of the Laravel Schema facade.
• Plugin Migration Constraints: Plugin migration files must be globally prefixed with the plugin’s slug ({slug}_000001_name.php) to avoid file collision in the shared migrations tracking table. RuntimeException is thrown if this is violated.
• Boot Lifecycle: Database::getCapsule() exposes the static Eloquent capsule after bootDatabaseConnectionOnly() to allow the Installer to retrieve the DatabaseManager before the rest of the application loads.

Schema Installer API (for UI Install Script)

For shared-host deployments that cannot run CLI commands, invoke the installer directly from PHP:

use Spora\Core\Database;
use Spora\Core\DatabaseSchemaInstaller;

Database::boot(); // runs install() automatically

// Or manually:
$installer = new DatabaseSchemaInstaller(pluginLoader: null, stampPath: null);
$installer->install();

The DatabaseSchemaInstaller constructor accepts:

• $pluginLoader — pass null during early install before plugins are loaded
• $stampPath — pass null to force migrations to run (no stamp caching), useful for one-shot install scripts

After pulling new code, touching the storage/.schema_stamp file (e.g. deleting it) will force the installer to re-run all migrations on the next boot.

Edit this page on GitHub

Contributors: Fabian Graßl

Previous pagePlugin system

Next pageTesting