# GL multi-currency (FX revaluation) — the machine-readable skill contract.
# The human tutorial is posts/enterprise_software/gl/gl-multi-currency.md (narrative that references this).
# check:skills binds the two: the post's `skill:` id must resolve here, its example
# param values must equal these defaults, and its tested badge must match tested_branches.
# Lives at skill/enterprise_software/gl/multi-currency.skill.yaml; served over MCP at
# dodil://skills/gl/multi-currency; executed by `dodil skill scaffold gl/multi-currency`.

id: gl/multi-currency
version: 1.0.0
summary: >-
  Revalue foreign-currency GL balances into the functional currency at period-end, exactly and
  reversibly. Adds fx_rates (from_ccy+to_ccy+rate_date PK, rate DECIMAL(18,6)) and an fx_reval register
  (reval_id PK: account_id, ccy, orig_balance, revalued_balance, gain_loss, rate, period) onto the
  gl/core masters. Take each open foreign-currency ledger balance, compute the unrealized gain/loss =
  (balance x period-end rate) - (balance x prior rate) rounded to functional cents DECIMAL(18,2), and post
  a BALANCED reval journal (FX gain/loss vs the account) that reverses next period so the two periods net
  to 0. Money is DECIMAL(18,2), FX rates are DECIMAL(18,6) (never double); every write is idempotent
  (INSERT ... ON CONFLICT (<pk>) DO UPDATE, or data_table_upsert) so a re-run of the close never
  double-posts. Pure SQL — no Models. Consumes accounts/journals/journal_lines/ledger from gl/core.
module: gl
category: enterprise-software
workflows: [fx-rates, revaluation, reversal]
pillars: [sql]
overlays: [finserv, manufacturing]        # cross-industry base; overlays add regulated-close / cost-accounting diffs

# Binding to the narrative (check:skills verifies these resolve + agree)
post: posts/enterprise_software/gl/gl-multi-currency.md
route: /library/gl-multi-currency

params:
  bucket:              { type: string,  default: gl,      prompt: "Bucket name?" }
  functional_currency: { type: string,  default: USD,     prompt: "Functional (reporting) currency to revalue INTO?" }
  reval_period:        { type: string,  default: "2026-09", prompt: "Period being revalued (YYYY-MM)?" }
  auto_reverse:        { type: boolean, default: true }    # post the reversing mirror journal in the next period
  rate_source:         { type: string,  default: ECB }     # provenance stamped on every fx_rates row

provides:
  tables:  [fx_rates, fx_reval]
  owns_master: []                    # owns the FX rate table + the reval register, not GL masters
  apps:    [gl-reval-engine]         # the pure-SQL period-end revaluation engine (image mode; shown-as-code in phase A)
consumes:
  master:  [{ table: accounts, from: gl/core }, { table: ledger, from: gl/core },
            { table: journals, from: gl/core }, { table: journal_lines, from: gl/core }]

# THE finance-critical correctness rules this skill obeys (stated so builders can't drift):
#  - MONEY = DECIMAL(18,2); FX RATES = DECIMAL(18,6). NEVER double — float drift breaks the gain/loss to the
#    cent. VERIFIED LIVE 2026-09-03: rate 1.095000 and 1.267845 store + read EXACT at (18,6), no float tail.
#  - IDEMPOTENT WRITES = INSERT ... ON CONFLICT (<pk>) DO UPDATE SET c = EXCLUDED.c, or data_table_upsert.
#    A bare re-INSERT of a committed PK raises duplicate-key SQLSTATE 23505 (VERIFIED LIVE on fx_reval) — it
#    is NOT an upsert. A period-end reval re-run (retry / shard replay) MUST leave fx_reval + the reval
#    journal unchanged — it recomputes the same gain/loss and updates in place, never double-posts.
#  - The reval journal obeys the BALANCE INVARIANT: SUM(debit) = SUM(credit) (VERIFIED: 1500.00 = 1500.00).
#    A gain debits the asset (its functional carrying value rose) and credits the FX gain/loss account; a
#    loss mirrors it. The reversing journal (auto_reverse) is the debit<->credit mirror linked by
#    reverses_journal_id, so each account nets to 0.00 across the two periods (VERIFIED live).
#  - The ledger balance of a foreign-currency account is carried in its OWN (transaction) currency; the
#    reval converts it to the functional currency for reporting. gain/loss is ROUNDed to functional cents.

