Skip to content

02_database_schema.md

Latest commit

 

History

History
361 lines (315 loc) · 17.1 KB

02_database_schema.md

File metadata and controls

361 lines (315 loc) · 17.1 KB
Raw

Database Schema and Data Model

1. Scope, Engine & Source of Truth

The ISSA platform utilizes a cloud-hosted PostgreSQL database provided by Neon.

  • Engine: PostgreSQL 16+ compatible serverless engine.
  • Connection Model: HTTP/WebSocket-based connection pooling via @neondatabase/serverless.
  • Source of Truth: The ordered SQL files inside db/migrations/ (10 migration files).
  • Migration Engine: Custom lightweight Node.js runner (node --env-file=.env scripts/migrate.js) that splits SQL scripts by the -- migrate:statement delimiter and executes statements sequentially.

2. Entity Relationship Diagram (ERD) — Verified from Neon Migrations

erDiagram
    STAFF_PROFILES ||--o{ BLOG_POSTS : "created_by_id / updated_by_id"
    STAFF_PROFILES ||--o{ BLOG_POST_REVISIONS : "editor_id"
    BLOG_POSTS ||--o{ BLOG_POST_REVISIONS : "has history of (post_id CASCADE)"
    JOB_OPENINGS o|--o{ CAREER_APPLICATIONS : "receives (job_id SET NULL)"
    CAREER_APPLICATIONS ||--o{ RESUME_FILES : "contains binary (application_id CASCADE)"
    
    STAFF_PROFILES {
        bigint id PK "GENERATED ALWAYS AS IDENTITY"
        string auth_user_id UK "Neon Auth foreign user ID"
        string full_name
        string email UK "CHECK @pinebrooktechnologies.com"
        string role "ADMIN | CONTENT (or initial pending NO_ACCESS)"
        string status "active | suspended"
        timestamptz last_login_at
        timestamptz created_at
        timestamptz updated_at
    }

    BLOG_POSTS {
        bigint id PK "GENERATED ALWAYS AS IDENTITY"
        string slug UK
        string category
        string title
        string subtitle
        string excerpt
        text content_markdown
        string cover_image_path
        string author_name
        smallint reading_time_minutes
        string status "draft | in_review | scheduled | published | archived"
        int version "Default 1, increments on edit"
        string seo_title
        string seo_description
        bigint created_by_id FK "REFERENCES staff_profiles(id)"
        bigint updated_by_id FK "REFERENCES staff_profiles(id)"
        timestamptz published_at
        timestamptz created_at
        timestamptz updated_at
    }

    BLOG_POST_REVISIONS {
        bigint id PK "GENERATED ALWAYS AS IDENTITY"
        bigint post_id FK "REFERENCES blog_posts(id) ON DELETE CASCADE"
        bigint editor_id FK "REFERENCES staff_profiles(id) ON DELETE SET NULL"
        string editor_email
        int revision_number "Sequential 1, 2, 3..."
        string title
        string subtitle
        string category
        string excerpt
        text content_markdown
        string cover_image_path
        string author_name
        smallint reading_time_minutes
        string status
        string seo_title
        string seo_description
        string change_summary
        timestamptz created_at
    }

    JOB_OPENINGS {
        bigint id PK "GENERATED ALWAYS AS IDENTITY"
        string slug UK
        string title
        string department
        string location
        string employment_type
        string salary
        text description
        jsonb requirements "Array of qualification strings"
        string status "active | closed | draft | archived"
        timestamptz closing_time
        int display_order
        timestamptz created_at
        timestamptz updated_at
    }

    CAREER_APPLICATIONS {
        bigint id PK "GENERATED ALWAYS AS IDENTITY"
        bigint job_id FK "REFERENCES job_openings(id) ON DELETE SET NULL"
        string role_slug
        string full_name
        string email
        string phone
        string experience_years
        text statement
        text consent_text
        string consent_version "Default: 2026-v1"
        timestamptz consented_at
        string status "new | under_review | interview_scheduled | rejected | hired | archived"
        string assigned_to
        timestamptz created_at
        timestamptz updated_at
    }

    RESUME_FILES {
        bigint id PK "GENERATED ALWAYS AS IDENTITY"
        bigint application_id FK "REFERENCES career_applications(id) ON DELETE CASCADE"
        string storage_key UK
        string original_filename
        string mime_type "application/pdf | application/msword | etc."
        bigint size_bytes "CHECK size_bytes > 0 AND <= 5MB"
        string checksum_sha256 "SHA-256 integrity hash"
        bytea file_data "Binary payload stored in PostgreSQL"
        timestamptz uploaded_at
        timestamptz deleted_at "Soft-deletion timestamp"
        timestamptz created_at
    }
Loading

3. Comprehensive Table Catalogue & Column Breakdown

3.1. Core Publishing & CMS Tables

blog_posts

Stores core blog articles, publication status, SEO metadata, and active version numbers.

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY): Unique post identifier.
  • slug (TEXT NOT NULL UNIQUE): URL-friendly identifier (e.g. empowering-rural-youth-2026).
  • category (TEXT NOT NULL): Article classification (e.g. Education, Healthcare, Skills).
  • title (TEXT NOT NULL): Display title of the article.
  • subtitle (TEXT NOT NULL): Supporting headline.
  • excerpt (TEXT NOT NULL): Short summary displayed in card previews and search results.
  • content_markdown (TEXT NOT NULL): Full article body formatted as Markdown.
  • cover_image_path (TEXT NOT NULL CHECK (cover_image_path LIKE '/%')): Relative path to hero graphic.
  • author_name (TEXT NOT NULL): Display name of author.
  • reading_time_minutes (SMALLINT NOT NULL CHECK (reading_time_minutes > 0)): Estimated reading time.
  • status (TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'in_review', 'scheduled', 'published', 'archived'))): Publication workflow state.
  • version (INT NOT NULL DEFAULT 1): Incremental version counter bumped on each update.
  • seo_title (TEXT): Custom <title> meta tag for search engines.
  • seo_description (TEXT): Custom <meta name="description"> tag.
  • created_by_id (BIGINT REFERENCES staff_profiles(id) ON DELETE SET NULL): Creating staff user.
  • updated_by_id (BIGINT REFERENCES staff_profiles(id) ON DELETE SET NULL): Last editing staff user.
  • published_at (TIMESTAMPTZ): Timestamp when the article was published.
  • created_at / updated_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())

blog_post_revisions

Immutable point-in-time snapshots created automatically whenever a blog post is modified.

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY): Revision ID.
  • post_id (BIGINT NOT NULL REFERENCES blog_posts(id) ON DELETE CASCADE): Target post.
  • editor_id (BIGINT REFERENCES staff_profiles(id) ON DELETE SET NULL): Staff member who performed the edit.
  • editor_email (TEXT NOT NULL): Email address of editor at time of snapshot.
  • revision_number (INT NOT NULL): Sequential revision number (1, 2, 3...).
  • title, subtitle, category, excerpt, content_markdown, cover_image_path, author_name, reading_time_minutes, status, seo_title, seo_description: Exact point-in-time snapshot.
  • change_summary (TEXT): Commit note describing what was altered.
  • created_at (TIMESTAMPTZ NOT NULL DEFAULT NOW()): Snapshot creation timestamp.

