pal-e-docs

Entity-Page Architecture

entity-page-architecture Doc

architecture active
Project

Entity-Page Architecture

Core Principle

Pages are for reading. Databases are for properties.
Every entity in pal-e-docs (project, repo, issue) has two aspects:
  • Structured properties — queryable columns in an entity table (name, slug, status, platform, is_public). These are what the database searches, filters, and joins on.
  • Rich content — HTML rendered for humans (architecture diagrams, roadmaps, prose descriptions). This lives in a note, which also provides revision tracking, tags, and links.
A foreign key (page_note_id) connects the entity to its page note. One query returns both.

What is a Foreign Key?

A foreign key (FK) is a column in one table that references the primary key (ID) of another table. It tells the database: "this value points to a row in that other table."
Example with pal-e-docs data:
page_note_id = 24 means "this project's page is note 24." The database enforces that note 24 exists. The number 24 is called "foreign" because it belongs to the notes table, not the projects table — it's a reference to a foreign table's primary key.

What the FK Enforces

1. Can't point to nothing

Without a FK, this would silently succeed and the project would reference a ghost.

2. Can't accidentally delete a page

With ON DELETE RESTRICT:
Without a FK, the note just disappears. The project's page is gone and nothing warned you.

3. Can't share pages within a table

With a UNIQUE constraint on page_note_id:

4. One-query joins

5. NULL means no page

Why Not Polymorphic Associations?

When a note can be the page for a project OR a repo OR an issue, there are alternative designs. Both lose referential integrity.

Polymorphic reverse relationship (Option 2)

Put owner_type + owner_id on the notes table:
Problem: owner_id = 3 could mean project 3, repo 3, or issue 3. The database cannot create a FK that points to multiple tables conditionally. So owner_id is just an integer with no enforcement. You can set owner_id = 999 even if project 999 doesn't exist. You've lost the whole point of using a database — enforcement without human discipline.
This pattern is common in Rails and Django but it sacrifices referential integrity for convenience.

Join table (Option 3)

The note_id FK is real. But entity_id has the same polymorphic problem — it can't be a real FK. Extra table, extra joins, same integrity gap on the entity side.

FK on entity tables (Option 4 — what pal-e-docs uses)

The FK from entity → note is real and enforced. The database guarantees the note exists. ON DELETE RESTRICT prevents deletion. UNIQUE prevents sharing within a table.
One gap: A project and a repo could both point to the same note (cross-table). The database can't prevent this because UNIQUE is per-table. In practice, slug conventions (project-* vs repo-*) make accidental collisions nearly impossible, and ON DELETE RESTRICT would surface any conflict quickly.

Comparison

Entity→Note FK enforced? Note→Entity FK enforced? Cross-table uniqueness?
Option 2 (polymorphic) No N/A Yes (natural)
Option 3 (join table) No Yes Yes (UNIQUE)
<strong>Option 4 (FK on entity)</strong> <strong>Yes</strong> No No (per-table only)
Option 4 wins because the entity→note direction is the relationship you query most: "give me this project and its page." That FK being real and enforced matters more than the unlikely edge case of cross-table collision.

Schema Pattern

Entity tables hold structured queryable properties. Notes hold rich HTML content with revision tracking, tags, and links. The page_note_id FK connects them. One query gets everything.

Before and After

Before (slug convention):
After (FK join):
  • plan-2026-02-26-schema-entity-links — the plan implementing this architecture
  • project-pal-e-docs — the project this architecture serves