# The ordered scaffold plan. Each step's `tools` must resolve against dodil://commands (check:skills).
steps:
  - id: stub_masters
    title: Stub the masters you consume (standalone — skip if gl/core is present)
    when: standalone
    tools: [data_bucket_create, data_table_create, data_table_upsert]
    detail: >-
      Create bucket {{bucket}} + minimal stubs of the gl/core masters this skill reads: accounts(key
      account_id: name, type, normal_balance, parent_account_id long, currency, active boolean),
      journals(key journal_id: journal_date timestamp, period, source, status, memo, reverses_journal_id
      long), journal_lines(composite key journal_id,line_no: account_id long, debit DECIMAL(18,2),
      credit DECIMAL(18,2), line_memo), ledger(key account_id: balance DECIMAL(18,2), currency,
      as_of timestamp). EVERY non-key column nullable:true (data table create defaults to NOT NULL, else a
      partial seed 500s). Seed a foreign-currency account (1200 Accounts Receivable (EUR), currency EUR) +
      an Unrealized FX Gain/Loss account (4900, revenue/credit, {{functional_currency}}) + a Product Revenue
      account (4100, EUR); post the opening EUR journal (journal 1, posted, period 2026-08: debit 1200
      100000.00, credit 4100 100000.00 — balances in EUR) and materialize the ledger so account 1200 carries
      an OPEN EUR balance of 100000.00. When gl/core is present it owns these masters; skip this step.
  - id: fx_rate_table
    title: Seed the FX rate table (fx_rates, DECIMAL(18,6))
    when: always
    tools: [data_table_create, data_table_upsert]
    detail: >-
      Create fx_rates(composite key from_ccy,to_ccy,rate_date: rate DECIMAL(18,6), source) and upsert one
      row per (currency pair, rate_date): the prior period-end rate (EUR->USD 2026-08-31 = 1.080000) and the
      period-end rate being revalued ({{reval_period}} end: EUR->USD 2026-09-30 = 1.095000), source
      {{rate_source}}. Merge-keyed, so re-seeding a corrected rate is idempotent. FX rates are DECIMAL(18,6)
      (more scale than money, still exact) — VERIFIED LIVE: 1.095000 and a full-precision 1.267845 read back
      exact, no float tail. Rates are quoted from_ccy->to_ccy where to_ccy is the {{functional_currency}}.
  - id: revaluation
    title: Revalue the open FX balance + post the balanced reval journal (fx_reval)
    when: always
    tools: [data_table_create, data_pg, data_table_upsert, data_sql]
    detail: >-
      Create fx_reval(key reval_id: account_id long, ccy, orig_balance DECIMAL(18,2), revalued_balance
      DECIMAL(18,2), gain_loss DECIMAL(18,2), rate DECIMAL(18,6), period). For each open foreign-currency
      ledger balance, JOIN fx_rates for the prior + period-end rates and compute orig_balance =
      ROUND(balance x prior_rate, 2), revalued_balance = ROUND(balance x new_rate, 2), gain_loss =
      revalued_balance - orig_balance (functional cents). Write fx_reval with INSERT ... SELECT ... ON
      CONFLICT (reval_id) DO UPDATE so a re-run is idempotent. VERIFIED LIVE: AR (1200) EUR 100000.00 ->
      orig 108000.00 (@1.080000), revalued 109500.00 (@1.095000), gain_loss = 1500.00 exact. Then post the
      BALANCED reval journal (journal 2, source fx-reval, status posted, period {{reval_period}}): a GAIN
      debits the account 1200 (its functional carrying value rose) and credits Unrealized FX Gain/Loss
      (4900) for the gain_loss; a loss mirrors it. SUM(debit) = SUM(credit) = 1500.00, balance 0.00
      (VERIFIED). A bare re-INSERT of fx_reval reval_id=1 raises SQLSTATE 23505 (VERIFIED) — recompute must
      use ON CONFLICT.
  - id: reversal
    title: Reverse the reval into the next period (mirror journal)
    when: { auto_reverse: true }
    tools: [data_table_upsert, data_sql]
    detail: >-
      An unrealized reval is an estimate at a point in time, so it reverses at the start of the next period
      and the fresh balance is revalued anew. Post the reversing mirror journal (journal 3, status posted,
      period 2026-10, reverses_journal_id = 2) with debit<->credit swapped from the reval journal: credit
      account 1200 1500.00, debit Unrealized FX Gain/Loss (4900) 1500.00. It BALANCES on its own
      (SUM(debit)-SUM(credit) = 0.00), and over the two journals (2 + 3) each affected account nets to 0.00
      (VERIFIED LIVE: account 1200 net 0.00, account 4900 net 0.00) — the reval leaves no permanent trace
      once the receivable settles. auto_reverse=false leaves the reval standing (a period-end-only balance
      adjustment) and the next reval computes against the standing carrying value instead.

