Jakob Wennberg
38f5d9812e
* feat(receipt-hunt): nightly matcher pairing unbooked purchases with held receipts Stages an attach_document_to_transaction proposal for every unbooked card purchase whose receipt the company already holds, so the underlag is attached before the transaction is booked and the gap never forms. When the user later books it, categorize-core.ts propagates the document onto the new verifikat through the matched_transaction_id link the executor writes. Deliberately scoped to UNBOOKED transactions. The posted-verifikat backlog is 96% imported history whose originals live in the previous system, so it stays a pull (the verifikat_missing_document worklist) rather than a nightly push. Ranking reuses scoreUnderlagCandidates; the pool is loaded once per company instead of per transaction, which removes both the N+1 and the newest-50 truncation a per-transaction lookup imposes on a deep backlog. Five guards, each mutation-tested: a confidence floor above the shared candidate floor, an ambiguity margin so two equally-good receipts are left to the picker rather than coin-flipped, one-receipt-one-purchase, one live proposal per purchase, and permanent suppression of pairs a human rejected. Suppression is derived from pending_operations history rather than a new table: terminal rows are immutable and a rejection is already the durable "no". Runs 05:30 UTC, after the 05:00 bank sync. Gated on RECEIPT_HUNT_COMPANY_IDS, which hunts nobody when unset so enabling it stays a deliberate act. No migration, no journal writes, no UI: proposals land in the existing Granskning queue. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * feat(receipt-hunt): dry-run mode for provkörning against a real ledger Returns the pairings a run would stage without writing any of them, so a company can see tonight's proposals before they reach the granskningskö and so the matcher can be validated against production data without staging an operation. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(matching): fold Swedish bank descriptors so receipts reach their purchases calculateMerchantSimilarity compared raw bank descriptors, so a receipt from "Alviks kött och fisk" scored 0.125 against the bank's own row for it, "Alviks koett och fisk K3667 Kortköp/uttag" — an öre-exact pair no threshold could reach. Adds normalizeForMatch, used for similarity only, which folds what the card rails add and never changes identity: the K#### token, Kortköp/uttag verbs, a leading "Kortköp YYMMDD", trailing /YY-MM-DD dates, reference numbers glued to the name, domain wrappers, legal forms, and the three ways banks mangle Swedish letters (ö, transliterated "oe", and ?? mojibake). Processor markers become spaces because the merchant sits before the star in GOOGLE*PLAY and after it in K*IKEA GALLE. Token-subset containment is scored level with substring containment so a receipt's legal name matches the bank's trading name. normalizeMerchantName is left byte-identical and now documents why: it is a transitive input to categorization_templates.counterparty_name, a persisted UNIQUE key with a hand-written SQL mirror the ledger-context RPC recomputes at query time. Changing it would make stored keys stop equalling computed ones, so the konteringskarta join misses and insertOrUpdateTemplate inserts a second row per merchant instead of migrating the occurrence counts. Aggressive folding is safe because it is applied to both sides of every comparison, so an over-eager fold still matches; the risk is collision between different merchants, which the new tests guard. Measured on 27 receipt/transaction pairs humans actually confirmed in production: recall 27/27, and 0/7 false positives on deliberately similar but distinct merchants. Full unit suite unchanged (13,004 passing), including the 22 string pins on the frozen key path. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * feat(mail): read-only Gmail connector so receipts are found without forwarding Forwarding was the only way a receipt reached Accounted, and it is both unpopular (97% of companies with the problem have never used their inbox address) and fragile: Arcim's own forward has been off for weeks and nobody noticed. This lets the hunt look in the mailbox instead. Scope is gmail.readonly and nothing else. It can search and download attachment bytes, and it structurally cannot send, modify or delete: the promise the consent screen makes is enforced by the grant, not by our code being careful. The consequence is deliberate: the agent can prepare a forward for a portal-link receipt but can never send one itself. Query-then-classify, never sync. For each unexplained purchase we run a provider-side search in a -3/+10 day window, pull metadata for a handful of hits, and keep nothing. No mailbox is mirrored and no message body is stored, which is what keeps this inside Google's Limited Use terms and GDPR data minimisation. Mail is searched only for purchases Underlag could not already explain, so a receipt we already hold never costs a mailbox read. The query ORs merchant against amount rather than requiring both: demanding both misses every rebrand and reseller (Anthropic bills as Claude), while the amount alone is a strong filter inside two weeks. mail_connections is service-role only with RLS enabled and zero policies, because the row holds a live refresh token and RLS cannot hide a column. Uniqueness is (company, provider, address) so a second mailbox is additive and a reconnect updates in place. Tokens are AES-256-GCM under their own key by preference, since a mail grant reads correspondence rather than backups. Core reaches the extension through a registered service, mirroring lib/email/service.ts, so lib/receipt-hunt never imports from @/extensions and a zero-extension build still compiles. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * feat(mail): connect UI and ingest, making the hunt reach into the mailbox Two halves that together make the connector usable. Ingest (lib/receipt-hunt/ingest.ts, core): fetches the attachment, files it as a document and an inbox item with source 'mail_hunt', then stages the pairing. It lives in core because it writes documents and inbox items, and an extension may never import another extension; the mail extension only ever hands over bytes. No re-matching for a hunted receipt: it was fetched WHILE SEARCHING for a specific purchase, so the pairing is known by construction. The search is a deliberately broad OR query, which is exactly why the proposal still goes to a human with the mailbox, sender and subject written on it rather than being linked automatically. Provenance goes in channel_context, never extracted_data, because retrying extraction overwrites extracted_data wholesale and the record of which mailbox a receipt came from has to survive that. A partial unique index on (company_id, channel_context->>'mail_message_id') makes re-runs and the same receipt arriving in two mailboxes idempotent, and a 23505 is treated as success rather than an error. Guards, both mutation-tested: a duplicate message costs no provider call, and an oversized attachment is skipped rather than stored. One unreadable attachment falls through to the next and never aborts a night's hunt. UI: /settings/mail lists connected mailboxes with their health, connects a new one through a user-gesture tab (opened before the await, so popup blockers do not eat it), and disconnects behind a ConfirmDialog that states the outcome up front, including that already-approved receipts stay because they belong to the bookkeeping now. Strings in sv and en; the read-only promise is spelled out on the page rather than buried in a consent screen. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(mail): renumber migrations to clear a version collision on main 20260806150000 was already taken by preserve_preset_committed_at, and woocommerce_connections plus enforce_balance_on_posted_insert landed after this branch was cut. Two files sharing a version breaks every fresh database, which only shows up on a clean setup rather than on an already-migrated one. Applied to prod under the new versions (20260807090000 / 20260807090100), so schema_migrations matches these filenames exactly. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(receipt-hunt): make the mailbox search actually able to find an underlag A provkörning against a real ledger returned the same seven unrelated messages for every purchase, all reporting no attachments. Three separate causes, each fixed and pinned: 1. `getMessageSummary` asked Gmail for `format=metadata`, which returns headers and omits `payload.parts` entirely. Every message therefore looked attachment-free, `bodyIsReceipt` was always true, and the `found.find(c => c.attachmentIds.length > 0)` guard in the hunt could never select anything: the feature could not file a single receipt. Gmail has no format that returns MIME structure without the body, so the body now comes down the wire; it is read for nothing and stored nowhere. 2. The bank's description is not a merchant name. "Lön Juli Jakob Överföring via internet" searched for "Juli" and matched most of the mailbox. Month names and payment-rail boilerplate are now stopwords. 3. Salary and tax runs are a company's largest outgoing rows, so they consumed the whole search budget hunting receipts that cannot exist. `canHaveEmailReceipt` skips them for the mail leg only. Deliberately narrow: a supplier invoice paid over bankgiro does arrive by mail, and an "Utlägg" reimbursement has a real receipt behind it. Measured on the same ledger: 22 hits, 0 with attachments, 0 ingestable -> 4 hits, all with attachments, 3 of 4 correct (Elgiganten, Sting, Anthropic). The fourth matched a Stockholm billing address, which is why every proposal still waits for a human. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * feat(receipt-hunt): let a model resolve merchants and pick the receipt The keyword hunt was failing for reasons regex tuning cannot reach, all measured against a real mailbox rather than assumed: - `from:anthropic.com` returns 0. Receipts arrive here by being forwarded, so the sender is the user, not the vendor. - The exact charged amount returns 0. The bank posts a converted SEK figure that appears nowhere in a USD receipt. - A date window around the purchase returns 0, while the same merchant search without one returns 10+. A forward is stamped when it was forwarded, sometimes months later. So the query now searches merchant names across the whole mailbox, and precision is restored by judgement rather than by syntax. Two model calls per run, both through forced tool use so the reply is a shape and not prose to be parsed: 1. `planMerchantGroups` resolves bank descriptors to merchants and merges repeats. Six Anthropic subscriptions become one search and one decision instead of six of each. 2. `assignReceipts` decides which mail, and which attachment on it, is the receipt for which charge, and says why in a sentence the reviewer reads. The attachment, not the message, is the unit of an underlag: a single forward routinely carries receipts for several purchases ("Fwd: Kvitton februari" has five). Migration 20260807103000 moves the dedupe key from message to message+attachment, with a backfill, because the old index would have silently blocked every receipt after the first in a forward. The model may not produce any number that reaches the ledger. It returns ids, a confidence and a reason; amounts, dates and the write stay in deterministic code. Its answer is validated, not trusted: an unknown message id, an invented filename or a low confidence drops the pairing, and any failed call proposes nothing at all. Every result still waits for a human. Measured on the same ledger: 0 receipts that could ever be filed -> 3 correct pairings (Elgiganten, Sting office invoice, Anthropic), each with a stated reason. The five remaining Anthropic charges are dated after 2026-06-15, when forwarding to the connected mailbox stopped; the model declined them correctly. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * refactor(receipt-hunt): amount first, and drop the confidence scoring Three findings from how others build this, applied. Production email search (Superhuman, Haystack 2026) reports that recall comes from loosening retrieval and letting the model filter downstream, not from tightening the query. Retrieval depth per merchant 12 -> 25, and purchases the planner cannot name a merchant for are now searched by amount alone instead of skipped: a line like "1260525758758 Europabetalning" identifies no merchant but is a real supplier payment whose invoice may carry exactly that total. Reconciliation engines weight amount far above date (Midday: 35% vs 5%) because banks post late while amounts do not drift. The Gmail query now leads with the amount and ORs the merchant, rather than dropping the amount whenever a merchant alias exists. Still an OR: a receipt billed in USD never contains the SEK figure the bank charged. The confidence score is gone entirely. Research on verbalised confidence finds it badly calibrated, clustered on round-number anchors and barely better than chance at separating a model's own right answers from its wrong ones. That matched what this ran into: the model anchored on 0.6 / 0.7 / 0.75 / 0.9, and the 0.7 threshold discarded two correct pairings. It is replaced by an observation rather than a self-assessment, whether the charged amount is actually visible in the mail, which is what a reviewer checks first and what sorts the queue. Also fixes a real defect the run exposed: the one-file-one-purchase guard only held within a merchant group, so when the planner split one landlord into "Sting" and "Kontorsplatser" both 15 000 kr charges were assigned the same invoice. A file is now claimed once per run, which is the duplicate underlag BFL forbids. Measured on the same ledger: 3 -> 5 pairings, no duplicate. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * refactor(receipt-hunt): harvest receipts, then pair them on the amount Splits the mailbox leg in two along the line of what each side can actually know. The model was being asked which purchase a mail belonged to. Deciding that needs the amount; the amount lives inside the PDF; a Gmail preview essentially never shows it. Measured over a real mailbox, every single pairing came back "belopp ej synligt": it was answering without the deciding evidence, which is why it declined five of six repeat subscriptions and why two correct pairings sat just under a threshold. Now it answers only what a subject, a sender and a preview line support: is this mail an underlag, and which attachment is it. Then the receipt is fetched, the extraction that already runs on document.uploaded reads its amount, date and vendor, and the pairing is the same deterministic amount-and-merchant match every other underlag goes through. Amount becomes decisive for real rather than as an instruction the model could not act on. The load-bearing fix is small: ingest now copies the extraction result onto the inbox item. The pool is read from invoice_inbox_items, so a hunted receipt with no extracted_data could never have matched anything, and the whole mail leg was quietly incapable of producing a pairing on amount. Consequences, all deliberate: - Harvesting runs BEFORE the pool is read, so a receipt found tonight is paired tonight rather than a night later. - One staging path instead of two. Mail-sourced proposals carry the same preview and confidence as every other, plus where they came from. - Deduped on the attachment filename, not on the message: the same invoice arrives as an original, a reminder and two forwards, and the old key filed "Invoice_13041840.pdf" four times over. - Capped at 8 receipts per merchant per run. Measured on the same ledger: 5 pairings attempted from thin evidence -> 16 real documents identified, each waiting on an amount it can be checked against. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * refactor(receipt-hunt): the model reads mail, arithmetic does the matching Collapses the mailbox leg to one model call that extracts fields, and hands every judgement back to deterministic code. Gone: resolving bank descriptors to merchant names, deciding which mail belongs to which charge, and the confidence score gating the result. Three prompts and two model calls become one, and mail-intelligence.ts drops from 450 lines to 250. What made this possible was measuring what a mail actually contains. The body was being downloaded and thrown away in favour of a 200-character snippet, and the body is where a forwarded receipt quotes its original sender and its original date. That is the purchase date, the thing whose absence forced the date window off entirely and made the old design miss five of six repeat subscriptions. It was there all along. So the model now answers only what text can support: is this an underlag, from whom, when, and for how much if the mail says so. Fields, not judgements. Everything after is arithmetic: - Retrieval is deterministic. No model decides what to search for. - Fetching is gated by worthFetching(): a stated amount is enough on its own, a vendor needs a plausible date, and a mail found by a purchase's own search is evidence in itself. That last rule is what handles a supplier the bank and the invoice name differently ("Kontorsplatser j BG" against "Stockholm Innovation & Growth AB"), which is what the deleted merchant-resolution call used to buy. - The pairing is the existing scorer, reached the same way as every other underlag: fetch, let the extraction that already runs on upload read the PDF, match on the amount. Amount is decisive in fact rather than as an instruction the model could not act on. Also adds the Swedish thousands-space amount formats to the query. Measured: the Sting invoice is findable as "15 000,00" and "15 000" and by no ungrouped form at all, so every amount search was missing them. Measured on the same ledger: 5 thin pairings -> 8 real documents, each with a vendor and a true purchase date, waiting on the amount in its own PDF. Currency is never converted to make a number agree. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(receipt-hunt): trust the bytes, not the mail, when filing an attachment Found by the first live run, which fetched nothing and reported success. Three defects, each invisible to a dry run because a dry run never downloads anything. 1. Gmail declares a forwarded PDF as application/octet-stream, and uploadDocument validates content against the declared type, so the upload was rejected: "Filinnehållet matchar inte den angivna filtypen". Every forwarded receipt with a generic MIME type would have failed this way, silently, since ingest swallows one bad attachment to protect the rest of the run. The type is now sniffed from the magic bytes, then the filename, and only then from what the mail claimed. 2. The filename was re-derived by a second full message fetch inside fetchAttachment, which came back empty and fell back to a generic "underlag.pdf", discarding the real "2332687551.pdf" the search had already reported. The known name now wins. 3. The provkörning script imported lib/init instead of calling ensureInitialized(), so document.uploaded reached no handler and nothing was ever extracted. It also used static imports, which are hoisted and ran before .env.local was read, leaving the extraction extension unable to build a Supabase client. Both are script defects, not product defects: the cron route calls ensureInitialized() at module level as the architecture requires. The script now loads the environment first and imports dynamically. Also makes the per-run fetch cap tunable (RECEIPT_HUNT_MAX_RECEIPTS) so a pilot can be held to a couple of documents, and adds --live to the script, which is the only way it writes anything. Verified end to end against a real ledger, every link exercised for the first time: two attachments fetched from Gmail, stored with their real names and types, extraction run on both, the amount copied onto the inbox item, and the deterministic matcher pairing Elgiganten 21 639,00 kr from the PDF against the -21 639 kr card purchase at 0.85, staged into Granskning as attach_document_to_transaction. The second document, a Bolagsverket filing receipt, carries no total and correctly paired with nothing. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * feat(receipt-hunt): sweep a whole mailbox, and stop lending one receipt twice A backfill on a real ledger, 22 documents fetched from 172 messages. Batches the extraction (25 mails per call) so a first run on an existing company can read the whole mailbox instead of the 40 mails one call can carry, and makes the per-run caps tunable (RECEIPT_HUNT_MAX_MAILS, RECEIPT_HUNT_MAX_RECEIPTS) so a pilot can be bounded. The nightly caps stay where they are: they pace the review queue, and a backlog is a different job from a nightly tick. Two defects the backfill exposed, neither reachable from a dry run: The one-receipt-one-purchase rule only held inside a single run. `spentDocumentIds` is per-invocation, so an H&M receipt was proposed against a -358 kr purchase on one pass and a -354 kr purchase on the next, and approving both would have put the same underlag on two verifikat. A live proposal now claims its document across runs, the same way it already claimed its transaction. A document reported with no filename, on a message carrying five attachments, was not an answer but a shrug: the caller fetched attachment number one and hoped. Those are dropped now. A body-only receipt, where there is nothing to choose between, still passes. Measured after the sweep: 21 of 22 documents read correctly, and the binding constraint on this ledger is no longer retrieval but currency. Ten receipts are in SEK and five of those pair on the amount; twelve are in USD or EUR, where the bank charged a converted figure that appears nowhere in the receipt, so no comparison is possible and none is attempted. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * feat(mail): show the provider's own mark on the mailbox settings page Someone connecting a mailbox is picking an account at a provider, and the provider's mark is how they recognise which one. A generic envelope glyph said "mail" when the question is "whose". The Google "G" already existed, drawn inline inside GoogleAuthButton for the sign-in flow. It moves to components/ui/provider-marks so there is one definition rather than two, and a Microsoft square joins it for the Graph connector. Both stay inline: no external host is contacted for an icon before anyone has agreed to anything. These are the only coloured glyphs in an achromatic interface, which is deliberate rather than an oversight. A brand mark is identity, not chrome, and Google's terms require its mark unaltered rather than tinted to match a palette. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(archive): drop the duplicate mail_connections exclusion left by the rebase Main added the table to ARCHIVE_EXCLUDED_TABLES while this branch was open, so rebasing produced the key twice and the zero-extension build failed to type check. Main's entry stays, in its alphabetical place, and keeps the sentence that answers the retention question: the grants are not räkenskapsinformation, but the receipts they find are archived as documents. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(mail): record who disconnected a mailbox, without keeping the token Raised by the compliance review: disconnect() hard-deleted the row with no trace, and which mailboxes feed underlag into the books is a control over how räkenskapsinformation is produced (BFNAR 2013:2 kap 8), so switching one off should be reconstructable years later. Written by hand rather than by the write_audit_log trigger the accounting tables use. That trigger copies the whole row into audit_log, which here would mean copying an encrypted refresh token into a second table and keeping it after the entire point of the delete was to destroy it. The sibling credential table shopify_connections omits the trigger for the same reason. Only the address and provider are recorded, pinned by a test that fails if a credential ever reaches the audit entry. The review's two other flags were checked rather than assumed: nothing purges mail_hunt documents, and categorize-core.ts:403 does carry the attached document onto the verifikat when the transaction is booked. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(mail): bound every outbound call, and stop the token widening itself Four findings from the review, each checked against the code first. Neither the Gmail API nor Google's token endpoint had a deadline. Both are awaited inside Promise.all across mailboxes, so one stalled request held the whole company's hunt open until the platform killed the run. Both now carry a 15s AbortSignal, which turns a stall into one mailbox missing from tonight's sweep. `include_granted_scopes: 'true'` let Google fold scopes this app was granted elsewhere into the token issued for a mailbox, so a grant could carry more authority than the consent screen showed. Removed, and pinned by a test asserting the parameter is absent. disconnect() ignored both statement results: a failed delete still wrote an audit entry claiming the mailbox was disconnected while the credential was live, and a failed audit insert passed silently. The delete now throws, so the entry is never written for a delete that did not happen. The audit failure is logged rather than rolled back: the two can now only diverge one way, credential gone and note missing, and recreating a credential to keep them in step would be worse than a missing note. The fifth finding is real and stays open by choice, recorded in DECISIONS.md: the cron still passes searchMail=false. A sweep of one 172-message mailbox took over 600s against a maxDuration of 300, so enabling the mailbox leg nightly would time out mid-run. That flag and RECEIPT_HUNT_COMPANY_IDS get flipped together once the per-company budget is measured. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(receipt-hunt): file each attachment under its own identity Four more findings from the review. The first is a real defect. ingestMailCandidate loops over candidate.attachmentIds, but the dedupe key, the mail_attachment_id provenance and the filename were all read from index 0. Storing the second attachment therefore recorded the first one's key and name, which mislabels the row and, because the key is unique, permanently blocks the first attachment from ever landing. Masked today only because the hunt narrows to a single attachment before calling in, so nothing in the current path exercises it. All three now come from the attachment actually being stored, and the duplicate pre-check moved inside the loop so trying a second attachment is not suppressed by the first already being filed. Mutation-tested. The per-run fetch key was the bare filename, which is not an identity: "invoice.pdf" is what half the world's billing systems attach, so a second supplier's invoice would be dropped as a duplicate of the first. Scoped by vendor as well, keeping the behaviour it was written for, one fetch for an invoice that arrives as an original, a reminder and two forwards. Adds tests/pg/mail-hunt-file-dedupe.pg.test.ts for the new unique index: five attachments from one forward all land, the same attachment is refused twice, two companies hold the same file independently, other inbox sources are untouched by the partial predicate, and the message-scoped predecessor is gone. Written against CI's Postgres; there is no local DATABASE_URL here, so CI is what exercises it. --live now refuses unless RECEIPT_HUNT_CONFIRM names the same company. The script writes to whatever .env.local points at, which for this repo is production, and a recalled command should not be able to fire it. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> * fix(test): cast the jsonb parameter so Postgres can type it pg-real could not determine the type of $3 inside jsonb_build_object. An explicit ::text is what the other pg tests do. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> --------- Co-authored-by: Jakob Wennberg <311770904+jakobwennberg-oss@users.noreply.github.com> Co-authored-by: Claude Opus 5 (1M context) <noreply@anthropic.com>
34 lines
1.5 KiB
SQL
34 lines
1.5 KiB
SQL
-- The unit of an underlag is an attachment, not a message.
|
|
--
|
|
-- 20260807090100 made (company_id, mail_message_id) unique for hunted mail, on
|
|
-- the assumption that one mail carries one receipt. A provkörning against a
|
|
-- real mailbox disproved it: receipts reach these inboxes by being forwarded in
|
|
-- batches, and a single message routinely carries several receipts for
|
|
-- different purchases ("Fwd: Kvitton februari" with five attachments, "Fwd:
|
|
-- Anthropic receipts" with two covering two different months).
|
|
--
|
|
-- Under the old index the first attachment filed would block every other
|
|
-- receipt in the same forward, silently and permanently. The key becomes
|
|
-- message + attachment.
|
|
--
|
|
-- Backfill first, so the new index can be created on existing rows: anything
|
|
-- already ingested was filed one-per-message, and its file key is derived from
|
|
-- the attachment id captured at the time.
|
|
UPDATE public.invoice_inbox_items
|
|
SET channel_context = channel_context || jsonb_build_object(
|
|
'mail_file_key',
|
|
(channel_context->>'mail_message_id') || '::' ||
|
|
COALESCE(channel_context->>'mail_attachment_id', '')
|
|
)
|
|
WHERE source = 'mail_hunt'
|
|
AND channel_context ? 'mail_message_id'
|
|
AND NOT (channel_context ? 'mail_file_key');
|
|
|
|
DROP INDEX IF EXISTS public.idx_invoice_inbox_mail_message_unique;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_invoice_inbox_mail_file_unique
|
|
ON public.invoice_inbox_items (company_id, ((channel_context->>'mail_file_key')))
|
|
WHERE source = 'mail_hunt';
|
|
|
|
NOTIFY pgrst, 'reload schema';
|