site_settings

Global organization-wide configuration and branding information.

  • id (INT PRIMARY KEY DEFAULT 1 CHECK (id = 1)): Singleton constraint.
  • site_name (TEXT NOT NULL DEFAULT 'ISSA Foundation')
  • site_tagline (TEXT NOT NULL DEFAULT 'Grassroots Development Across Uttarakhand')
  • logo_url (TEXT NOT NULL DEFAULT '')
  • announcement_enabled (BOOLEAN NOT NULL DEFAULT false)
  • announcement_text / announcement_link / announcement_button_text (TEXT)
  • phone (TEXT NOT NULL DEFAULT '0135 430 8180')
  • email (TEXT NOT NULL DEFAULT 'career.issafoundation@gmail.com')
  • head_office_address / regional_office_address (TEXT NOT NULL)
  • youtube_url / facebook_url / instagram_url / twitter_url / linkedin_url (TEXT NOT NULL)
  • tax_exempt_info / footer_tagline (TEXT NOT NULL)
  • updated_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())

hero_slides

Carousel slides rendered on the homepage hero section.

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  • slide_key (TEXT NOT NULL UNIQUE): Unique slide identifier (e.g. ecosystem, healthcare, education).
  • eyebrow (TEXT NOT NULL): Top tag label.
  • title (TEXT NOT NULL): Headline.
  • highlight (TEXT NOT NULL): Emphasized title text.
  • description (TEXT NOT NULL): Narrative summary.
  • image (TEXT NOT NULL): Image URL/path.
  • cta_label / cta_href (TEXT NOT NULL): Primary call-to-action button.
  • donate_label / donate_href (TEXT NOT NULL DEFAULT 'Support Our Mission' / '/contact')
  • display_order (INT NOT NULL DEFAULT 0): Sort ordering.
  • is_active (BOOLEAN NOT NULL DEFAULT true): Visibility toggle.
  • created_at / updated_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())