# The Q&A an agent runs with the user (was `## Customize`). Each answer's effect = the best-practice encoding.
customize:
  - param: functional_currency
    ask: "What functional (reporting) currency do you revalue foreign balances INTO?"
    effects:
      USD: >-
        every foreign-currency ledger balance is converted to this currency at the period-end rate; the
        gain/loss is expressed + rounded in it (DECIMAL(18,2)). fx_rates are quoted from_ccy -> this currency.
        Inherit it from gl/core — set it once there and every reval uses it.
  - param: reval_period
    ask: "Which accounting period are you revaluing (YYYY-MM)?"
    effects:
      default: >-
        the reval reads the period-end rate for this period (e.g. 2026-09 -> the 2026-09-30 rate) and the
        prior period-end rate, and stamps fx_reval.period + the reval journal's period. Default 2026-09.
        Change it and the reval re-derives against that period's rate — the engine reads the rate table,
        never a hard-coded rate.
  - param: auto_reverse
    ask: "Auto-reverse the revaluation at the start of the next period?"
    effects:
      "true": >-
        posts the reversing mirror journal (reverses_journal_id set) in the next period so the two periods
        net to 0.00 — the standard treatment for UNREALIZED reval, re-struck fresh each period. ## Test
        asserts the net-zero across periods 2026-09 + 2026-10.
      "false": >-
        the reval stands as a period-end carrying adjustment; the next reval computes the incremental
        gain/loss against the standing revalued balance instead of reversing first.
  - param: rate_source
    ask: "What is the source/provenance stamped on each FX rate?"
    effects:
      ECB: >-
        every fx_rates row carries this source (e.g. ECB, your treasury desk, a rate feed) for audit — a
        controller signing the reval can trace each rate to where it came from. Default ECB.

# What proves it (was `## Test`) — assertions the CI replay runs on the default branch.
# Live-validated 2026-09-03, org IHDIASH, bucket glb-fx-7k2q (torn down). Real returned values inline.
tests:
  - "fx_rates seeded; rate reads EXACT at DECIMAL(18,6): EUR->USD 2026-08-31 = 1.080000, 2026-09-30 = 1.095000; a full-precision GBP->USD 2026-09-30 = 1.267845 (no float tail)"
  - "reval of AR (1200), EUR ledger balance 100000.00: orig_balance = ROUND(100000.00 x 1.080000, 2) = 108000.00, revalued_balance = ROUND(100000.00 x 1.095000, 2) = 109500.00, gain_loss = 1500.00 (exact DECIMAL(18,2) string, no float tail)"
  - "reval journal (2) balances: SUM(debit) = SUM(credit) = 1500.00, SUM(debit)-SUM(credit) = 0.00 (debit AR 1200, credit Unrealized FX Gain/Loss 4900)"
  - "reversal journal (3, reverses_journal_id = 2) is the debit<->credit mirror; over journals 2 + 3 each account nets to 0.00 — account 1200 net 0.00, account 4900 net 0.00; the mirror balances on its own (0.00)"
  - "idempotent recompute: a bare re-INSERT of fx_reval reval_id=1 raises SQLSTATE 23505; the INSERT ... SELECT ... ON CONFLICT (reval_id) DO UPDATE re-run leaves fx_reval = 1 row, gain_loss = 1500.00, reval journal lines = 2, journal balance 0.00 (no double-post)"
  - "data connect {{bucket}} -> prints pg.uk-lon-1.dodil.io:5432/{{bucket}} + bolt + grpc"

