Database Schema¶

This document describes the database tables used by Poweradmin and their relationships. Poweradmin extends the PowerDNS database schema with additional tables for user management, permissions, logging, and enhanced functionality.

Database Requirements¶

Poweradmin requires both PowerDNS and Poweradmin database schemas to be installed:

PowerDNS Schema: Core DNS functionality (zones, records, cryptokeys)
Poweradmin Schema: User management, permissions, logging, and enhanced features

PowerDNS Tables¶

These tables are created by the PowerDNS schema and store the core DNS data:

`domains`¶

Stores DNS zones/domains.

Column	Type	Description
`id`	int	Primary key
`name`	varchar(255)	Domain name
`master`	varchar(128)	Master server for slave zones
`last_check`	int	Last check timestamp
`type`	varchar(8)	Zone type (MASTER, SLAVE, NATIVE, and PRODUCER/CONSUMER on PowerDNS 4.7+; widened from `varchar(6)` in 4.7 to fit them)
`notified_serial`	int	Last notified serial
`account`	varchar(40)	Account identifier
`options`	text	Zone options
`catalog`	varchar(255)	Catalog zone

`records`¶

Stores DNS records within zones.

Column	Type	Description
`id`	bigint	Primary key
`domain_id`	int	Foreign key to domains.id
`name`	varchar(255)	Record name
`type`	varchar(10)	Record type (A, AAAA, CNAME, etc.)
`content`	varchar(64000) on MySQL, varchar(65535) on PostgreSQL/SQLite	Record content/value
`ttl`	int	Time to live
`prio`	int	Priority (for MX, SRV records)
`disabled`	tinyint	Record disabled flag
`ordername`	varchar(255)	DNSSEC ordering
`auth`	tinyint	Authoritative flag

`supermasters`¶

Stores supermaster configuration for automatic slave zone creation.

Column	Type	Description
`ip`	varchar(64)	Supermaster IP address
`nameserver`	varchar(255)	Nameserver hostname
`account`	varchar(40)	Account identifier

`comments`¶

Stores comments for DNS records.

Column	Type	Description
`id`	int	Primary key
`domain_id`	int	Foreign key to domains.id
`name`	varchar(255)	Record name
`type`	varchar(10)	Record type
`modified_at`	int	Modification timestamp
`account`	varchar(40)	Account identifier
`comment`	text	Comment text

`domainmetadata`¶

Stores domain metadata (DNSSEC settings, etc.).

Column	Type	Description
`id`	int	Primary key
`domain_id`	int	Foreign key to domains.id
`kind`	varchar(32)	Metadata type
`content`	text	Metadata content

`cryptokeys`¶

Stores DNSSEC cryptographic keys.

Column	Type	Description
`id`	int	Primary key
`domain_id`	int	Foreign key to domains.id
`flags`	int	Key flags
`active`	tinyint	Key active status
`published`	tinyint	Key published status
`content`	text	Key content

`tsigkeys`¶

Stores TSIG keys for secure zone transfers.

Column	Type	Description
`id`	int	Primary key
`name`	varchar(255)	Key name
`algorithm`	varchar(50)	Algorithm
`secret`	varchar(255)	Secret key

Poweradmin Core Tables¶

`users`¶

Stores user accounts and authentication information.

Column	Type	Description
`id`	int	Primary key
`username`	varchar(64)	Username
`password`	varchar(128)	Encrypted password
`fullname`	varchar(255)	Full name
`email`	varchar(255)	Email address
`description`	varchar(1024)	User description
`perm_templ`	int	Permission template ID (FK to `perm_templ.id`)
`perm_templ_source`	varchar(20)	How `perm_templ` was assigned: `admin` (default; set by an administrator) or `sso` (assigned by OIDC/SAML provisioning)
`active`	int	Active status
`use_ldap`	int	Use LDAP authentication
`auth_method`	varchar(20)	Authentication method (sql, ldap, oidc, saml)

`perm_items`¶

Defines available permissions in the system.

