be9f14ce34
A small helpdesk system: an office Pixel running a patched GrapheneOS Dialer answers technician calls, records both call legs as separate channels, and a Ruby backend transcribes them through Whisper and files an AI summary against the caller. Squashed to a single commit for sharing. No credentials are included; secrets live outside the repo in /etc/helpdesk/env on the server or a gitignored .claude/env.local locally. See .claude/env.local.example for the shape. Start at README.md, then docs/architecture.md.
79 lines
4.3 KiB
Ruby
79 lines
4.3 KiB
Ruby
# frozen_string_literal: true
|
|
# One-shot, IDEMPOTENT importer: a JSON-store snapshot -> PostgreSQL (the migration path off the JSON store).
|
|
# - copies the JSON file to a timestamped backup BEFORE any write (never mutates the source).
|
|
# - upserts on preserved IDs (re-runnable; a half-import needs no cleanup).
|
|
# - fixes sequences past the snapshot's @seq counters.
|
|
# - verifies: row counts per entity vs JSON object counts, FK integrity, spot-check; nonzero exit on mismatch.
|
|
# Usage: HELPDESK_DATABASE_URL=... ruby db/import_json.rb [/path/to/state.json]
|
|
require "json"
|
|
require "fileutils"
|
|
require_relative "../lib/helpdesk/domain"
|
|
require_relative "../lib/helpdesk/pg_store"
|
|
|
|
url = ENV["HELPDESK_DATABASE_URL"] or abort "set HELPDESK_DATABASE_URL"
|
|
src = ARGV[0] || ENV["HELPDESK_STATE"] || File.join(Dir.home, ".config", "helpdesk", "state.json")
|
|
abort "source snapshot not found: #{src}" unless File.exist?(src)
|
|
|
|
# 1) backup the source (never touched again)
|
|
bkp_dir = File.join(File.dirname(src), "import-backups")
|
|
FileUtils.mkdir_p(bkp_dir)
|
|
bkp = File.join(bkp_dir, "#{File.basename(src)}.#{Process.clock_gettime(Process::CLOCK_REALTIME).to_i}")
|
|
FileUtils.cp(src, bkp)
|
|
puts "backed up #{src} -> #{bkp}"
|
|
|
|
raw = JSON.parse(File.read(src))
|
|
seq = raw["seq"] || {}
|
|
json = Helpdesk::Store.load(src) # symbol-keyed records
|
|
pg = Helpdesk::PgStore.new(url)
|
|
|
|
# 2) import (idempotent upsert on preserved IDs), in FK-safe order
|
|
counts = {}
|
|
json.orgs.values.each { |o| pg.put_org(o) }; counts[:orgs] = json.orgs.size
|
|
json.campaigns.values.each { |c| pg.put_campaign(c) }; counts[:campaigns] = json.campaigns.size
|
|
json.people.values.each { |p| pg.put_person(p) }; counts[:people] = json.people.size
|
|
json.dids.values.each { |d| pg.put_did(d) }; counts[:dids] = json.dids.size
|
|
json.phone_numbers.values.each { |pn| pg.put_phone_number(pn) }; counts[:phone_numbers] = json.phone_numbers.size
|
|
json.events.values.each { |ev| pg.put_event(ev) }; counts[:events] = json.events.size
|
|
json.quarantine.each { |uuid, h| pg.put_quarantine(uuid, h) }; counts[:quarantine] = json.quarantine.size
|
|
puts "imported: #{counts.inspect}"
|
|
|
|
# 3) fix sequences past the snapshot counters (so next_id never collides with imported IDs)
|
|
{ org: "org_id_seq", person: "person_id_seq", campaign: "campaign_id_seq",
|
|
event: "event_id_seq", ledger: "ledger_seq" }.each do |kind, s|
|
|
n = (seq[kind.to_s] || 0).to_i
|
|
if n >= 1 then pg.db.run("SELECT setval('#{s}', #{n}, true)") # next nextval -> n+1 (matches @seq[kind]+1)
|
|
else pg.db.run("ALTER SEQUENCE #{s} RESTART") # unused counter -> first nextval = 1
|
|
end
|
|
end
|
|
puts "sequences set past @seq #{seq.inspect}"
|
|
|
|
# 4) verify
|
|
fail = 0
|
|
pg2 = Helpdesk::PgStore.new(url) # fresh reload straight from the DB
|
|
db_counts = { orgs: pg2.orgs, campaigns: pg2.campaigns, people: pg2.people, dids: pg2.dids,
|
|
phone_numbers: pg2.phone_numbers, events: pg2.events, quarantine: pg2.quarantine }
|
|
db_counts.each do |k, coll|
|
|
ok = coll.size == counts[k]
|
|
fail += 1 unless ok
|
|
puts " #{ok ? 'ok ' : 'BAD'} count #{k}: pg=#{coll.size} json=#{counts[k]}"
|
|
end
|
|
# FK integrity: events.person_id that don't resolve to a person (expected: some dangle after delete_person)
|
|
dangling = pg2.events.values.count { |e| e[:person_id] && !pg2.people.key?(e[:person_id]) }
|
|
puts " info dangling event.person_id (expected, kept intentionally): #{dangling}"
|
|
# spot-check up to 5 random events field-by-field vs JSON
|
|
sample = json.events.values.sample([5, json.events.size].min)
|
|
sample.each do |jev|
|
|
pev = pg2.events[jev[:id]]
|
|
# compare via JSON normalize (nil vs missing key tolerant)
|
|
jn = JSON.parse(JSON.generate(jev.reject { |_k, v| v.nil? }))
|
|
pn = JSON.parse(JSON.generate((pev || {}).reject { |_k, v| v.nil? }))
|
|
if jn == pn then puts " ok spot-check event #{jev[:id]}"
|
|
else fail += 1; puts " BAD spot-check event #{jev[:id]}\n json:#{jn.inspect[0,200]}\n pg: #{pn.inspect[0,200]}"
|
|
end
|
|
end
|
|
# call_id uniqueness sanity
|
|
dupes = pg2.db["SELECT call_id, count(*) c FROM events GROUP BY call_id HAVING count(*)>1"].all
|
|
fail += dupes.size; puts(dupes.empty? ? " ok call_id unique" : " BAD duplicate call_ids: #{dupes.inspect}")
|
|
|
|
puts(fail.zero? ? "\nIMPORT VERIFIED OK" : "\nIMPORT FAILED (#{fail} mismatches)")
|
|
exit(fail.zero? ? 0 : 1)
|