PUL Staff Airtable

Configuration

Setting Up Test and Production Environments

This tool uses separate test and production Airtable bases for safety. In particular, the test environment lets you manually test sync workflows without affecting the production base.

Authentication

Visit https://airtable.com/create/tokens and create a token (or two separate tokens) with data.records:read and data.records:write permissions.

Configuration File

Create a file called private.json as a sibling to main.py:

{
  "test": {
    "PAT": "your_test_airtable_token",
    "BASE_ID": "your_test_base_id",
    "ALL_STAFF_TABLE_ID": "test_all_staff_table_id",
    "REMOVAL_TABLE_ID": "test_removal_table_id"
  },
  "production": {
    "PAT": "your_production_airtable_token",
    "BASE_ID": "your_production_base_id",
    "ALL_STAFF_TABLE_ID": "production_all_staff_table_id",
    "REMOVAL_TABLE_ID": "production_removal_table_id"
  }
}

Note: You can use the same PAT for both environments if desired, but the base IDs should be different. If you copied the test base from produciton, the table IDs might be the same, but the base ids will be different.

⚠️ Security and Privacy

IMPORTANT: This application handles sensitive data.

• NEVER commit private.json - it contains API credentials and is in .gitignore
• NEVER commit CSV files - they contain personal employee data
• NEVER commit real employee data - use mock data in test fixtures
• Test fixtures should use synthetic data only - generic names, fake IDs, placeholder information
• Always verify that no sensitive data is staged before committing: git diff --staged

Installation

1. Install PDM
2. Clone this repository
3. Run pdm install

Running

Test Environment (Default - Recommended First)

All commands use the test Airtable base by default for safety. Try these first:

# Run consistency checks on TEST base
pdm run pul-staff check

# Sync CSV data to TEST base (interactive prompts)
pdm run pul-staff sync

# Update supervisor hierarchy on TEST base
pdm run pul-staff update-supervisors

# Convert an employee's position to a vacancy on TEST base (by employee ID)
pdm run pul-staff employee-to-vacancy 123456789

# Convert a position to a vacancy on TEST base (by position number)
pdm run pul-staff position-to-vacancy 00012345

Production Environment (Requires Explicit Flag)

Once you're comfortable with the workflow in test, use the --production / -p flag:

# Run checks on PRODUCTION
pdm run pul-staff --production check

# Sync to PRODUCTION (prompts for confirmation)
pdm run pul-staff --production sync

# Update supervisors on PRODUCTION
pdm run pul-staff --production update-supervisors

# Convert an employee's position to a vacancy on PRODUCTION (prompts for confirmation)
pdm run pul-staff --production employee-to-vacancy 123456789

# Convert a position to a vacancy on PRODUCTION (prompts for confirmation)
pdm run pul-staff --production position-to-vacancy 00012345

Additional Options

# Specify custom CSV file path
pdm run pul-staff --csv ./path/to/report.csv sync

# Show detailed field changes during sync (verbose mode)
pdm run pul-staff sync --verbose
pdm run pul-staff sync -v

# Show detailed supervisor updates (verbose mode)
pdm run pul-staff update-supervisors --verbose
pdm run pul-staff update-supervisors -v

# Combine flags
pdm run pul-staff --production sync --verbose
pdm run pul-staff --production update-supervisors --verbose

# Get help
pdm run pul-staff --help
pdm run pul-staff sync --help

Verbose Mode (--verbose / -v):

Available for check, sync, and update-supervisors commands.

For check command:

• Non-verbose (default): Shows count of records with field differences and always displays position number changes (critical)
• Verbose: Shows all field-by-field changes for every record
• Example output (non-verbose):

  Found 5 record(s) with field differences
  John Doe [Position Number] changed from [12345] to [67890]
  

• Example output (verbose):

  Field differences between CSV and Airtable:
  John Doe [Title] changed from [Associate Librarian] to [Senior Librarian]
  John Doe [Grade] changed from [P3] to [P4]
  Jane Smith [Department] changed from [IT] to [Library Services]
  

For sync command:

• Displays line-by-line changes for each field
• Format: Name [field] changed from [old value] to [new value]
• Example output:

  Jane Smith [Title] changed from [Associate Librarian] to [Senior Librarian]
  John Doe [pul:On Leave?] changed from [No] to [Yes]
  

For update-supervisors command:

• Displays each supervisor relationship change
• Format: Employee supervisor changed from Old Supervisor to New Supervisor
• Useful for auditing supervisor hierarchy changes
• Example output:

  Jane Smith supervisor changed from Alice Brown to John Doe
  Bob Jones supervisor changed from (none) to Jane Smith
  

Converting a Position to a Vacancy

When someone leaves, their position can be turned into a vacancy on demand — without waiting for the next full sync. Two commands do the same conversion; they differ only in how the record is located:

# By employee ID (Airtable "University ID" field)
pdm run pul-staff employee-to-vacancy <emplid>

# By position number (Airtable "Position Number" field)
pdm run pul-staff position-to-vacancy <position_number>

# Add --production to target the production base (prompts for confirmation)
pdm run pul-staff --production position-to-vacancy <position_number>

Use position-to-vacancy when you know the position number but not the employee ID, or for records that have no employee ID to match on (for example DoF staff matched by name). position-to-vacancy refuses to run if the position is already a vacancy. The conversion:

• Renames the record to the next __VACANCY_NNN__ placeholder
• Preserves the departing person's name in the Last Occupant field
• Keeps the Position Number, so the vacancy stays tied to its position
• Clears personal fields (name, email, netid, phone, start/end/rehire dates, University ID)
• Sets pul:Search Status to Recently Vacated and applies the "vacant" headshot
• Logs the removal (name, netid, title, position, division, supervisor, and today's date) to the removal-history table

This is the same conversion the interactive sync command offers when it finds an Airtable record that is missing from the CSV report; the standalone command simply lets you do it on demand for a single person.

Development

Running Tests

The test suite uses pytest with comprehensive coverage for all modules:

# Run all tests and get a coverage report
pdm test

# Run specific test file
pdm run pytest tests/test_sync_validator.py

All tests use mock data and fixtures - no real employee data is required or used in tests.

Type Checking

The project uses mypy with strict type checking enabled:

# Run mypy on entire project
pdm mypy

# Run mypy on specific module
pdm mypy staff_management/

# Run mypy on specific file
pdm mypy main.py

All code must pass mypy strict type checking with no errors.