Column	Type	Description
`id`	int	Primary key
`name`	varchar(64)	Permission name
`descr`	varchar(1024)	Permission description

`perm_templ`¶

Defines permission templates (roles).

Column	Type	Description
`id`	int	Primary key
`name`	varchar(128)	Template name
`descr`	varchar(1024)	Template description

`perm_templ_items`¶

Links permission templates to specific permissions.

Column	Type	Description
`id`	int	Primary key
`templ_id`	int	Foreign key to perm_templ.id
`perm_id`	int	Foreign key to perm_items.id

`zones`¶

Links zones to users for permission management. In API backend mode, also caches zone metadata from the PowerDNS API.

Column	Type	Description
`id`	int	Primary key
`domain_id`	int	Foreign key to domains.id (nullable in API mode)
`owner`	int	Foreign key to users.id
`comment`	varchar(1024)	Zone comment
`zone_templ_id`	int	Zone template ID
`zone_name`	varchar(255)	Zone name (cached from PowerDNS API, v4.3.0+)
`zone_type`	varchar(8)	Zone type: Master, Slave, Native (cached, v4.3.0+)
`zone_master`	varchar(255)	Master server for slave zones (cached, v4.3.0+)

`zone_templ`¶

Defines zone templates for bulk zone creation.

Column	Type	Description
`id`	int	Primary key
`name`	varchar(128)	Template name
`descr`	varchar(1024)	Template description
`owner`	int	Foreign key to users.id
`created_by`	int	Foreign key to users.id (creator)

`zone_templ_records`¶

Stores record templates within zone templates.

Column	Type	Description
`id`	int	Primary key
`zone_templ_id`	int	Foreign key to zone_templ.id
`name`	varchar(255)	Record name template
`type`	varchar(10)	Record type (widened from varchar(6) in 4.5.0)
`content`	varchar(2048)	Record content template
`ttl`	int	Time to live
`prio`	int	Priority

`records_zone_templ`¶

Links records to zone templates.

Column	Type	Description
`id`	int	Primary key
`domain_id`	int	Foreign key to domains.id
`record_id`	int	Foreign key to records.id
`zone_templ_id`	int	Foreign key to zone_templ.id

Security Tables¶

`login_attempts`¶

Tracks login attempts for security purposes.

Column	Type	Description
`id`	int	Primary key
`user_id`	int	Foreign key to users.id (nullable)
`ip_address`	varchar(45)	IP address
`timestamp`	int	Attempt timestamp (Unix)
`successful`	tinyint	Success flag

`api_keys`¶

Stores API keys for authentication.

Column	Type	Description
`id`	int	Primary key
`name`	varchar(255)	Key name
`secret_key`	varchar(255)	Hashed API key
`created_by`	int	Foreign key to users.id
`created_at`	timestamp	Creation timestamp
`last_used_at`	timestamp	Last usage timestamp
`disabled`	tinyint	Disabled flag
`expires_at`	timestamp	Expiration timestamp
`is_readonly`	tinyint	Restricts the key to read-only operations
`allowed_operations`	varchar(255)	Comma-separated operations the key may perform (null means no restriction)

`api_key_zones`¶

Restricts an API key to a set of zones. A key with no rows here is unrestricted and can reach every zone its creator may access.

Column	Type	Description
`id`	int	Primary key
`api_key_id`	int	Foreign key to `api_keys.id` (cascades on delete)
`zone_id`	int	Zone the key is allowed to reach

`user_mfa`¶

Stores multi-factor authentication settings.

Column	Type	Description
`id`	int	Primary key
`user_id`	int	Foreign key to users.id
`enabled`	tinyint	MFA enabled flag
`secret`	varchar(255)	MFA secret
`recovery_codes`	text	Recovery codes
`type`	varchar(20)	MFA type (app, email)
`last_used_at`	datetime	Last usage timestamp
`created_at`	datetime	Creation timestamp
`updated_at`	datetime	Update timestamp
`verification_data`	text	Verification data

`password_reset_tokens`¶

Stores password reset tokens.

