Database Tables & Migration Reference

Overview

All ODP tables use the odp_ prefix. User-defined collections use their own names without a prefix. This document covers all system tables created by the migration files.

Supported databases: PostgreSQL, MySQL/MySQL2, SQLite/better-sqlite3

---

Core Schema Tables

odp_collections

Metadata for all collections (both system and user-defined).

Column	Type	Nullable	Default	Description
collection	string(255) PK	No		Collection name (= table name)
icon	string(30)	Yes		Admin UI icon
note	text	Yes		Description
color	string(10)	Yes		Brand color hex
display_template	string(255)	Yes		Item display template
hidden	boolean	No	false	Hide from admin UI
singleton	boolean	No	false	Single-item collection
archive_field	string(64)	Yes		Field used for soft-delete
archive_value	string(255)	Yes		Value that marks item as archived
unarchive_value	string(255)	Yes		Value that marks item as active
archive_app_filter	boolean	No	true	Show archive filter in app
sort_field	string(64)	Yes		Field used for manual sort
accountability	string(25)	No	all	Audit mode: all, activity, null
group	string(255) FK → odp_collections	Yes		Parent collection group
collapse	string(25)	No	open	Group collapse state
preview_url	string(255)	Yes		Preview URL template
versioning	boolean	No	false	Enable content versioning

---

odp_fields

Field metadata and display configuration.

Column	Type	Nullable	Default	Description
id	integer PK (auto)	No
collection	string(255) FK → odp_collections	No
field	string(255)	No		Field name (column name)
special	text (JSON)	Yes		Special field types (uuid, json, csv, etc.)
interface	string(255)	Yes		Admin UI interface component
options	text (JSON)	Yes		Interface options
display	string(255)	Yes		Display component
display_options	text (JSON)	Yes		Display options
readonly	boolean	No	false	Read-only in admin
hidden	boolean	No	false	Hidden in admin
sort	integer	Yes		Field order
width	string(30)	No	full	Display width
translations	text (JSON)	Yes		Label translations
note	text	Yes		Help text
conditions	text (JSON)	Yes		Conditional display rules
required	boolean	No	false	Required field
group	integer FK → odp_fields	Yes		Field group parent
validation	text (JSON)	Yes		Validation rules
validation_message	string(255)	Yes		Custom validation message
searchable	boolean	No	true	Include in search

---

odp_relations

Relation metadata between collections (foreign key descriptions).

Column	Type	Nullable	Default	Description
id	integer PK (auto)	No
many_collection	string(255)	No		The "many" side collection
many_field	string(255)	No		FK field on many side
one_collection	string(255)	Yes		The "one" side collection (M2O/O2M)
one_field	string(255)	Yes		Virtual O2M field name
one_collection_field	string(255)	Yes		M2A discriminator field
one_allowed_collections	text (JSON)	Yes		M2A allowed collection names
junction_field	string(255)	Yes		M2M junction field
sort_field	string(255)	Yes		Sort field for O2M lists
one_deselect_action	string(255)	No	nullify	nullify or delete

---

Auth & Identity

odp_roles

User roles for access control grouping.

Column	Type	Nullable	Default	Description
id	UUID PK	No	gen_random_uuid()
name	string(255)	No		Role display name
icon	string(30)	No	supervised_user_circle
description	text	Yes
parent	UUID FK → odp_roles	Yes		Parent role (role inheritance)
admin_access	boolean	No	false	Access to admin dashboard
app_access	boolean	No	false	Access to frontend app
tech_access	boolean	No	false	Superadmin: bypass all permission checks
impersonate_access	boolean	No	false	Can start impersonation sessions

---

odp_policies

Access control policies with optional IP restriction and TFA enforcement.

Column	Type	Nullable	Default	Description
id	UUID PK	No
name	string(255)	No		Policy name
icon	string(30)	No	badge
description	text	Yes
ip_access	text (JSON)	Yes		Array of allowed CIDR ranges
enforce_tfa	boolean	No	false	Require TFA for users with this policy
admin_access	boolean	No	false	Grant admin access
app_access	boolean	No	false	Grant app access

---

odp_access

Junction table: links policies to roles or individual users.

Column	Type	Nullable	Default	Description
id	UUID PK	No
policy	UUID FK → odp_policies	No		(CASCADE delete)
role	UUID FK → odp_roles	Yes		(CASCADE delete)
user	UUID FK → odp_users	Yes		(CASCADE delete)
sort	integer	Yes		Display/priority order

---

odp_permissions

Row-level RBAC permissions on collections.