home_sections & impact_content

Modular content blocks stored as flexible JSONB.

  • section_key (VARCHAR(100) PRIMARY KEY): Unique semantic key.
  • title (VARCHAR(255)): Header title.
  • content_json (JSONB NOT NULL): Structured JSON payload.
  • updated_at (TIMESTAMPTZ DEFAULT NOW())

programs_content

Detailed narrative and modular curriculum data.

  • slug (VARCHAR(100) PRIMARY KEY): URL path identifier (e.g. healthcare, education, entrepreneurship).
  • title (VARCHAR(255) NOT NULL): Program title.
  • summary (TEXT): Short summary.
  • objectives (JSONB NOT NULL DEFAULT '[]'): Array of objective strings.
  • curriculum_modules (JSONB NOT NULL DEFAULT '[]'): Array of module objects.
  • impact_metrics (JSONB NOT NULL DEFAULT '{}'): Key figures.
  • is_active (BOOLEAN DEFAULT true)

faqs & office_locations

  • faqs: id, category, question, answer, display_order, is_active.
  • office_locations: id, name, address_line1, address_line2, city, state, postal_code, phone, email, is_headquarters, display_order, is_active.

legal_pages

Stores Markdown legal agreements rendered at /privacy and /terms.

  • slug (VARCHAR(50) PRIMARY KEY): privacy or terms.
  • title (VARCHAR(255) NOT NULL): Page title.
  • content_markdown (TEXT NOT NULL): Raw Markdown body.
  • last_updated (TIMESTAMPTZ NOT NULL DEFAULT NOW())

media_assets

Metadata index and optional binary store for uploaded media.

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  • file_name (VARCHAR(255) NOT NULL)
  • mime_type (VARCHAR(100) NOT NULL)
  • file_size_bytes (INT NOT NULL)
  • url (TEXT)
  • alt_text (VARCHAR(500))
  • created_at (TIMESTAMPTZ DEFAULT NOW())

3.2. Careers & Resume Tables

job_openings

Vacancies published at /careers.

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  • slug (TEXT NOT NULL UNIQUE)
  • title (TEXT NOT NULL)
  • department (TEXT NOT NULL)
  • location (TEXT NOT NULL)
  • employment_type (TEXT NOT NULL)
  • salary (TEXT)
  • description (TEXT NOT NULL)
  • requirements (JSONB NOT NULL DEFAULT '[]'::jsonb)
  • status (TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'closed', 'draft', 'archived')))
  • closing_time (TIMESTAMPTZ)
  • display_order (INTEGER NOT NULL DEFAULT 0)
  • created_at / updated_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())

career_applications

Applicant submissions.

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  • job_id (BIGINT REFERENCES job_openings(id) ON DELETE SET NULL)
  • role_slug (TEXT NOT NULL)
  • full_name (TEXT NOT NULL)
  • email (TEXT NOT NULL)
  • phone (TEXT)
  • experience_years (TEXT NOT NULL)
  • statement (TEXT)
  • consent_text (TEXT NOT NULL)
  • consent_version (TEXT NOT NULL DEFAULT '2026-v1')
  • consented_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())
  • status (TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'under_review', 'interview_scheduled', 'rejected', 'hired', 'archived')))
  • assigned_to (TEXT)
  • created_at / updated_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())

resume_files