Column	Type	Description
`id`	int	Primary key
`email`	varchar(255)	User email
`token`	varchar(64)	Reset token
`expires_at`	timestamp	Expiration timestamp
`created_at`	timestamp	Creation timestamp
`used`	tinyint	Token used flag
`ip_address`	varchar(45)	Request IP address

`username_recovery_requests`¶

Tracks username recovery requests for rate limiting.

Column	Type	Description
`id`	int	Primary key
`email`	varchar(255)	Email address
`ip_address`	varchar(45)	Request IP address
`created_at`	timestamp	Request timestamp

Authentication Provider Tables¶

`oidc_user_links`¶

Links users to OpenID Connect providers.

Column	Type	Description
`id`	int	Primary key
`user_id`	int	Foreign key to users.id
`provider_id`	varchar(50)	OIDC provider identifier
`oidc_subject`	varchar(255)	OIDC subject claim
`username`	varchar(255)	Username from provider
`email`	varchar(255)	Email from provider
`created_at`	timestamp	Creation timestamp
`updated_at`	timestamp	Update timestamp

`saml_user_links`¶

Links users to SAML providers.

Column	Type	Description
`id`	int	Primary key
`user_id`	int	Foreign key to users.id
`provider_id`	varchar(50)	SAML provider identifier
`saml_subject`	varchar(255)	SAML NameID
`username`	varchar(255)	Username from provider
`email`	varchar(255)	Email from provider
`created_at`	timestamp	Creation timestamp
`updated_at`	timestamp	Update timestamp

User Data Tables¶

`user_preferences`¶

Stores user preferences and settings.

Column	Type	Description
`id`	int	Primary key
`user_id`	int	Foreign key to users.id
`preference_key`	varchar(100)	Preference key
`preference_value`	text	Preference value

`user_agreements`¶

Tracks user agreement acceptance.

Column	Type	Description
`id`	int	Primary key
`user_id`	int	Foreign key to users.id
`agreement_version`	varchar(50)	Agreement version
`accepted_at`	timestamp	Acceptance timestamp
`ip_address`	varchar(45)	IP address
`user_agent`	text	Browser user agent

Zone Management Tables¶

`zone_template_sync`¶

Tracks zone template synchronization.

Column	Type	Description
`id`	int	Primary key
`zone_id`	int	Foreign key to zones.id
`zone_templ_id`	int	Foreign key to zone_templ.id
`last_synced`	timestamp	Last sync timestamp
`template_last_modified`	timestamp	Template modification time
`needs_sync`	tinyint	Sync needed flag
`created_at`	timestamp	Creation timestamp
`updated_at`	timestamp	Update timestamp

Logging Tables¶

`log_users`¶

Logs user-related activities.

Column	Type	Description
`id`	int	Primary key
`event`	varchar(2048)	Event description
`created_at`	timestamp	Event timestamp
`priority`	int	Log priority level

`log_zones`¶

Logs zone-related activities.

Column	Type	Description
`id`	int	Primary key
`event`	varchar(2048)	Event description
`created_at`	timestamp	Event timestamp
`priority`	int	Log priority level
`zone_id`	int	Related zone ID

`log_api`¶

Logs API requests handled by the public API.

Column	Type	Description
`id`	int	Primary key
`event`	varchar(2048)	Event description
`created_at`	timestamp	Event timestamp
`priority`	int	Log priority level

`log_groups`¶

Logs user-group lifecycle events (create / rename / membership changes).

Column	Type	Description
`id`	int	Primary key
`event`	varchar(2048)	Event description
`created_at`	timestamp	Event timestamp
`priority`	int	Log priority level
`group_id`	int	Related `user_groups.id` (nullable)

`log_changesets`¶

Groups the record changes made in a single edit, so the change log can present them as one entry with a shared comment.

Column	Type	Description
`id`	int	Primary key
`zone_id`	int	Related zone (nullable)
`user_id`	int	Acting user (nullable)
`username`	varchar(64)	Acting username (denormalized so the log survives user deletion)
`comment`	text	Optional reason supplied with the change
`client_ip`	varchar(64)	Source IP
`created_at`	timestamp	Event timestamp