Column	Type	Nullable	Default	Description
id	integer PK (auto)	No
policy	UUID FK → odp_policies	No		(CASCADE delete)
collection	string(255)	No		Collection name
action	string(10)	No		create, read, update, delete, share
fields	text (JSON)	Yes		Allowed fields (null = all)
permissions	text (JSON)	Yes		Row filter conditions
validation	text (JSON)	Yes		Write validation rules
presets	text (JSON)	Yes		Default field values for creates

---

odp_users

User accounts.

Column	Type	Nullable	Default	Description
id	UUID PK	No	gen_random_uuid()
first_name	string(50)	Yes
last_name	string(50)	Yes
email	string(255) UNIQUE	Yes
password	string(255)	Yes		Argon2 hash
location	string(255)	Yes
title	string(50)	Yes
description	text	Yes
tags	text (JSON)	Yes		Array of tag strings
avatar	UUID FK → odp_files	Yes
language	string(10)	No	en-US	Preferred language
tfa_secret	string(255)	Yes		TOTP secret (if TFA enabled)
status	string(16)	No	active	active, suspended, archived
role	UUID FK → odp_roles	Yes
token	string(255) UNIQUE	Yes		Static API token
last_access	timestamp	Yes
last_page	string(255)	Yes		Last visited admin page
email_notifications	boolean	No	true
appearance	string(10)	No	auto	auto, light, dark
theme_light	string(255)	Yes
theme_dark	string(255)	Yes
theme_light_overrides	text (JSON)	Yes
theme_dark_overrides	text (JSON)	Yes

---

odp_sessions

Active user sessions (refresh tokens).

Column	Type	Nullable	Default	Description
token	string(64) PK	No		Session/refresh token
user	UUID FK → odp_users	Yes		(CASCADE delete)
expires	timestamp	No		Session expiry
ip	string(255)	Yes
user_agent	text	Yes
share	UUID FK → odp_shares	Yes		For share sessions
origin	string(255)	Yes		Request origin
next_token	string(64)	Yes		Rolling session next token

---

odp_user_providers

Junction table: links users to SSO provider identities.

Column	Type	Nullable	Default	Description
id	UUID PK	No
user_id	UUID FK → odp_users	No		(CASCADE delete)
provider	string(128)	No		Provider name (e.g., google)
external_identifier	string(255)	No		Provider's user ID
provider_email	string(255)	Yes		Email from provider
auth_data	text (JSON)	Yes		Raw provider auth data
created_at	timestamp	Yes	now()

Unique constraint: (provider, external_identifier)

---

odp_user_tokens

Sub-tokens (scoped API keys).

Column	Type	Nullable	Default	Description
id	UUID PK	No
user_id	UUID FK → odp_users	No		(CASCADE delete)
name	string(255)	No		Human label
description	text	Yes
token_hash	string(64) UNIQUE	No		SHA-256 hash of the token
token_prefix	string(8)	No		Always odp:
scopes	json	Yes		["read","create","update","delete"] subset
allowed_roles	json	Yes		Array of role UUIDs
expires_at	timestamp	Yes
last_used_at	timestamp	Yes
revoked_at	timestamp	Yes
created_at	timestamp	No	now()

---

odp_impersonation_sessions

Impersonation session records.

Column	Type	Nullable	Default	Description
id	UUID PK	No
admin_user	UUID FK → odp_users	No		User performing impersonation
target_user	UUID FK → odp_users	No		User being impersonated
token	string(64) UNIQUE	No		Session lookup token
expires	timestamp	No		JWT expiry
ip	string(255)	Yes
user_agent	text	Yes
reason	text	Yes		Audit reason
ended_at	timestamp	Yes		Null if active
end_reason	string(50)	Yes		manual, expired, revoked
created_at	timestamp	No	now()

---

odp_ephemeral_codes

Short-lived one-time codes (used for SAML callback flow).

Column	Type	Nullable	Default	Description
code	string(64) PK	No		Random hex code
type	string(32)	No		saml
data	jsonb	No		Stored payload
expires_at	timestamp (TZ)	No		5-minute TTL
created_at	timestamp (TZ)	No	now()

---

Files & Storage

odp_folders

Hierarchical file organization.

Column	Type	Nullable	Default	Description
id	UUID PK	No
name	string(255)	No		Folder name
parent	UUID FK → odp_folders	Yes		Self-referential parent

---

odp_files

File records (metadata + storage reference).

