# Working with HRFocus database schema projects

Owner: HRFocus Development  
Audience: Developers, technical support, DBAs, QA, and Codex users  
Applies to: HRDB (MyFocus), PayrollDB, and CareerFocusDB  
Sensitivity: Internal confidential  
Last reviewed: 2026-08-25

## Purpose and authority

These packages provide an object-per-file snapshot of the three core HRFocus database schemas for code navigation, dependency analysis, comparisons, testing, and implementation planning.

- **HRDB**, also referred to as **MyFocus**, is the parent and most current core schema. Employee, TBComp, department, POSI, jobs, workflow, notifications, Report Portal, and leave calculation objects principally live here.
- **PayrollDB** contains payroll processing, tax calculation, tax configuration, and payroll reporting objects. Some current views directly reference HRDB; treat those as cross-database dependencies until they are deliberately removed.
- **CareerFocusDB** contains the CareerFocus schema and shares selected platform definitions, including Report Portal structures.
- Report Portal definitions should remain aligned across the three solutions where the shared feature applies.

The packages are reference snapshots, not deployment scripts and not yet the authoritative source-control repository. Do not execute an entire package against a database.

## Available packages

The Development Resources Hub publishes:

| Package | Intended use |
|---|---|
| `HRFocus-Database-Schema-HRDB.zip` | MyFocus/HR schema analysis and comparison |
| `HRFocus-Database-Schema-PayrollDB.zip` | Payroll and tax calculation analysis |
| `HRFocus-Database-Schema-CareerFocusDB.zip` | CareerFocus schema analysis and comparison |
| `HRFocus-Database-Schemas-All.zip` | Recommended complete analysis workspace with all three schemas |
| `Database-Schema-Package-Manifest.csv` | File counts, sizes, generation time, and SHA-256 package checksums |

## Create a local database analysis project

1. Create a dedicated local folder outside an application source tree, for example `C:\Development\HRFocus-Databases`.
2. Download `HRFocus-Database-Schemas-All.zip` and `Database-Schema-Package-Manifest.csv` from the **Database Schemas** category in the Development Resources Hub.
3. Verify the download before extraction:

   ```powershell
   Get-FileHash -Algorithm SHA256 -LiteralPath '.\HRFocus-Database-Schemas-All.zip'
   Import-Csv -LiteralPath '.\Database-Schema-Package-Manifest.csv' |
       Where-Object Package -eq 'HRFocus-Database-Schemas-All.zip'
   ```