`log_record_changes`¶

Structured before/after snapshots for record mutations. Powers the diff-style change-log UI and email digests. Complements log_zones (which is a free-text activity feed) with machine-readable detail.

Column	Type	Description
`id`	int	Primary key
`zone_id`	int	Related zone (nullable)
`changeset_id`	int	Related `log_changesets.id` (nullable)
`record_id`	text	Record identifier (numeric for SQL mode, encoded for API mode)
`action`	varchar(32)	`create`, `update`, `delete`
`user_id`	int	Acting user (nullable)
`username`	varchar(64)	Acting username (denormalized so log survives user deletion)
`before_state`	text	JSON snapshot prior to mutation (null for creates)
`after_state`	text	JSON snapshot after mutation (null for deletes)
`client_ip`	varchar(64)	Source IP
`created_at`	timestamp	Event timestamp

Group Tables¶

`user_groups`¶

Named groups that bundle a permission template and a set of members. Underpins group-based ownership of zones.

Column	Type	Description
`id`	int unsigned	Primary key
`name`	varchar(255)	Group name (unique)
`description`	text	Free-form description
`perm_templ`	int	FK to `perm_templ.id` - the permission template applied to members
`created_by`	int	FK to `users.id` (nullable; `SET NULL` on user deletion)
`created_at`	timestamp	Creation timestamp
`updated_at`	timestamp	Last update timestamp

`user_group_members`¶

Membership join table between users and groups.

Column	Type	Description
`id`	int unsigned	Primary key
`group_id`	int unsigned	FK to `user_groups.id` (cascade delete)
`user_id`	int	FK to `users.id` (cascade delete)
`created_at`	timestamp	Membership added at

`zones_groups`¶

Maps zones to groups for group-based ownership. A zone can be owned directly via zones.owner and/or via one or more rows in this table.

Column	Type	Description
`id`	int unsigned	Primary key
`domain_id`	int	Zone identifier (matches PowerDNS `domains.id`)
`group_id`	int unsigned	FK to `user_groups.id` (cascade delete)
`created_at`	timestamp	Assignment timestamp

Defaults and Application State¶

`record_type_defaults`¶

Per-record-type default TTL values used when adding new records via the UI.

Column	Type	Description
`record_type`	varchar(20)	DNS record type (e.g. `A`, `AAAA`, `MX`); primary key
`ttl`	int	Default TTL in seconds
`created_at`	timestamp	Created at
`updated_at`	timestamp	Updated at

`app_settings`¶

Key/value store for settings that can be changed at runtime from the admin UI (without editing config/settings.php).

Column	Type	Description
`setting_key`	varchar(128)	Setting identifier (primary key)
`setting_value`	text	Stored value (always serialized to string)
`value_type`	varchar(16)	One of `string`, `int`, `bool`, `array` - tells callers how to deserialize `setting_value`
`created_at`	timestamp	Created at
`updated_at`	timestamp	Updated at

`records_zone_templ_api`¶

API-mode equivalent of records_zone_templ. Tracks which template a record was created from when Poweradmin is talking to PowerDNS via the API (record identifiers are opaque strings, not numeric IDs).

Column	Type	Description
`id`	int	Primary key
`domain_id`	int	Zone identifier
`record_id`	varchar(255)	API-mode record identifier
`zone_templ_id`	int	FK to `zone_templ.id`

Record Comment Tables¶

`record_comment_links`¶

Bridge table that maps a record identifier to a PowerDNS comments.id. Record-level comment text lives in PowerDNS's own comments table; Poweradmin only stores the link so that comments survive record edits in the UI / API.

Column	Type	Description
`record_id`	VARCHAR(3072) on MySQL / VARCHAR(4096) on PostgreSQL & SQLite	Record identifier (numeric `records.id` in SQL mode, encoded string in API mode). Primary key.
`comment_id`	int	Reference to PowerDNS `comments.id`. Unique.

Default Permission Templates¶

Poweradmin seeds five user templates (template_type = 'user', ids 1-5):