Binary document storage for applicant CVs.

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  • application_id (BIGINT NOT NULL REFERENCES career_applications(id) ON DELETE CASCADE)
  • storage_key (TEXT NOT NULL UNIQUE): Unique lookup key.
  • original_filename (TEXT NOT NULL): Original file name (e.g. JohnDoe_Resume.pdf).
  • mime_type (TEXT NOT NULL): MIME type (e.g. application/pdf).
  • size_bytes (BIGINT NOT NULL CHECK (size_bytes > 0)): File size in bytes.
  • checksum_sha256 (TEXT NOT NULL): Cryptographic SHA-256 hash.
  • file_data (BYTEA): Binary buffer stored directly in PostgreSQL.
  • uploaded_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())
  • deleted_at (TIMESTAMPTZ): Soft-delete timestamp.
  • created_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())

3.3. Public Inquiries & Subscriptions

contact_submissions

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  • name (TEXT NOT NULL), email (TEXT NOT NULL), phone (TEXT), subject (TEXT NOT NULL), message (TEXT NOT NULL)
  • status (TEXT NOT NULL DEFAULT 'unread' CHECK (status IN ('unread', 'in_progress', 'resolved', 'spam')))
  • created_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())

newsletter_subscriptions

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  • email (TEXT NOT NULL UNIQUE)
  • status (TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'unsubscribed')))
  • source_page (TEXT DEFAULT '/')
  • created_at (TIMESTAMPTZ NOT NULL DEFAULT NOW())

3.4. Staff Access, Governance & Telemetry

staff_profiles & staff_email_access

  • staff_profiles: id, auth_user_id (TEXT UNIQUE), full_name (TEXT NOT NULL), email (TEXT NOT NULL UNIQUE CHECK (email = LOWER(email) AND email LIKE '%@pinebrooktechnologies.com')), role (TEXT NOT NULL CHECK (role IN ('ADMIN', 'CONTENT'))), status (TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended'))), last_login_at, created_at, updated_at.
  • staff_email_access: email (TEXT PRIMARY KEY), role_access_name (staff_role_access_name ENUM ('admin', 'content')), created_at, updated_at.

audit_events

  • id (BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  • actor_email (VARCHAR(255) NOT NULL), action (VARCHAR(100) NOT NULL), entity_type (VARCHAR(100) NOT NULL), entity_id (VARCHAR(100)), details (JSONB DEFAULT '{}'), ip_address (VARCHAR(100)), user_agent (TEXT), created_at (TIMESTAMPTZ DEFAULT NOW()).

server_logs

  • id (SERIAL PRIMARY KEY)
  • timestamp (TIMESTAMPTZ NOT NULL DEFAULT NOW()), status_code (INT NOT NULL), log_type (VARCHAR(50) NOT NULL), region (VARCHAR(50) NOT NULL DEFAULT 'ap-south-1'), endpoint (VARCHAR(255)), method (VARCHAR(10) DEFAULT 'GET'), response_time_ms (INT DEFAULT 0), error_message (TEXT), client_ip_hash (VARCHAR(64)), metadata (JSONB).

4. Chronological Migration Inventory (10 Files)

# Migration Filename Scope & Key DDL
1 20260820_blog_posts.sql Creates blog_posts table with title, slug, category, markdown content, status constraint.
2 20260821_careers_and_resumes.sql Creates job_openings, career_applications, resume_files (BYTEA), indexes and initial jobs.
3 20260822_public_forms.sql Creates contact_submissions and newsletter_subscriptions tables.
4 20260824_staff_profiles.sql Creates staff_profiles with @pinebrooktechnologies.com domain check and staff_email_access.
5 20260825_staff_access_requests.sql Adds pending access request handlers and promotion workflows.
6 20260825_cms_blog_workflow.sql Alters blog_posts (adds version, seo_title, seo_description, created_by_id, updated_by_id) and creates blog_post_revisions.
7 20260826_resume_files_neon_storage.sql Ensures resume_files.file_data is configured for binary storage and checksum verification.
8 20260827_audit_events.sql Creates audit_events immutable security ledger.
9 20260828_complete_site_cms.sql Creates site_settings, hero_slides, home_sections, impact_content, programs_content, faqs, office_locations, media_assets, and legal_pages.
10 20260829_server_logs_and_monitoring.sql Creates server_logs table with 4xx/5xx telemetry and regional latency tracking.

5. Verification SQL Queries

Run these queries in the Neon SQL console to verify schema integrity:

-- 1. Verify all 18 tables exist
SELECT tablename, tableowner 
FROM pg_tables 
WHERE schemaname = 'public' 
ORDER BY tablename;

-- 2. Verify foreign key integrity
SELECT 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public';