Column	Type	Nullable	Default	Description
id	UUID PK	No
storage	string(255)	No	local	Storage driver name
filename_disk	string(255)	Yes		Internal filename (UUID-based)
filename_download	string(255)	Yes		Original filename for downloads
title	string(255)	Yes		Display title
type	string(255)	Yes		MIME type
folder	UUID FK → odp_folders	Yes
uploaded_by	UUID FK → odp_users	Yes
created_on	timestamp	Yes	now()
modified_by	UUID FK → odp_users	Yes
modified_on	timestamp	Yes	now()
charset	string(50)	Yes		Character encoding
filesize	bigint	Yes		File size in bytes
width	integer	Yes		Image width
height	integer	Yes		Image height
duration	integer	Yes		Media duration (seconds)
embed	string(200)	Yes		Embed provider
description	text	Yes
location	string(200)	Yes		Geographic location
tags	text (JSON)	Yes		Tag array
metadata	text (JSON)	Yes		EXIF/XMP metadata
focal_point_x	integer	Yes
focal_point_y	integer	Yes
tus_id	string(255)	Yes		TUS upload ID
tus_data	text (JSON)	Yes		TUS upload metadata

---

Content

odp_versions

Content version snapshots.

Column	Type	Nullable	Default	Description
id	UUID PK	No
key	string(64)	No		Human-readable slug
name	string(255)	Yes		Display name
collection	string(255) FK → odp_collections	No
item	string(255)	No		Item primary key
hash	string(255)	Yes		Content hash
date_created	timestamp	Yes	now()
date_updated	timestamp	Yes
user_created	UUID FK → odp_users	Yes
user_updated	UUID FK → odp_users	Yes
delta	text (JSON)	Yes		Changed fields

---

odp_activity

Audit log of all system actions.

Column	Type	Nullable	Default	Description
id	integer PK (auto)	No
action	string(45)	No		Action type
user	UUID FK → odp_users	Yes		Acting user
timestamp	timestamp	Yes	now()
ip	string(50)	Yes
user_agent	text	Yes
collection	string(255)	Yes		Affected collection
item	string(255)	Yes		Affected item PK
origin	string(255)	Yes		Request origin
impersonated_by	UUID	Yes		Real admin when impersonating

---

odp_revisions

Point-in-time data snapshots linked to activity records.

Column	Type	Nullable	Default	Description
id	integer PK (auto)	No
activity	integer FK → odp_activity	Yes		(CASCADE delete)
collection	string(255)	No
item	string(255)	No
data	text (JSON)	Yes		Full item snapshot after change
delta	text (JSON)	Yes		Only changed fields
parent	integer FK → odp_revisions	Yes		Parent revision
version	UUID FK → odp_versions	Yes		Linked version

---

odp_comments

Item comments.

Column	Type	Nullable	Default	Description
id	UUID PK	No
collection	string(255)	No
item	string(255)	No
comment	text	No		Markdown comment text
date_created	timestamp	Yes	now()
date_updated	timestamp	Yes
user_created	UUID FK → odp_users	Yes
user_updated	UUID FK → odp_users	Yes

---

System Config

odp_settings

Global project settings (singleton — always row id=1).

Column	Type	Nullable	Default	Description
id	integer PK	No		Always 1
project_name	string(255)	No	CMS
project_url	string(255)	Yes
project_color	string(10)	Yes		Hex color
project_logo	UUID FK → odp_files	Yes
public_foreground	UUID FK → odp_files	Yes
public_background	UUID FK → odp_files	Yes
public_note	text	Yes		Login page note
auth_login_attempts	integer	Yes	25	Max failed attempts before lockout
auth_password_policy	string(100)	Yes		Password policy descriptor
storage_asset_transform	string(30)	No	all	all, none, presets
storage_asset_presets	text (JSON)	Yes		Named transform presets
custom_css	text	Yes		CSS injected into admin
storage_default_folder	UUID FK → odp_folders	Yes
report_error_url	string(255)	Yes
report_bug_url	string(255)	Yes
report_feature_url	string(255)	Yes
public_registration	boolean	No	false
public_registration_verify_email	boolean	No	true
public_registration_role	UUID FK → odp_roles	Yes
public_registration_email_filter	text (JSON)	Yes		Allowed email domains
public_role	UUID FK → odp_roles	Yes		Role for unauthenticated access

---

odp_presets

Saved collection view configurations.

Column	Type	Nullable	Default	Description
id	integer PK (auto)	No
bookmark	string(255)	Yes		Saved bookmark name
user	UUID FK → odp_users	Yes		Per-user preset (CASCADE delete)
role	UUID FK → odp_roles	Yes		Per-role preset (CASCADE delete)
collection	string(255)	Yes
search	string(255)	Yes		Saved search
layout	string(100)	Yes	tabular
layout_query	text (JSON)	Yes
layout_options	text (JSON)	Yes
refresh_interval	integer	Yes		Auto-refresh seconds
filter	text (JSON)	Yes		Filter rules
icon	string(30)	No	bookmark
color	string(10)	Yes

