Database Schema
NotedWP stores all data in eight custom tables within your WordPress database. Every table is prefixed with your site’s $wpdb->prefix (typically wp_). Tables are created on plugin activation via dbDelta() and upgraded automatically when the plugin detects a newer NOTED_DB_VERSION.
All tables use the InnoDB engine with foreign key constraints. Cascading deletes ensure that removing a record cleans up all child records (pages, pins, comments, annotations, text edits, guests, exports).
Table Prefix
Throughout this document, tables are shown without the WordPress prefix. In practice, noted_projects means wp_noted_projects (or whatever your $table_prefix is set to in wp-config.php).
noted_projects
> Internal table. This table exists for internal use. A single default row is auto-created on plugin activation. Users do not interact with projects directly.
An internal record used by the overlay. A single default row is auto-created. It holds the share link, guest list, and notification settings for the site.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id |
bigint(20) unsigned |
No | Auto-increment | Primary key. |
name |
varchar(255) |
No | Project display name. | |
status |
varchar(20) |
No | 'active' |
active or archived. |
created_by |
bigint(20) unsigned |
Yes | NULL |
WordPress user ID of the creator. |
access_token |
varchar(64) |
Yes | NULL |
Unique token used in share links. |
password_hash |
varchar(255) |
Yes | NULL |
Bcrypt hash if password protection is enabled. |
expires_at |
datetime |
Yes | NULL |
Share link expiration timestamp. NULL means no expiry. |
toolbar_position |
varchar(20) |
Yes | 'bottom-right' |
Overlay toolbar position: bottom-right, bottom-left, top-right, top-left. |
brand_color |
varchar(7) |
Yes | '#E06042' |
Hex color used for the overlay UI. |
notify_on_new_pin |
tinyint(1) |
Yes | 1 |
Send email on new pin creation. |
notify_on_reply |
tinyint(1) |
Yes | 1 |
Send email on new comment. |
notification_emails |
text |
Yes | NULL |
Comma-separated list of notification recipients. |
created_at |
datetime |
No | CURRENT_TIMESTAMP |
Row creation time. |
updated_at |
datetime |
No | CURRENT_TIMESTAMP |
Auto-updated on modification. |
Indexes
| Name | Type | Columns |
|---|---|---|
PRIMARY |
Primary key | id |
uk_token |
Unique | access_token |
idx_status |
Index | status |
noted_pages
Tracks individual pages (by URL path) on the site. Created automatically when the overlay loads on a page for the first time.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id |
bigint(20) unsigned |
No | Auto-increment | Primary key. |
project_id |
bigint(20) unsigned |
No | FK to noted_projects.id. References the internal default project. |
|
url_path |
varchar(2048) |
No | Relative URL path (e.g., /about/). |
|
title |
varchar(255) |
Yes | NULL |
Human-readable page title. |
sort_order |
int(11) |
Yes | 0 |
Manual sort position in the admin. |
created_at |
datetime |
No | CURRENT_TIMESTAMP |
Row creation time. |
Indexes
| Name | Type | Columns |
|---|---|---|
PRIMARY |
Primary key | id |
uk_project_path |
Unique | project_id, url_path(191) |
Foreign Keys
| Column | References | On Delete |
|---|---|---|
project_id |
noted_projects.id |
CASCADE |
Notes. The unique key on (project_id, url_path) uses a prefix length of 191 characters for the url_path column. This is required for compatibility with utf8mb4 character sets on MySQL/MariaDB where the maximum index key length is 767 bytes.
noted_pins
Each pin represents a feedback point placed on a page. Pins track both their visual position (percentage-based coordinates) and an optional CSS selector for element-anchored positioning.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id |
bigint(20) unsigned |
No | Auto-increment | Primary key. |
page_id |
bigint(20) unsigned |
No | FK to noted_pages.id. |
|
project_id |
bigint(20) unsigned |
No | FK to noted_projects.id. References the internal default project. |
|
x_percent |
double |
No | Horizontal position as percentage (0-100). | |
y_percent |
double |
No | Vertical position as percentage (0-100). | |
css_selector |
text |
Yes | NULL |
CSS selector of the target element. |
selector_offset_x |
double |
Yes | NULL |
Horizontal offset within the selected element. |
selector_offset_y |
double |
Yes | NULL |
Vertical offset within the selected element. |
viewport_width |
int(11) |
Yes | NULL |
Browser viewport width when pin was placed. |
scroll_y |
double |
Yes | NULL |
Scroll position when pin was placed. |
breakpoint |
varchar(10) |
No | 'desktop' |
desktop, tablet, or mobile. |
pin_number |
int(10) unsigned |
No | Sequential number within the site. | |
status |
varchar(20) |
No | 'open' |
open or resolved. |
priority |
varchar(10) |
No | 'normal' |
low, normal, high, or critical. |
label |
varchar(20) |
Yes | NULL |
Custom label (e.g., design, copy, bug). |
is_internal |
tinyint(1) |
No | 0 |
1 = only visible to logged-in users. |
author_type |
varchar(10) |
No | user or guest. |
|
author_user_id |
bigint(20) unsigned |
Yes | NULL |
WordPress user ID (when author_type = 'user'). |
author_guest_id |
bigint(20) unsigned |
Yes | NULL |
Guest ID (when author_type = 'guest'). |
body |
text |
No | Initial comment body. | |
attachment_ids |
text |
Yes | NULL |
JSON array of WordPress media attachment IDs. |
resolved_at |
datetime |
Yes | NULL |
Timestamp when pin was resolved. |
resolved_by |
bigint(20) unsigned |
Yes | NULL |
User ID who resolved the pin. |
created_at |
datetime |
No | CURRENT_TIMESTAMP |
Row creation time. |
updated_at |
datetime |
No | CURRENT_TIMESTAMP |
Auto-updated on modification. |
Indexes
| Name | Type | Columns |
|---|---|---|
PRIMARY |
Primary key | id |
idx_page |
Index | page_id |
idx_project |
Index | project_id |
idx_status |
Index | status |
idx_breakpoint |
Index | breakpoint |
idx_page_status |
Composite index | page_id, status |
Foreign Keys
| Column | References | On Delete |
|---|---|---|
page_id |
noted_pages.id |
CASCADE |
project_id |
noted_projects.id |
CASCADE |
noted_comments
Threaded comments on pins. Supports one level of nesting via parent_id.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id |
bigint(20) unsigned |
No | Auto-increment | Primary key. |
pin_id |
bigint(20) unsigned |
No | FK to noted_pins.id. |
|
parent_id |
bigint(20) unsigned |
Yes | NULL |
FK to noted_comments.id for threaded replies. |
author_type |
varchar(10) |
No | user or guest. |
|
author_user_id |
bigint(20) unsigned |
Yes | NULL |
WordPress user ID. |
author_guest_id |
bigint(20) unsigned |
Yes | NULL |
Guest ID. |
body |
text |
No | Comment text. | |
attachment_ids |
text |
Yes | NULL |
JSON array of media attachment IDs. |
created_at |
datetime |
No | CURRENT_TIMESTAMP |
Row creation time. |
updated_at |
datetime |
No | CURRENT_TIMESTAMP |
Auto-updated on modification. |
Indexes
| Name | Type | Columns |
|---|---|---|
PRIMARY |
Primary key | id |
idx_pin |
Index | pin_id |
Foreign Keys
| Column | References | On Delete |
|---|---|---|
pin_id |
noted_pins.id |
CASCADE |
parent_id |
noted_comments.id |
CASCADE |
noted_annotations
Drawing annotations (rectangles, arrows, freehand strokes) layered on top of pages. Optionally linked to a pin.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id |
bigint(20) unsigned |
No | Auto-increment | Primary key. |
page_id |
bigint(20) unsigned |
No | FK to noted_pages.id. |
|
pin_id |
bigint(20) unsigned |
Yes | NULL |
FK to noted_pins.id. Set to NULL if pin is deleted. |
annotation_type |
varchar(20) |
No | rectangle, arrow, or freehand. |
|
breakpoint |
varchar(10) |
No | 'desktop' |
desktop, tablet, or mobile. |
data |
longtext |
No | JSON string with coordinates and shape data. | |
color |
varchar(7) |
No | '#E06042' |
Hex color of the annotation stroke. |
viewport_width |
int(11) |
Yes | NULL |
Viewport width when annotation was drawn. |
author_type |
varchar(10) |
No | user or guest. |
|
author_user_id |
bigint(20) unsigned |
Yes | NULL |
WordPress user ID. |
author_guest_id |
bigint(20) unsigned |
Yes | NULL |
Guest ID. |
created_at |
datetime |
No | CURRENT_TIMESTAMP |
Row creation time. |
Indexes
| Name | Type | Columns |
|---|---|---|
PRIMARY |
Primary key | id |
idx_page |
Index | page_id |
idx_pin |
Index | pin_id |
Foreign Keys
| Column | References | On Delete |
|---|---|---|
page_id |
noted_pages.id |
CASCADE |
pin_id |
noted_pins.id |
SET NULL |
noted_text_edits
Inline text change suggestions. Reviewers highlight text on the page and propose a replacement. Site owners can accept or reject each suggestion.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id |
bigint(20) unsigned |
No | Auto-increment | Primary key. |
page_id |
bigint(20) unsigned |
No | FK to noted_pages.id. |
|
pin_id |
bigint(20) unsigned |
Yes | NULL |
FK to noted_pins.id. Set to NULL if pin is deleted. |
css_selector |
text |
No | CSS selector of the element containing the text. | |
original_text |
text |
No | The current (before) text. | |
suggested_text |
text |
No | The proposed (after) text. | |
breakpoint |
varchar(10) |
No | 'desktop' |
desktop, tablet, or mobile. |
status |
varchar(20) |
No | 'pending' |
pending, accepted, or rejected. |
author_type |
varchar(10) |
No | user or guest. |
|
author_user_id |
bigint(20) unsigned |
Yes | NULL |
WordPress user ID. |
author_guest_id |
bigint(20) unsigned |
Yes | NULL |
Guest ID. |
created_at |
datetime |
No | CURRENT_TIMESTAMP |
Row creation time. |
updated_at |
datetime |
No | CURRENT_TIMESTAMP |
Auto-updated on modification. |
Indexes
| Name | Type | Columns |
|---|---|---|
PRIMARY |
Primary key | id |
idx_page |
Index | page_id |
idx_status |
Index | status |
Foreign Keys
| Column | References | On Delete |
|---|---|---|
page_id |
noted_pages.id |
CASCADE |
pin_id |
noted_pins.id |
SET NULL |
noted_guests
External reviewers who access the site via a share link. Each guest is scoped to the internal default project.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id |
bigint(20) unsigned |
No | Auto-increment | Primary key. |
project_id |
bigint(20) unsigned |
No | FK to noted_projects.id. References the internal default project. |
|
name |
varchar(255) |
No | Guest display name. | |
email |
varchar(255) |
Yes | NULL |
Guest email (optional). |
avatar_hash |
varchar(32) |
Yes | NULL |
MD5 hash of email for Gravatar lookup. |
token |
varchar(64) |
No | Authentication token sent via X-Noted-Guest-Token. |
|
last_seen_at |
datetime |
Yes | NULL |
Last presence heartbeat timestamp. |
created_at |
datetime |
No | CURRENT_TIMESTAMP |
Row creation time. |
Indexes
| Name | Type | Columns |
|---|---|---|
PRIMARY |
Primary key | id |
idx_project |
Index | project_id |
idx_token |
Index | token |
Foreign Keys
| Column | References | On Delete |
|---|---|---|
project_id |
noted_projects.id |
CASCADE |
noted_exports
Audit log of pin exports. Each row records which pins were exported, where they went, and whether the operation succeeded.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id |
bigint(20) unsigned |
No | Auto-increment | Primary key. |
project_id |
bigint(20) unsigned |
No | FK to noted_projects.id. References the internal default project. |
|
pin_ids |
text |
No | JSON array of exported pin IDs. | |
destination |
varchar(50) |
No | Export destination identifier (e.g., csv, trello, slack). |
|
destination_ref |
text |
Yes | NULL |
Reference to the created resource (URL, task ID, etc.). |
exported_by |
bigint(20) unsigned |
Yes | NULL |
WordPress user ID of the exporter. |
status |
varchar(20) |
No | 'success' |
success or failed. |
error_message |
text |
Yes | NULL |
Error details if the export failed. |
created_at |
datetime |
No | CURRENT_TIMESTAMP |
Row creation time. |
Indexes
| Name | Type | Columns |
|---|---|---|
PRIMARY |
Primary key | id |
idx_project |
Index | project_id |
Foreign Keys
| Column | References | On Delete |
|---|---|---|
project_id |
noted_projects.id |
CASCADE |
Entity Relationship Diagram
The following describes the relationships between all eight tables. The noted_projects table is internal and always contains a single default row.
noted_projects (1, internal default row)
|
|-- (many) noted_pages
| |
| |-- (many) noted_pins
| | |
| | |-- (many) noted_comments
| | | |
| | | +-- (self-ref) parent_id -> noted_comments.id
| | |
| | +-- (many) noted_annotations.pin_id (SET NULL on delete)
| | |
| | +-- (many) noted_text_edits.pin_id (SET NULL on delete)
| |
| |-- (many) noted_annotations.page_id
| |
| +-- (many) noted_text_edits.page_id
|
|-- (many) noted_guests
|
+-- (many) noted_exportsKey relationship details:
- Projects (internal). A single default row exists. All other tables reference it via
project_id. This row is auto-created on activation and should not be deleted. - Pages to Pins. One-to-many. Deleting a page cascades to all its pins.
- Pins to Comments. One-to-many. Deleting a pin cascades to all its comments.
- Comments to Comments. Self-referential via
parent_idfor threaded replies. Deleting a parent comment cascades to its replies. - Pins to Annotations. One-to-many (optional). Deleting a pin sets
annotation.pin_idto NULL (the annotation remains orphaned on the page). - Pins to Text Edits. One-to-many (optional). Deleting a pin sets
text_edit.pin_idto NULL. - Pages to Annotations. One-to-many. Deleting a page cascades to all its annotations.
- Pages to Text Edits. One-to-many. Deleting a page cascades to all its text edits.
- Guests. One-to-many from the internal project. Each guest belongs to the default project row.
- Exports. One-to-many from the internal project. Export audit records reference the default project row.
Querying with $wpdb
All tables are accessible through the standard $wpdb global. Always use the prefix:
global $wpdb;
$table = $wpdb->prefix . 'noted_pins';
$open_pins = $wpdb->get_results(
"SELECT * FROM {$table} WHERE status = 'open' ORDER BY created_at DESC"
);Table Creation
Tables are created via WordPress dbDelta() in Noted_Activator::create_tables(). Foreign key constraints are added in a separate step via ALTER TABLE statements. This two-step approach is necessary because dbDelta() does not support inline foreign key definitions.
On multisite installations with an Agency license, tables are created per-site (each blog gets its own set of eight tables). When a new site is added to the network, the wp_initialize_site hook triggers automatic table creation.