tested_branches:
  - { functional_currency: USD, reval_period: "2026-09", auto_reverse: true, rate_source: ECB, tested_at: 2026-09-03 }   # anchor — full replay incl. rate exactness, gain/loss 1500.00, reversal nets to 0.00, idempotent recompute
  - { auto_reverse: false, tested_at: 2026-09-03 }                                                                       # reval-stands branch (same DDL + reval; no reversing journal) — derived

# Ship-it contract.
# NOTE (2026-09-03): the reval logic is pure data ops (data_table_create / data_table_upsert / data_pg /
# data_sql) — an author-time period-end run. The stateful piece, when you run it on a schedule, is the
# gl-reval-engine: an IMAGE-mode Ignite app (Lane B: a Dockerfile + --dockerfile-path, Kaniko build-on-deploy,
# HTTP server on $PORT with GET /healthz + POST /revalue) that recomputes fx_reval + posts the reval journal
# for every open foreign-currency balance over the pg wire. It is PURE SQL (deterministic rate math, no
# embeddings), so its service account needs ONLY k3.editor + ignite.app-developer (NO ignite.model-user).
# The /revalue recompute MUST write with INSERT ... ON CONFLICT (<pk>) DO UPDATE (a bare re-INSERT of a
# committed PK raises duplicate-key 23505 — VERIFIED live on fx_reval) and retry SerializationFailure. There
# is no server-side scheduler — run it from your own period-end cron hitting POST /revalue, or pin an
# always-on loop warm (--reserved 1 --max-replicas 1). DODIL_SERVICE_ACCOUNT_ID = the cli-... serviceAccountId
# from `auth service-account create`, NOT the uuid. Shown-as-code in phase A (the base reval is validated live
# via the CLI/MCP over the pg wire); the dedicated live-deploy stress test runs later.
deploy:
  app: gl-reval-engine
  mode: image            # Dockerfile + --dockerfile-path (NOT --runtime python / compile mode)
  entrypoint: "http server (0.0.0.0:$PORT, /healthz + POST /revalue)"
  status: shown-as-code  # base reval validated live over the pg wire; the live-deploy stress test runs later
  service_account:
    roles: [k3.editor, ignite.app-developer]   # pure SQL — NO ignite.model-user (deterministic rate math, no Models)
    note: >-
      DODIL_SERVICE_ACCOUNT_ID = the cli-... serviceAccountId printed by `auth service-account create`,
      NOT the internal uuid (the uuid fails client_credentials with invalid_client).
  data_plane: "pg-wire pg.uk-lon-1.dodil.io:5432 (db = bucket, user = token; psycopg; idempotent write = INSERT ... ON CONFLICT (<pk>) DO UPDATE, a bare re-INSERT of a committed PK raises duplicate-key 23505) — no k3.dodil.io HTTP API"
  scheduling: "no server-side scheduler — own period-end cron hitting POST /revalue, or an always-on loop pinned warm (--reserved 1 --max-replicas 1)"
  env: [DODIL_SERVICE_ACCOUNT_ID, DODIL_SERVICE_ACCOUNT_SECRET, BUCKET, FUNCTIONAL_CURRENCY, REVAL_PERIOD, AUTO_REVERSE]
  models: []             # pure SQL — deterministic rate math, no Models call