4. Extract the bundle into the project folder:

   ```powershell
   Expand-Archive -LiteralPath '.\HRFocus-Database-Schemas-All.zip' `
       -DestinationPath 'C:\Development\HRFocus-Databases' -Force
   ```

5. Confirm the project has exactly these schema roots:

   ```text
   HRFocus-Databases/
   ├── HRDB/
   ├── PayrollDB/
   └── CareerFocusDB/
   ```

6. Open `HRFocus-Databases` as the workspace/project in Codex, Visual Studio Code, or the approved editor. Keep all three database folders in the same workspace so cross-database references and shared definitions can be traced.
7. Do not add connection strings, client exports, database backups, credentials, `.dacpac` files containing data, or generated test data to this folder.

To use only one schema, download its individual ZIP and extract it into the same project layout. Do not merge files from different databases into one folder; identical object names must retain their database context.

## Reference the schemas from skills and analysis tasks

Give the skill or task the database root, the object or business capability being investigated, and the required comparison boundary. Useful prompts include:

- `Use PayrollDB as the primary schema and HRDB only for cross-database dependencies. Trace the tax calculation entry procedure, every called function/procedure, and every tax table it reads or writes.`
- `Compare the Report Portal table and procedure definitions in HRDB, PayrollDB, and CareerFocusDB. Treat HRDB/MyFocus as the parent implementation and report material differences.`
- `Use $build-hrfocus-database-package with the PayrollDB definitions as the baseline. Package only the approved tax-table changes and include verification and rollback guidance.`
- `Find every PayrollDB view that directly references HRDB and produce a dependency inventory. Diagnose only; do not remove or rewrite anything.`

For reliable results:

- State which database is authoritative for the task. MyFocus/HRDB is the parent unless a PayrollDB- or CareerFocusDB-specific rule is being analysed.
- Ask for read-only analysis when you do not want changes.
- Ask the skill to cite exact files and lines for important conclusions.
- Include all called objects in scope; tax and leave calculations frequently delegate work to helper procedures, functions, views, and configuration tables.
- Distinguish schema definitions from legislation or client data. A schema snapshot proves what the code says, not that current statutory values are correct.

## Search and compare locally

The file names follow the scripted-object convention `schema.object.ObjectType.sql`. `rg` is the fastest first pass:

```powershell
rg -n -i "tax|paye|rebate|threshold" '.\PayrollDB' --glob '*.sql'
rg -n "\[HRDB\]|HRDB\.dbo" '.\PayrollDB' --glob '*.sql'
rg -n "REPORT_PORTAL|HRF_REPORT" '.\HRDB' '.\PayrollDB' '.\CareerFocusDB' --glob '*.sql'
```

When comparing two snapshots, compare by relative file name first, then inspect semantic SQL differences. Generated formatting, `SET` directives, or scripting headers can otherwise create noise.

## Refresh and validate the published packages

The governed publisher runs:

```powershell
& '.\00 Administration\005 - Package Database Schemas.ps1'
& '.\00 Administration\Validate-DeveloperResources.ps1'
```

The packaging script:

- reads only `.sql` files from the three approved schema roots;
- preserves each database as a separate top-level folder;
- excludes `.git`, editor state, temporary files, and non-SQL content;
- produces individual and combined ZIPs;
- records object counts, byte counts, timestamps, and SHA-256 hashes; and
- copies the governed artifacts into the website download folder.

Any changed package should be reviewed before the website is deployed. A package refresh must not silently replace the eventual source-controlled baseline.

## Recommended source-control direction

Use one private repository named along the lines of `HRFocus.DatabaseSchemas`, with one SQL database project per core database:

```text
HRFocus.DatabaseSchemas/
├── src/
│   ├── HRDB/
│   │   └── HRDB.sqlproj
│   ├── PayrollDB/
│   │   └── PayrollDB.sqlproj
│   └── CareerFocusDB/
│       └── CareerFocusDB.sqlproj
├── tests/
├── tools/
├── docs/
└── HRFocus.DatabaseSchemas.sln
```

Adopt it in two controlled phases:

1. **Snapshot phase:** import the current scripts without redesigning them, normalize only unstable scripting headers, establish one reviewed baseline commit, and use pull requests for every object change.
2. **Database-project phase:** build each schema as a SQL database project targeted to SQL Server 2016, resolve same-database references, and model legitimate cross-database dependencies explicitly. PayrollDB references to HRDB should use a database/project reference and a deployment-time database variable while they still exist; do not copy HRDB objects into PayrollDB merely to make a build pass.

For new development, evaluate SDK-style SQL projects using `Microsoft.Build.Sql`; they automatically include `.sql` files placed in the project directory and build a `.dacpac` with syntax and reference validation. Confirm the chosen Visual Studio/Visual Studio Code workflow in a pilot before standardising because Visual Studio support differs by version. Microsoft guidance:

- [What are SQL database projects?](https://learn.microsoft.com/sql/tools/sql-database-projects/sql-database-projects)
- [Add existing files to a SQL project](https://learn.microsoft.com/sql/tools/sql-database-projects/howto/add-existing-files-to-sql-project)
- [Database references overview](https://learn.microsoft.com/sql/tools/sql-database-projects/concepts/database-references)
- [SqlPackage Extract](https://learn.microsoft.com/sql/tools/sqlpackage/sqlpackage-extract)

The repository should become the reviewed source of truth. A scheduled export from approved reference databases may detect drift and open a branch or pull request, but it should never overwrite the main branch automatically. CI should build all three projects, validate cross-database references, create disposable SQL Server 2016 test databases where practical, run smoke/regression tests for critical calculations, and publish immutable build artifacts. Tax legislation changes should normally modify versioned tax configuration tables and their tests; procedural changes require an explicit code review with before/after calculation cases.