Template	Description
Administrator	Full access (user_is_ueberuser)
Zone Manager	Full management of own zones including creation, editing, deletion, and templates
Editor	Edit own zone records but cannot modify SOA and NS records
Viewer	Read-only access to own zones with search capability
Guest	Temporary access with no permissions

Since 4.2.0 it also seeds five matching group templates (template_type = 'group', ids 6-10): Administrators, Zone Managers, Editors, Viewers, and Guests.

Table Relationships¶

Key Relationships¶

Direct zone ownership: zones.domain_id → domains.id; zones.owner → users.id
Group zone ownership: zones_groups.domain_id → domains.id; zones_groups.group_id → user_groups.id
Group membership: user_group_members.group_id → user_groups.id; user_group_members.user_id → users.id
DNS records: records.domain_id → domains.id
User permissions: users.perm_templ → perm_templ.id; user_groups.perm_templ → perm_templ.id
Permission templates: perm_templ_items.templ_id → perm_templ.id; perm_templ_items.perm_id → perm_items.id
Zone templates: zone_templ_records.zone_templ_id → zone_templ.id; records_zone_templ.zone_templ_id → zone_templ.id (SQL mode); records_zone_templ_api.zone_templ_id → zone_templ.id (API mode)
API keys: api_keys.created_by → users.id; api_key_zones.api_key_id → api_keys.id
MFA: user_mfa.user_id → users.id
User preferences: user_preferences.user_id → users.id
OIDC / SAML links: oidc_user_links.user_id → users.id; saml_user_links.user_id → users.id
Comment bridge: record_comment_links.comment_id → PowerDNS comments.id

Version History¶

Version	Changes
4.5.0 (develop)	Added `log_changesets` and `log_record_changes` (grouped, structured before/after record snapshots), `records_zone_templ_api` (template tracking in API mode), `record_type_defaults` (per-type default TTLs), `app_settings` (admin-managed runtime key/value store), and `api_key_zones` (per-zone API key scoping); added `is_readonly` and `allowed_operations` to `api_keys`. `zones.zone_templ_id` now defaults to `0`. Widened `zone_templ_records.type` from varchar(6) to varchar(10) so longer record types such as `NSEC3PARAM` and `OPENPGPKEY` can be stored in zone templates.
4.4.0 (master)	Added `is_default` to `zone_templ` for marking a system-wide default template.
4.3.0	Added `log_api` and migrated API key log entries into it; added `zone_name`, `zone_type`, `zone_master` to `zones`; widened `record_comment_links.record_id` to VARCHAR.
4.2.0	Introduced group-based ownership (`user_groups`, `user_group_members`, `zones_groups`, `log_groups`); added `template_type` to `perm_templ`; made `zones.owner` nullable. Introduced `record_comment_links`. Renamed default permission templates (DNS Editor → Editor, Read Only → Viewer, No Access → Guest).
4.1.0	Added `oidc_user_links`, `saml_user_links`, `username_recovery_requests`; added `auth_method` to `users` (with backfill from `use_ldap`); performance indexes added.
4.0.0	Major rework: added `login_attempts`, `api_keys`, `user_mfa`, `user_preferences`, `zone_template_sync`, `password_reset_tokens`, `user_agreements`.

Backup Considerations¶

When backing up Poweradmin, ensure you backup:

PowerDNS Tables: Contains your DNS data
Poweradmin Tables: Contains users, permissions, and configuration
Configuration Files: config/settings.php and related files

For more information about database setup and migration, see:

Database Schema¶

Database Requirements¶

PowerDNS Tables¶

domains¶

records¶

supermasters¶

comments¶

domainmetadata¶

cryptokeys¶

tsigkeys¶

Poweradmin Core Tables¶

users¶

perm_items¶

perm_templ¶

perm_templ_items¶

zones¶

zone_templ¶

zone_templ_records¶

records_zone_templ¶

Security Tables¶

login_attempts¶

api_keys¶

api_key_zones¶

user_mfa¶

password_reset_tokens¶

username_recovery_requests¶