Skip to main content

Database Review

This document records findings from a schema review of the BonardaHR PostgreSQL database, including identified issues, their severity, recommendations, and an action plan.

Summary of Findings​

SeverityCount
🔴 Critical4
🟡 Medium6
🟢 Low5

🔴 Critical Issues​

1. Missing Foreign Key: employees.position​

Location: employees table

Issue: The position column stores a plain text string (VARCHAR(100)) rather than a foreign key to a positions table.

Problems:

  • Inconsistent position names across employees (e.g. "Software Engineer" vs. "SW Engineer")
  • No referential integrity — positions can't be renamed or managed centrally
  • Cannot query employees by position without string matching

Recommendation: Add a positions table and a position_id FK:

CREATE TABLE positions (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    ...
);

ALTER TABLE employees
    ADD COLUMN position_id BIGINT REFERENCES positions(id) ON DELETE SET NULL;

2. Missing Primary Key on section_fields​

Location: section_fields table

Issue: The table lacks a clearly defined primary key in its current schema definition.

Recommendation: Verify the migration and ensure a BIGSERIAL PK is defined.

3. Denormalised visible_to_roles Column​

Location: employee_sections.visible_to_roles

Issue: Roles are stored as a comma-separated string (e.g. 'ADMIN,HR_MANAGER').

Current state:

visible_to_roles VARCHAR(500)  -- e.g., 'ADMIN,HR_MANAGER'

Problems:

  • Violates First Normal Form (1NF)
  • Requires string parsing in queries
  • Cannot use FK constraints
  • If a role is renamed, all sections using that role string need manual updates

Recommendation: Replace with a junction table:

CREATE TABLE section_role_visibility (
    section_id BIGINT NOT NULL REFERENCES employee_sections(id) ON DELETE CASCADE,
    role_id    BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (section_id, role_id)
);

4. Document Deletion Cascades When Uploader Leaves​

Location: documents.uploaded_by_id FK constraint

Issue: ON DELETE CASCADE means if the employee who uploaded a document is deleted, all their documents are also deleted.

Current state:

CONSTRAINT fk_documents_uploaded_by
    FOREIGN KEY (uploaded_by_id) REFERENCES employees(id) ON DELETE CASCADE

Problems:

  • Company policy documents and contracts are lost when an HR person leaves
  • Document shares and signatures also cascade-delete
  • Historical compliance documents could be permanently lost

Recommendation: Change to ON DELETE SET NULL and make uploaded_by_id nullable (or use soft-delete for employees).

🟡 Medium Severity Issues​

5. No Foreign Key on Audit Columns​

Location: created_by, updated_by columns on all tables

Issue: These columns use BIGINT (matching employees.id) but have no actual FK constraints.

Problems: Orphaned references when employees are deleted; cannot verify who made changes if the auditor no longer exists.

Recommendation:

ALTER TABLE employees
    ADD CONSTRAINT fk_employees_created_by
    FOREIGN KEY (created_by) REFERENCES employees(id) ON DELETE SET NULL;

6. Timestamps Without Timezone​

Location: All TIMESTAMP columns across all tables

Issue: All timestamps use TIMESTAMP without timezone information.

Problems: Timezone ambiguity when deploying across regions; daylight saving time edge cases; API consumers don't know the timezone context.

Recommendation: Use TIMESTAMPTZ:

created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP

7. Document Share Cascade Deletes When Sharer Leaves​

Location: document_shares.shared_by_id

Issue: ON DELETE CASCADE means all document shares are deleted when the sharing employee leaves.

Problems: Other employees lose access to shared documents; audit trail of who shared what is lost.

Recommendation: Change to ON DELETE SET NULL and make nullable.

8. No Soft Delete Pattern for Critical Data​

Location: documents, time_off_requests, and other tables

Issue: Hard deletes are used throughout — no deleted_at or is_deleted column.

Problems: Lost historical data; cannot restore accidentally deleted records; potential compliance issues.

Recommendation:

ALTER TABLE documents ADD COLUMN deleted_at TIMESTAMP;
CREATE INDEX idx_documents_not_deleted ON documents(deleted_at) WHERE deleted_at IS NULL;

9. Project Time Log Cannot Record Zero Hours​

Location: project_time_logs.hours CHECK constraint

Issue: CHECK (hours > 0) prevents logging zero hours.

Problems: Cannot "zero out" an existing entry; must delete rather than update to 0.

Recommendation: Change to hours >= 0 or handle via application logic.

10. employee_roles Lacks Full Audit Trail​

Location: employee_roles table

Issue: Has assigned_at and assigned_by but:

  • assigned_by has no FK constraint
  • No tracking of when roles are removed

Recommendation: Add FK on assigned_by and consider an employee_role_audit table to track role removals.

🟢 Low Severity Issues​

11. Inconsistent Unique Constraint Patterns​

Some tables use inline UNIQUE constraints; others use separate CREATE UNIQUE INDEX. Standardise on CREATE UNIQUE INDEX for better control (partial indexes, concurrent creation).

12. Missing Composite Indexes for Common Query Patterns​

Queries that could benefit from composite indexes:

  • time_off_requests: (employee_id, status)
  • document_shares: (employee_id, viewed_at) for unread document queries
  • timesheet_entries: (timesheet_id, entry_date)

13. Redundant Index on employee_sections.name​

employee_sections.name already has a UNIQUE constraint (which creates an implicit unique index). The explicit CREATE INDEX idx_employee_sections_name is redundant and should be removed.

14. FieldValueAudit Denormalised Fields (Intentional)​

employee_field_value_audit stores field_name and section_name denormalised. If a field or section is later renamed, audit records retain the original names. This is intentional and correct for audit purposes — audit records must preserve the state at the time of the change.

15. No Index on company_events.event_type​

If filtering by event_type is common, add:

CREATE INDEX idx_company_events_event_type ON company_events(event_type);

✅ Positive Observations​

The schema demonstrates several strong practices:

  1. Consistent public_id UUID for all API-facing identifiers
  2. Good use of CHECK constraints for enum column validation
  3. Proper cascade behaviours on most FK relationships
  4. updated_at triggers consistently applied via update_updated_at_column() function
  5. GIN indexes on JSONB columns for efficient dynamic field queries
  6. Unique constraints preventing duplicates in junction tables
  7. version column for optimistic locking support throughout

Phase 1 — Critical​

  1. Add position_id FK to employees table (create positions table)
  2. Replace visible_to_roles VARCHAR with a section_role_visibility junction table
  3. Change documents.uploaded_by_id to ON DELETE SET NULL

Phase 2 — Important​

  1. Add FK constraints on audit columns (created_by, updated_by) with ON DELETE SET NULL
  2. Migrate all TIMESTAMP columns to TIMESTAMPTZ
  3. Add soft-delete pattern to critical tables (documents, time_off_requests)

Phase 3 — Cleanup​

  1. Remove redundant index on employee_sections.name
  2. Add missing composite indexes for common query patterns
  3. Standardise unique constraint patterns to use CREATE UNIQUE INDEX
  4. Add FK and removal tracking to employee_roles