---

odp_translations

Custom string translations.

Column	Type	Nullable	Default	Description
id	UUID PK	No
language	string(10)	No		Language code (e.g., en-US)
key	string(255)	No		Translation key
value	text	No		Translated string

---

odp_extensions

Registered extensions.

Column	Type	Nullable	Default	Description
id	UUID PK	No
enabled	boolean	No	true	Whether extension is loaded
folder	string(255)	No		Directory name under EXTENSIONS_PATH
source	string(255)	No		local, registry, or module
bundle	UUID FK → odp_extensions	Yes		Parent bundle

---

odp_extension_settings

Key-value settings store for extensions and system features.

Column	Type	Nullable	Default	Description
id	integer PK (auto)	No
extension_key	string	No		Namespace (e.g., system_auth_provider)
name	string	No		Setting name within namespace
value	text/JSON	Yes		Configuration value

Migration 053 added an index on extension_key for fast lookups.

---

Notifications

odp_notifications

In-app notifications sent to users.

Column	Type	Nullable	Default	Description
id	integer PK (auto)	No
timestamp	timestamp	Yes	now()
status	string(25)	No	inbox	inbox or archived
recipient	UUID FK → odp_users	No		(CASCADE delete)
sender	UUID FK → odp_users	Yes
subject	string(255)	No
message	text	Yes
collection	string(255)	Yes		Related collection
item	string(255)	Yes		Related item PK

---

Sharing

odp_shares

Public/password-protected content shares.

Column	Type	Nullable	Default	Description
id	UUID PK	No
name	string(255)	Yes		Display name
collection	string(255)	No
item	string(255)	No
role	UUID FK → odp_roles	Yes		Role applied to share sessions
password	string(255)	Yes		Hashed password
user_created	UUID FK → odp_users	Yes
date_created	timestamp	Yes	now()
date_start	timestamp	Yes		Share validity start
date_end	timestamp	Yes		Share validity end
times_used	integer	No	0	Usage counter
max_uses	integer	Yes		Maximum uses limit

---

App Permissions

odp_app_permissions

Feature-level permission grants.

Column	Type	Nullable	Default	Description
id	UUID PK	No
policy	UUID FK → odp_policies	No		(CASCADE delete)
module	text	No		Module ID (e.g., mcp, workflow)
action	text	No		Action ID or *
collection_scope	text	No	__global__	Collection name or __global__
created_at	timestamp	No	now()
created_by	UUID FK → odp_users	Yes

Unique constraint: (policy, module, action, collection_scope)

---

odp_app_access_logs

App permission access audit log.

Column	Type	Nullable	Default	Description
id	UUID PK	No
user_id	text	Yes
initiated_by	text	Yes		Real actor (differs from user_id during impersonation)
module	text	No
action	text	No
collection_scope	text	Yes
result	text	No		pass or fail
policy_id	text	Yes		Matched policy (on pass)
fail_reason	text	Yes		Reason for denial (on fail)
ip	text	Yes
user_agent	text	Yes
created_at	timestamp	No	now()

---

Workflow

odp_workflows

Workflow definitions.

Column	Type	Nullable	Default	Description
id	string PK	No		UUID
name	string	No
description	text	Yes
collection	string	Yes		Target collection
status	string	No	draft	draft, active, archived
trigger_event	string	No	manual	items.create, items.update, manual
trigger_filter	json	Yes		Row filter for auto-trigger
options	json	Yes
created_by	string	Yes
created_at	timestamp	Yes	now()
updated_at	timestamp	Yes	now()

---

odp_workflow_steps

Individual steps in a workflow.

Column	Type	Nullable	Default	Description
id	string PK	No		UUID
workflow_id	string FK → odp_workflows	No		(CASCADE delete)
key	string	No		Unique key within workflow
name	string	No		Display name
type	string	No	approval	start, approval, condition, action, notification, end
assign_type	string	Yes		role, user, field, creator, auto
assign_value	string	Yes		Assignment value
approval_mode	string	Yes	any	any, all, majority, count
approval_count	integer	Yes		Required approvals for count mode
timeout_minutes	integer	Yes		Step timeout
timeout_action	string	Yes		escalate, auto_approve, auto_reject, notify
escalate_to	string	Yes		Escalation target
action_type	string	Yes		For action steps
action_config	json	Yes		Action configuration
position_x, position_y	float	Yes	0	Visual position
sort_order	integer	Yes	0
options	json	Yes

