payroll/migrations.py

"""Payroll schema migrations.

This is an aiolabs-original extension, not a fork of an upstream LNbits one,
so there is no upstream `migrations.py` to stay byte-identical with and the
`migrations_fork.py` split does not apply here — ordinary migrations are
correct. They are still written so a re-run cannot wedge the schema: the
`dbversions` bookkeeping lives in the core DB while the tables live in
`ext_payroll`, and those two writes are not atomic, so a crash between them
replays the migration on the next boot.

Tables live in `ext_payroll.sqlite3` (SQLite) or the `payroll` Postgres
schema.
"""


async def m001_initial(db):
    """Payroll contracts — the standing "pay X this much, this often" rows."""

    await db.execute(f"""
        CREATE TABLE payroll.contracts (
            id TEXT PRIMARY KEY,
            employee_id TEXT NOT NULL,
            employee_username TEXT NOT NULL DEFAULT '',
            employee_wallet TEXT NOT NULL,
            source_wallet TEXT NOT NULL,
            amount REAL NOT NULL,
            currency TEXT NOT NULL DEFAULT 'sat',
            frequency TEXT NOT NULL DEFAULT 'monthly',
            start_date TEXT NOT NULL,
            total_periods INTEGER,
            periods_done INTEGER NOT NULL DEFAULT 0,
            label TEXT NOT NULL DEFAULT '',
            memo TEXT NOT NULL DEFAULT '',
            backfill BOOLEAN NOT NULL DEFAULT false,
            status TEXT NOT NULL DEFAULT 'active',
            created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now},
            updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
        );
        """)

    # The scheduler's hot path is "every contract still eligible to pay".
    # NOTE: the schema qualifier goes on the INDEX name, not the table —
    # SQLite attaches the extension DB as schema `payroll`, and
    # `CREATE INDEX ON schema.table` is a syntax error there.
    await db.execute("CREATE INDEX payroll.idx_contracts_status ON contracts (status);")
    # Employee-facing lookups ("show me my payroll lines") filter by account.
    await db.execute(
        "CREATE INDEX payroll.idx_contracts_employee ON contracts (employee_id);"
    )


async def m002_payouts(db):
    """The payout ledger — one row per attempt at one period.

    Rows are self-describing (they carry the contract terms in force at the
    time) so a payout still reads correctly after its contract is edited or
    deleted. There is deliberately no foreign key to `contracts` for the
    same reason: deleting a contract must not take its history with it.
    """

    await db.execute(f"""
        CREATE TABLE payroll.payouts (
            id TEXT PRIMARY KEY,
            contract_id TEXT NOT NULL,
            period_index INTEGER NOT NULL,
            payday TEXT NOT NULL,
            status TEXT NOT NULL,
            attempt INTEGER NOT NULL DEFAULT 1,
            amount_msat {db.big_int},
            amount REAL NOT NULL DEFAULT 0,
            currency TEXT NOT NULL DEFAULT 'sat',
            employee_id TEXT NOT NULL DEFAULT '',
            employee_wallet TEXT NOT NULL DEFAULT '',
            source_wallet TEXT NOT NULL DEFAULT '',
            payment_hash TEXT,
            detail TEXT NOT NULL DEFAULT '',
            created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
        );
        """)

    # Counting a period's prior failures runs on every failed attempt.
    await db.execute(
        "CREATE INDEX payroll.idx_payouts_period "
        "ON payouts (contract_id, period_index);"
    )
    # The employee-facing payslip view filters on the destination wallet.
    await db.execute(
        "CREATE INDEX payroll.idx_payouts_employee_wallet "
        "ON payouts (employee_wallet);"
    )


async def m003_pricing_mode(db):
    """Let a back-dated period be priced at the day it was due.

    Until now every payout converted at whatever the rate was when it
    settled, which silently misprices a period paid late. The contract now
    carries how to convert, and each payout records the rate it actually
    used plus where that rate came from — so a figure in the ledger can be
    explained months later instead of merely trusted.

    Defaults preserve existing behaviour for anything already in flight:
    every live payday is today or ahead, and for those all three modes agree
    on using LNbits' own live pricing.
    """
    await db.execute(
        "ALTER TABLE payroll.contracts "
        "ADD COLUMN pricing_mode TEXT NOT NULL DEFAULT 'payday';"
    )
    await db.execute("ALTER TABLE payroll.contracts ADD COLUMN manual_rate REAL;")
    await db.execute("ALTER TABLE payroll.payouts ADD COLUMN rate REAL;")
    await db.execute(
        "ALTER TABLE payroll.payouts ADD COLUMN rate_source TEXT NOT NULL DEFAULT '';"
    )


async def m004_paused_reason(db):
    """Tell an automatic pause apart from a deliberate one.

    Without this they share a recovery path, and resuming a contract that
    payroll paused because it could not price a backlog silently writes that
    backlog off — the operator fixes the cause, clicks resume, and the money
    owed quietly disappears while the contract looks healthy again.
    """
    await db.execute(
        "ALTER TABLE payroll.contracts "
        "ADD COLUMN paused_reason TEXT NOT NULL DEFAULT '';"
    )