pal-e-docs

pal-e-docs Database Schema

doc-pal-e-docs-schema Doc

architecture active
Project

pal-e-docs Database Schema

Current database schema for pal-e-docs. 8 tables on SQLite (migrating to Postgres). Source of truth: src/pal_e_docs/models.py.

Entity-Relationship Diagram




          
    
Note: page_notes and linked_notes in the diagram are aliases for the notes table. Mermaid ERD does not support self-referential relationships or multiple relationships to the same entity, so aliases are used for visual clarity. The actual database has 8 tables, not 10.



          
  
Table Summary



          
  

    

        
          

              Table
              Rows (approx)
              Purpose
          

        
      
          

              <code>projects</code>
              11
              Top-level organizational unit. Has many notes and repos. Optional page_note_id FK to a note for rich content.
          

          

              <code>repos</code>
              24
              Code repositories. Belongs to a project. Has platform, url, status, role.
          

          

              <code>notes</code>
              ~162
              The core content unit. HTML fragments stored in html_content. Belongs to a project. Tagged via note_tags. Linked via note_links. Revision-tracked via note_revisions.
          

          

              <code>tags</code>
              ~47
              Topic/domain labels. Many-to-many with notes via note_tags. Currently doing triple duty (type + lifecycle + topic) until note_type/status columns are added.
          

          

              <code>note_tags</code>
              ~300
              Junction table. Composite PK (note_id, tag_id). CASCADE on note delete.
          

          

              <code>note_links</code>
              ~50
              Directed relationships between notes (source to target). Composite PK. CASCADE on either note delete. Renders as Related Notes in browse frontend.
          

          

              <code>note_revisions</code>
              ~500
              Revision history. Every update_note call creates a revision. Ordered by revision_number. CASCADE on note delete.
          

          

              <code>users</code>
              1
              Browse frontend auth. Email + hashed password. Session-based login.
          

      
    

  



          
  
Key Relationships



          
  
    
      
  • projects to notes (1:N via project_id): every note belongs to a project
    • 
      
  • projects to notes (1:1 via page_note_id): a project can have one page note for rich content. RESTRICT on delete prevents orphaning.
    • 
      
  • projects to repos (1:N via project_id): repos belong to projects
    • 
      
  • notes to tags (M:N via note_tags): notes have topic tags
    • 
      
  • notes to notes (M:N via note_links): directed relationships (source to target)
    • 
      
  • notes to note_revisions (1:N): full revision history, cascade delete
    • 



          
  
What the Note Decomposition Plan Adds



          
    
See plan-2026-03-01-note-decomposition Phase 2. Four new columns on the notes table:



          
  



          
    
What these enable:



          
  

    

        
          

              Column
              Type
              Purpose
          

        
      
          

              <code>note_type</code>
              varchar, nullable
              Replaces type tags. Values: plan, phase, sop, convention, issue, todo, template, project-page, skill, agent, doc. Enables <code>list_notes(note_type="phase")</code>.
          

          

              <code>status</code>
              varchar, nullable
              Replaces lifecycle tags. Values depend on note_type (see <code>note-conventions</code>). Enables status-only updates without rewriting content.
          

          

              <code>parent_note_id</code>
              FK to notes.id, nullable
              Self-referential. Links a phase to its parent plan. Enables <code>list_notes(parent_slug="plan-...")</code> to get all phases of a plan.
          

          

              <code>position</code>
              integer, nullable
              Ordering of children within a parent. Phase 1 = position 1, Phase 2 = position 2, etc.
          

      
    

  



          
  
Pydantic API Schemas



          
    
The API uses Pydantic models in src/pal_e_docs/schemas.py. Key patterns:



          
  
    
      
  • Create schemas accept slugs for FK references (e.g., project_slug instead of project_id). The route resolves the slug to an ID.
    • 
      
  • Out schemas nest related objects (e.g., NoteOut.project is a full ProjectOut, not just an ID).
    • 
      
  • NoteSummary omits html_content to save tokens on list queries. Does not currently include project info (N+1 problem on session start hook).
    • 
      
  • Tags are passed as comma-separated strings on create/update, returned as TagOut objects on read.
    • 



          
  


          
  
    
      
  • entity-page-architecture — why page_note_id FK is on entity tables, not polymorphic
    • 
      
  • plan-2026-03-01-note-decomposition — the plan adding note_type, status, parent_note_id, position
    • 
      
  • note-conventions — defines the note_type enum and status-per-type values
    • 
      
  • Procedures: sop-db-migration-recovery — recovery SOP for database schema changes
    •