---

odp_workflow_transitions

Transitions between workflow steps.

Column	Type	Nullable	Default	Description
id	string PK	No		UUID
workflow_id	string FK → odp_workflows	No		(CASCADE delete)
from_step_id	string FK → odp_workflow_steps	No		(CASCADE delete)
to_step_id	string FK → odp_workflow_steps	No		(CASCADE delete)
trigger	string	No	approve	approve, reject, timeout, condition, always
condition	json	Yes		Filter condition for condition trigger
sort_order	integer	Yes	0
label	string	Yes		Display label

---

odp_workflow_instances

Active/completed workflow runs.

Column	Type	Nullable	Default	Description
id	string PK	No		UUID
workflow_id	string FK → odp_workflows	No		(RESTRICT delete)
collection	string	No		Collection of the item
item_id	string	No		Item primary key
status	string	No	running	running, completed, rejected, cancelled, error
current_step_id	string FK → odp_workflow_steps	Yes		(SET NULL)
started_by	string	Yes
started_at	timestamp	Yes	now()
completed_at	timestamp	Yes
options	json	Yes

---

odp_workflow_instance_steps

Per-step state within a running workflow instance.

Column	Type	Nullable	Default	Description
id	string PK	No
instance_id	string FK → odp_workflow_instances	No		(CASCADE delete)
step_id	string FK → odp_workflow_steps	No		(RESTRICT)
status	string	No	pending	pending, active, approved, rejected, skipped, timed_out
assigned_users	json	Yes		Array of user IDs
approvals	json	Yes		Array of approval records
activated_at	timestamp	Yes
completed_at	timestamp	Yes
timeout_at	timestamp	Yes
result	string	Yes

---

odp_workflow_actions

Workflow audit log.

Column	Type	Nullable	Default	Description
id	string PK	No
instance_id	string FK → odp_workflow_instances	No		(CASCADE delete)
step_id	string FK → odp_workflow_steps	Yes		(SET NULL)
user_id	string	Yes
action	string	No		start, approve, reject, delegate, comment, escalate, cancel, auto_approve, timeout
comment	text	Yes
data	json	Yes		Additional action data
created_at	timestamp	Yes	now()

---

odp_workflow_instance_step_users

Junction: users assigned to a workflow instance step.

Column	Type	Description
(created by migration 040)		Additional junction for step user assignments

---

Notify System

odp_notify_settings (singleton)

Notification system settings.

odp_notify_templates

Email/notification template definitions.

odp_notify_template_contents

Template content for each channel (email, in-app).

odp_notify_logs

Notification send log (read-only).

odp_notify_layouts

Email layout templates (wraps template content).

---

Migration History

Migration	Description
001	Create odp_collections
002	Create odp_fields
003	Create odp_relations
004	Create odp_roles
005	Create odp_policies
006	Create odp_folders
007	Create odp_files
008	Create odp_users
009	Create odp_access
010	Create odp_permissions
011	Create odp_sessions
012	Create odp_activity
013	Create odp_revisions
014	Create odp_versions
015	Create odp_comments
016	Create odp_settings
017	Create odp_presets
018	Create odp_extensions
019	Create odp_notifications
020	Create odp_translations
021	Create odp_shares
022	Create odp_migrations
023	Create indexes
024	Seed default roles/policies
025	Add roles shortname/weight
026	Add password policy to settings
027	Add module bar to settings
028	Create notify system tables
029	Seed default notification templates
030	Add email transport settings
031	Add tech_access to odp_roles
032	Add session device/token fields
033	Add MCP settings
034	Create odp_user_providers (replaces legacy provider columns on odp_users)
035	Add provider_email to odp_user_providers
036	Add collection translations
037	Create workflow tables (odp_workflows, steps, transitions, instances, instance_steps, actions)
038	Add workflow notify templates
039	Create odp_ephemeral_codes
040	Create odp_workflow_instance_step_users junction
041	Add extra MCP settings fields
042	Add impersonate_access to odp_roles
043	Create odp_impersonation_sessions
044	Add impersonated_by to odp_activity
045	Add type and group_name to odp_fields
046	Create odp_app_permissions + odp_app_access_logs
047	Seed default app permissions
048	Create odp_notify_layouts
049	Create odp_user_tokens
050	Add theme settings to odp_settings
051	Create odp_extension_settings
052	Create Public role + add public_role to odp_settings
053	Add extension_key index to odp_extension_settings
054	Add sort to collections
055	Add is_system flag to odp_notify_templates