hirejared-kw-db/docs/project-docs/02-database/conventions.md

Database and Project Conventions

Database Naming Conventions

General Rules

• All database objects use snake_case
• All keywords stored in lowercase
• All names should be descriptive and clear
• Abbreviations avoided unless universally understood

Table Names

• Use plural form for table names (e.g., Keywords, Campaigns)
• Compound names use underscores (e.g., Landing_Pages)
• Junction tables combine both entity names (e.g., Keyword_Ad_Group_Assignments)
• Historical/log tables append appropriate suffix (e.g., Import_History)

Column Names

• Use singular form
• Include units in name if applicable (e.g., budget_daily)
• Use standard suffixes:
  • _id for primary keys
  • _at for timestamps
  • _date for dates
  • _type for type indicators
  • _status for status fields

Index Names

• Format: idx_[table]_[column(s)]
• Examples:
  • idx_keyword_search
  • idx_ad_group_campaign
  • idx_keyword_assignments

View Names

• Prefix with v_
• Describe the data being presented
• Examples:
  • v_active_keywords_per_ad_group
  • v_landing_page_usage

Function Names

• Use verb_noun format
• Describe the action being performed
• Examples:
  • update_keyword_metrics
  • calculate_campaign_stats

Data Type Conventions

Text Data

• VARCHAR(255) for standard text fields
• VARCHAR(2048) for URLs
• TEXT for unlimited length fields
• Always specify length for VARCHAR

Numeric Data

• INTEGER for whole numbers
• DECIMAL(10,2) for currency
• DECIMAL(5,2) for percentages
• Always specify precision and scale for DECIMAL

Date/Time Data

• TIMESTAMP without timezone for all timestamps
• DATE for date-only fields
• Default CURRENT_TIMESTAMP where appropriate

Boolean Data

• Use BOOLEAN type (not INTEGER 0/1)
• Default values specified where appropriate

Status Fields

Standard Status Values

• 'active'
• 'inactive'
• 'pending'
• 'archived'
• 'deleted'

Import Status Values

• 'SUCCESS'
• 'PARTIAL'
• 'FAILED'

Script Naming Conventions

Bash Scripts

• All scripts end in .sh
• Use kebab-case for names
• Format: action-object[-modifier].sh
• Examples:
  • create-database.sh
  • import-keywords-gkwp.sh
  • update-functions.sh

SQL Files

• End in .sql
• Match associated script name where applicable
• Examples:
  • create-keywords-table.sql
  • create-import-views.sql

File Organization

Script Categories

• Keep related scripts in appropriate subdirectories
• Create new categories as needed
• Document new categories in project documentation

Configuration

• All sensitive data in .database file
• Configuration format:

DB_USER=postgres
DB_PASSWORD=your_password
DB_HOST=localhost
DB_PORT=5432
DB_NAME=keyword_management

Code Formatting

SQL Formatting

• Keywords in UPPER CASE
• Indentation: 2 spaces
• One statement per line
• Commas at the end of lines
• Proper line breaks for readability

Example:

SELECT 
  k.keyword,
  k.gkwp_search_volume,
  ag.ad_group_name
FROM 
  Keywords k
  JOIN Keyword_Ad_Group_Assignments kaga 
    ON k.keyword_id = kaga.keyword_id
  JOIN Ad_Groups ag 
    ON kaga.ad_group_id = ag.ad_group_id
WHERE 
  k.status = 'active'
  AND ag.status = 'active';

Bash Script Formatting

• Two space indentation
• Clear section comments
• Consistent error handling
• Standard variable naming

Example:

#!/bin/bash

# Load database configuration
source .database

# Execute SQL command
psql -h "${DB_HOST}" \
     -p "${DB_PORT}" \
     -U "${DB_USER}" \
     -d "${DB_NAME}" \
     -f "sql/create-table.sql"

Documentation Conventions

Script Headers

• Purpose of script
• Usage example
• Dependencies
• Expected inputs/outputs
• Author and date

Example:

#!/bin/bash
# Purpose: Creates the Keywords table in the database
# Usage: ./create-keywords-table.sh
# Dependencies: .database configuration file
# Created: 2024-11-19

Comment Conventions

• SQL: -- for single line comments
• Bash: # for single line comments
• Include purpose for complex queries
• Document assumptions
• Explain non-obvious choices

Error Handling

Database Errors

• Proper error codes
• Meaningful error messages
• Transaction management
• Rollback procedures

Script Errors

• Exit codes for different scenarios
• Error logging
• User feedback
• Cleanup on failure

Logging Conventions

Log Format

• Timestamp
• Script name
• Action performed
• Status/Result
• Error details if applicable

Log Locations

• Script logs in /logs directory
• Database operation logs
• Error logs
• Import logs

Version Control

Commit Messages

• Clear and descriptive
• Reference task/ticket numbers
• Explain significant changes
• Document breaking changes

Branch Naming

• feature/description
• bugfix/description
• hotfix/description

These conventions ensure consistency across the project and make maintenance and collaboration easier.

Testing Conventions

Test Organization

• Test scripts stored in /tests directory
• Mirror /bin directory structure
• Test files named with .test.sh suffix
• Example:

  /bin
  └── create-functions/
      └── create-keyword-function.sh
  /tests
  └── create-functions/
      └── create-keyword-function.test.sh
  

Test Script Structure

#!/bin/bash

# Load test utilities
source ./tests/utils/test-framework.sh

# Test specific function creation
test_keyword_function() {
    # Setup - capture initial state
    initial_state=$(psql -t -c "SELECT count(*) FROM pg_proc WHERE proname = 'keyword_function';")
    
    # Execute the script being tested
    ../bin/create-functions/create-keyword-function.sh
    
    # Verify function exists
    exists_check=$(psql -t -c "SELECT count(*) FROM pg_proc WHERE proname = 'keyword_function';")
    assert_not_equals "$initial_state" "$exists_check" "Function should be created"
    
    # Verify function signature
    signature_check=$(psql -t -c "SELECT pg_get_functiondef('keyword_function'::regproc);")
    assert_contains "$signature_check" "expected_signature" "Function signature should match"
    
    # Test function behavior
    result=$(psql -t -c "SELECT keyword_function('test_input');")
    assert_equals "$result" "expected_output" "Function should produce expected results"
}

# Run tests
run_test_suite

Test Utilities

• Common test framework in /tests/utils
• Standard assertions
• Setup/teardown helpers
• Database state management
• Test reporting functions

Running Tests

• Individual test: ./tests/path/script.test.sh
• Category tests: ./tests/run-category.sh create-functions
• All tests: ./tests/run-all.sh

Test Reporting

• Standard output format
• Success/failure counts
• Execution time
• Detailed failure information
• State difference on failures

Database Test Environment

• Separate test database
• Reset to known state before tests
• Cleanup after test completion
• Isolation from production data

Continuous Integration

• Tests run before deployment
• Required passing tests for merges
• Test reports archived
• Coverage tracking

This approach offers several advantages:

1. Separation of concerns
2. Easier maintenance
3. Clear test organization
4. Reusable test utilities
5. Proper test isolation
6. Easy integration with CI/CD

Rather than building tests into operation scripts, we maintain parallel test scripts that:

• Mirror the structure of operational scripts
• Have access to shared test utilities
• Can be run individually or as suites
• Maintain clear relationships with what they test