Computing Policy State From Dates

Goal

You become able to compute a policy's state directly from the payment history and the as-of date instead of reading it from a stored column, and to prove with data why the same query gives different answers depending on the as-of date and the known-as-of date.

Why it matters

When you get a report at an insurer that "lapse was not processed," you start by looking at the logs. But the cause of that report is usually not in the logs. The status column is an opinion that last night's batch computed and put in, and the facts are in the payment history. The place where the two split is exactly where reports come in.

And lapse is not an event but a computed result. No table has a record saying "it lapsed"; the lapse date comes out of the unpaid installment and the grace rule. The rule used in this lab is as follows.

납입기일        해당월 10일
유예 만료일     납입 해당월의 다음 달 말일   (2026-02 회차 → 2026-03-31)
실효일          유예 만료일 다음 날          (2026-04-01)

Two priorities come with it. Termination and maturity events come before the unpaid calculation. You do not work out lapse for a policy that has already ended. And only installments after reinstatement go into the lapse judgment. Reinstatement does not mean the overdue money was paid but that the reinstatement application and review were gone through again, so unpaid installments before it no longer cut off the policy.

Finally, there is one more time axis. The payment-receipt correction that came in on July 5 changes the state on June 30. Even with the same as-of date, the answer differs depending on which facts known as of when it was computed from.

Steps

Save the generator in the example on the right as /root/ins/make_policy.py and run it with python3 make_policy.py. It creates 40 policies, 480 payment installments, 7 policy events, and 3 payment-receipt corrections.
For each policy that has an unpaid installment, compute the first unpaid installment and the lapse date, and write them to /root/ins/lapse_calc.csv with the header policy_no,first_unpaid_ym,due_date,grace_end,lapse_date.
Compute the policy state as of 2026-06-30 and write it to /root/ins/status_20260630.csv as policy_no,calc_status. All 40 policies must be included.
Compare policy.status with the step 3 result and write only the mismatched policies to /root/ins/mismatch.csv as policy_no,stored_status,calc_status.
Run the same query with as-of date 2026-09-30 too, and write five lines to /root/ins/drift.txt: asof_20260630_normal=, asof_20260630_lapsed=, asof_20260930_normal=, asof_20260930_lapsed=, moved=.
Holding the as-of date fixed at 2026-06-30, compute with known-as-of dates 2026-06-30 and 2026-08-31 respectively, and write four lines to /root/ins/restate.txt: known_20260630_lapsed=, known_20260831_lapsed=, restated=, restated_policies=.
Create a status_snapshot(as_of_date, known_as_of, policy_no, calc_status) table inside policy.db and put in the two results from step 6, 40 rows each, 80 rows in all.
Write a report in /root/ins/policy_report.md with four sections: ## 확인한 것, ## 상태가 어긋난 계약, ## 기준일과 인지일, ## 권고 (the four Korean section titles mean: what was checked, policies whose status is off, as-of date and known-as-of date, and recommendations).

Notes

If you read with sqlite3 -readonly <파일> "<쿼리>" (the placeholders are the file and the query), you can prevent accidental writes.
Get the month end with date('2026-02-01','+2 months','-1 day'). The last day differs by month, so do not add by hand.
Common mistake 1: if you extract with .mode csv, Korean values get double quotes. If you use .mode list and .separator , it comes out clean (the grader accepts both).
Common mistake 2: counting the grace period end date itself as lapse. For as-of date 2026-06-30, a policy whose grace end date is 2026-06-30 is still normal.
Common mistake 3: putting unpaid installments from before reinstatement into the lapse judgment. Then all 3 reinstated policies come out as lapsed.
Common mistake 4: in step 8, reading the numbers by eye and copying them by hand. Numbers copied by hand are always wrong by one digit.

Generate the policy data

Save the generator in the example on the right as /root/ins/make_policy.py and run it with python3 make_policy.py. It creates 40 policies, 480 payment installments, 7 policy events, and 3 payment-receipt corrections.

Save the generator in the lab instructions as /root/ins/make_policy.py and run it with python3. If you edit it by hand, the numbers in the later steps all go off.

Compute the grace period and the lapse date

For each policy that has an unpaid installment, compute the first unpaid installment and the lapse date, and write them to /root/ins/lapse_calc.csv with the header policy_no,first_unpaid_ym,due_date,grace_end,lapse_date.

An unpaid installment is a row where paid_date is NULL. The grace period end date is the last day of the month after the month the payment belongs to, and the lapse date is the day after that. Try attaching '+2 months' and '-1 day' to the sqlite3 date() function.

Compute the policy state as of the as-of date

Compute the policy state as of 2026-06-30 and write it to /root/ins/status_20260630.csv as policy_no,calc_status. All 40 policies must be included.

There is a priority. Ending events such as termination and maturity come first, and lapse due to unpaid premiums comes next. And only installments after reinstatement should go into the lapse judgment.

Find the policies that disagree with the status column

Compare policy.status with the step 3 result and write only the mismatched policies to /root/ins/mismatch.csv as policy_no,stored_status,calc_status.

Match policy.status against the state you computed in the previous step by policy number and keep only the ones that differ. Note that the mismatches are not in just one direction.

Move the as-of date and see the numbers split

Run the same query with as-of date 2026-09-30 too, and write five lines to /root/ins/drift.txt: asof_20260630_normal=, asof_20260630_lapsed=, asof_20260930_normal=, asof_20260930_lapsed=, moved=.

Use the query you built in step 3 as it is and change only the date. If you write the query anew, a typo hides in between, and the typo is always in the side that runs.

Change the known-as-of date to expose the retroactive correction

Holding the as-of date fixed at 2026-06-30, compute with known-as-of dates 2026-06-30 and 2026-08-31 respectively, and write four lines to /root/ins/restate.txt: known_20260630_lapsed=, known_20260831_lapsed=, restated=, restated_policies=.

Fix the as-of date at 2026-06-30. A correction that came in after the known-as-of date was a fact not known then, so you must compute with the payment_correction's old_paid_date restored.

Fix it into a reproducible snapshot

Create a status_snapshot(as_of_date, known_as_of, policy_no, calc_status) table inside policy.db and put in the two results from step 6, 40 rows each, 80 rows in all.

Storing the query does not make it reproducible. Put the computed result into a table together with the as-of date and the known-as-of date. You can load a CSV into a table with sqlite3's .import.

Write a report the person in charge will read

Write a report in /root/ins/policy_report.md with four sections: ## 확인한 것, ## 상태가 어긋난 계약, ## 기준일과 인지일, ## 권고 (the four Korean section titles mean: what was checked, policies whose status is off, as-of date and known-as-of date, and recommendations).

It needs four sections. Do not copy the numbers by hand; read them from the files the earlier steps left and fill them in. And this data contains insured-person identifiers.