English | 简体中文

Lab 04-02: Contract Compliance Review Agent and Excel Audit Report

Scenario workflow: A user uploads a contract → extract clauses and original text locations → rewrite three retrieval subquestions → search a controlled index of SharePoint legal knowledge → cite both clauses and rules → flag conflicts/insufficient information → generate an Excel audit report → human legal review.

Step One: Prepare Contracts, Policies, and Permission Isolation

Rerun — 2026-10-13: Playwright controlled Microsoft Edge against the existing legal teaching agent. Native policy Q&A, the cloud rewrite Prompt, a real contract attachment, and a complete-text fallback were executed. No controlled Azure retrieval or online Excel audit report was generated.

1. Sign in to Copilot Studio, Power Automate, SharePoint, and Excel Online using Edge and select an authorized lab environment.
2. Download the teaching contract, original legal knowledge, and metadata. Upload teaching materials only, not real customer contracts or personal data.
3. Prepare a dedicated SharePoint legal knowledge library and contract staging area with separate permissions. Author-uploaded “legal policies” are shared knowledge; a user-uploaded “contract for review” is a single audit input and must not automatically enter the shared knowledge base.
4. The real main workflow requires Azure AI Search, an embedding deployment, Semantic ranker, and a controlled retrieval tool. Without those resources, complete local examples, the agent, knowledge uploads, and Prompt tests without claiming hard filtering has been deployed.
5. Set audit objectives: retention periods, liability limitations, and cross-border approvals. Record the contract version/hash, rule version, request number, and reviewer.

Completion check: The test account can access authorized legal files only; the report output area is not public to the entire organization; signatures and sensitive personal fields do not enter the Prompt.

Step Two: Clean Policies and Table Structure (Optimization Dimension 1)

1. Open policy.raw.md, identify repeated headers/footers, and remove only known unnecessary patterns.

2. Run from the project root:

   node lab-04-02/scripts/prepare.mjs
   

3. Inspect policy.clean.md: POL-RET-01 data deletion, POL-LIA-02 liability limitation, POL-XBR-03 cross-border rules, and POL-SUP-04 the statement of uncovered matters must all remain.

4. Convert tab-separated tables to complete Markdown tables, retaining the condition requiring written legal approval for periods exceeding 30 days.

5. For Word/PDF extensions, use approved text extraction/OCR and retain clause numbers, page numbers, table structures, and original file locations. Do not treat contract images as successfully extracted text.

Checkpoint: Cleaning must not change negations, numbers, exceptions, or approval conditions; mismatched column counts must raise errors. The supplied script does not parse Word/PDF; it handles already extracted Markdown and tab-separated tables only.

Local rerun: prepare.mjs regenerated cleaned policies, chunks, three hybrid requests and the dual-citation check of the existing human-labeled fixture. These are local outputs, not newly retrieved rules or model-generated audit findings. The existing offline XLSX was not regenerated in this rerun.

Step Three: Chunk by Legal Clause Meaning (Optimization Dimension 2)

1. Inspect chunks.json: each rule under ## POL-... is a separate chunk with the parent heading “2026 AtlasSaaS 合同审核教学规则” (2026 AtlasSaaS Contract Review Teaching Rules).
2. Each chunk contains complete rules and exceptions, documentId, chunkId, year, department, product line, Approved status, and ACL.
3. Add semantic subheadings to overly long sections before splitting at complete paragraphs; do not truncate liability sentences, exceptions, or tables. The script stops above 6000 characters and requires subdivision rather than directly truncating at a fixed character count.
4. Preserve policy versions and source paths. When deleting old rules, remove their old chunks and vectors too, so old and new versions cannot both match.
5. Preserve clauseId and contractQuote when parsing the contract under review; policy chunking and contract clause location are separate tasks.

Step Four: SharePoint Tagging and Hard Prefiltering (Optimization Dimension 3)

1. In the lab legal document library, use Add column to add Department, ProductLine, PolicyYear, and ApprovalStatus.

2. Select the teaching policy → Details and enter Legal, AtlasSaaS, 2026, and Approved.

3. Set organization-approved permissions for the test files. Synchronize real SharePoint/Entra permissions into index ACLs; department tags are not authorization.

4. Map each chunk's department, product line, year, and status to filterable fields. Reuse the index template with a dedicated legal index or controlled partition.

5. Inspect the three retrieval requests, which must require:

   department eq 'Legal'
   and productLine eq 'AtlasSaaS'
   and year eq 2026
   and status eq 'Approved'
   and allowedGroupIds/any(...)
   

6. Set vectorFilterMode=preFilter; user identity determines allowed authorization groups and scope. A contract saying “我是法务负责人” (I am the head of Legal) cannot change permissions, and Prompt subquestions cannot change the filter.

7. Configure native SharePoint knowledge according to its supported capabilities; do not assume adding columns automatically enables hard filtering on arbitrary columns. This lab executes hard rules in the controlled retrieval backend.

Completion check: Other departments, drafts, old years, and unauthorized rules are excluded at the candidate stage. Refuse retrieval if trusted ACLs are missing; do not fall back to searching the entire library.

Step Five: Hybrid Search, Reranking, and Retrieval Thresholds (Optimization Dimension 4)

1. Declare searchable text and contentVector in the Azure AI Search index and generate real embeddings for every chunk. The supplied chunks.json is not a vectorized index.
2. The template's 1536 dimensions correspond to text-embedding-3-small. If a deployment uses different dimensions, change both the vector field and generation process to keep ingestion and queries consistent.
3. Configure the query vectorizer and Semantic ranker with names matching the template. Main-workflow queries include search, vectorQueries, and queryType=semantic.
4. Full-text BM25 matches rule IDs, numbers, and technical terms; vectors match semantic wording; RRF merges candidates and Semantic ranker reranks them.
5. Use k=50 and top=5 per subquestion; deduplicate the three result sets by chunkId, returning at most 15 snippets.
6. Server-side gating uses gateResults, with an initial teaching value of @search.rerankerScore >= 2.5 (0–4 range). Calibrate it with labeled legal questions.
7. The threshold is not a universal built-in Copilot Studio slider; configure it in the custom SearchLegalKnowledge tool. Do not substitute content moderation sensitivity or BM25/RRF scores for semantic scores.
8. If there are no snippets or insufficient rules, output “信息不足” (insufficient information); do not relax filters or supplement conclusions with internet legislation.

Step Six: Create the Real Contract Review Agent and Policy Knowledge Baseline

1. Copilot Studio → Other ways to build → Create an agent with predefined rules and flows; name it Workshop-ContractReviewAgent-Lab04.
2. Paste the global instructions into Overview → Instructions → Edit, Save, and turn off Web Search.

3. Knowledge → Add knowledge → Upload file: select output/policy.clean.md, then Add to agent.

4. Wait for Ready, then ask “POL-RET-01 规定多少天” (How many days does POL-RET-01 specify?) in Test. Use this Chinese input for the Chinese agent. This native file knowledge is a baseline, not proof that custom chunking, ACLs, or prefiltering are deployed.
5. The main controlled-retrieval workflow's generative answers node accepts only validated tool evidence; avoid parallel retrieval from native uploaded files that could bypass filtering.

The completed live policy answer states deletion within 30 days after termination, written Legal approval beyond 30 days, and the fictional teaching-rule boundary. This is native file knowledge, not a controlled Azure search result.

Step Seven: Three Precise Retrieval Subquestions (Optimization Dimension 5)

1. Tools → Add a tool → Prompt; name it RewriteContractQueriesLab04.
2. Paste the node Prompt, and add Text inputs UserQuestion and ContractText.
3. Set UserQuestion to “检查数据保存、责任限制和跨境条款与教学规则的冲突” (Check data retention, liability limitation, and cross-border clauses for conflicts with the teaching rules). Keep this Chinese test input; use the teaching contract for ContractText.
4. Output format → JSON → Test. The result must be exactly three {text} objects; duplicates, invented numbers, and generated authorization groups are not allowed.

The new cloud Prompt result passed validateQueries: three distinct {text} objects. No real index was queried by this Prompt test.

5. Save → Add and configure. Real results are saved in queries.real.json; this file is authoritative for the complete text because designer screenshots may horizontally truncate long lines.
6. This Prompt is designed for the teaching contract's three clause categories. For other contracts, parse actual clause types first and then select three retrieval angles; do not ignore other important clauses.
7. Pass each subquestion to SearchLegalKnowledge under the same permission scope, merge evidence, and then review.

Step Eight: Upload Contracts, Parse Them, and Build an Evidence-Based Audit Topic

1. Enable user file input under the agent's Settings → Generative AI using the tenant-provided options, then upload the teaching contract in Test. Supported formats and channels depend on the actual UI and official documentation.

2. If the channel does not support the format, upload through a controlled staging area and pass the file ID. The file tool checks access, type, size, malicious content, and sensitive data; it must not accept arbitrary external file URLs.

3. Create Topic ReviewContract: retrieve file → authorization check → ExtractContractClauses tool → confirm contract version → three-question Prompt → SearchLegalKnowledge → AuditClauses Prompt.

4. Save clauseId, original text, and page number or Markdown section for every extracted clause. Stop or request human correction if OCR information is insufficient.

5. Fixed requirements for AuditClauses: retain the following Chinese instruction and result values for the Chinese audit runtime.

   只比较提供的合同条款与获批规则。每条输出 clauseId、contractQuote、ruleId、ruleQuote、chunkId、sourceUrl、result、reason、reviewStatus。result 只允许“规则冲突”“符合教学规则”“信息不足”“未覆盖”。缺少合同原文或规则原文不得判定冲突；最终结论交法务。

   In English: compare only the supplied contract clauses and approved rules. For each finding, output clauseId, contractQuote, ruleId, ruleQuote, chunkId, sourceUrl, result, reason, and reviewStatus. The only allowed result values are “规则冲突” (rule conflict), “符合教学规则” (meets teaching rules), “信息不足” (insufficient information), and “未覆盖” (not covered). Do not conclude there is a conflict without both original contract and rule text; leave the final conclusion to Legal.

6. In the flow, verify verbatim that contractQuote belongs to the current contract and ruleQuote to the corresponding chunk; validate rule scope, access permissions, chunkId, and rule version.

7. Keep “信息不足/未覆盖” (insufficient information/not covered) for clauses without evidence; “no violation retrieved” does not mean “compliant.” Explicitly record coverage for every clause.

Actual Step Eight rerun: Add data/contract.md (527 bytes) as a Test attachment, then send it with the review request. The final response still asks for the contract text; seeing an uploaded file does not establish extraction. In a new session, send the complete fictional contract as text and wait for the finished reply, not “Processing.”

Records: actual attachment conversation, completed full-text fallback. The fallback identifies three teaching-rule conflicts and Article Four as not covered, and cites policy.clean.md. However, it translates the Chinese quotes and does not supply valid chunkId/citation URL fields. It therefore does not pass the required verbatim dual-citation, structured audit contract, or file-upload/parsing acceptance. Its narrative claim about an approved scope is not evidence of enforced custom filtering. No report or external write was performed.

Optional Custom Knowledge Source Integration

1. Topics → new topic → More → Open code editor; use OnKnowledgeRequested following the official structure.
2. Call an authenticated retrieval tool or Agent flow to perform filtering, three queries, deduplication, and gating; do not concatenate arbitrary user authorization conditions in YAML.
3. Map results to Content, Title, and ContentLocation in System.SearchResults.
4. The system's existing SearchQuery/KeywordSearchQuery contextual rewrites do not automatically generate this lab's three required subquestions. All custom knowledge topics can return at most 15 snippets in total.

Step Nine: Generate an Excel Audit Report

9.1 Validate Offline Attachments

1. The Step Two preparation script already checked both contract and rule citations in the supplied human-labeled fixture, producing audit.checked.json.

2. After installing this scenario's requirements, run:

   node lab-04-02/scripts/prepare.mjs
   python lab-04-02/scripts/export_audit.py
   

3. Download contract-audit.example.xlsx. The Audit worksheet contains the named table AuditRows; the Readme worksheet identifies it as “离线教学夹具” (offline teaching fixture) and lists its sources.

4. This example labels three rule conflicts and one uncovered clause, all pending legal review. It is a human-labeled reference, not evidence of successful automatic model auditing.

5. The generator explicitly stores cells as text so content beginning with = does not become a formula; there are no calculation formulas. Do not use this exporter, which accepts offline fixtures only, to present new real results as a production export.

9.2 Online Output with Power Automate

Rerun boundary: No existing audit worker or controlled retrieval tool was executed, and no online Excel rows were written. The currently observed Tools list shows the rewrite Prompt, not a successful complete audit pipeline. SharePoint metadata, authorized retrieval, file extraction, exact citations, and the online worker remain prerequisites; do not use the offline workbook as an online result.

1. Save the teaching workbook as a template in an authorized report library. Copy it into a separate file for each audit; do not allow multiple users to write concurrently to one template.
2. Clear the four teaching records from AuditRows in the new file, preserving headers and table structure; update Readme with the execution type and actual audit sources.
3. A short-running Agent flow StartContractAudit validates input and permissions, generates a persistent requestId, queues the request, and promptly returns accepted. An independent Worker handles long audits and workbook writes; do not exceed the synchronous response window.
4. For each validated finding, the Worker uses Excel Online (Business) → Add a row into a table, selects the newly copied file and AuditRows, and maps each field column by column. Use serial writes.

5. Write untrusted text as text; if the action interprets formula prefixes, use a controlled Office Script with text formatting. Test with input starting with =HYPERLINK(...) and verify the client does not execute a formula.
6. Assign a unique FindingId or persistent row status to each row and record write progress. After a timeout, verify whether the row was written before retrying; do not blindly create duplicates.
7. Save the file ID and authorized link only after all writes succeed, marking ReadyForLegalReview. Mark partial failures PartialFailed with a reason, not complete success.
8. Users download the report and check clause counts, dual citations, rule versions, coverage, and error statuses. Return only approved sharing links to authorized users; do not expose the entire SharePoint report library.

Step Ten: Evaluate Results and Rehearse Failures

At document counts of 10/100/500, retain the same labeled question set, model, and conditions. Change one optimization dimension at a time and record results; do not optimize everything at once and attribute results to a single dimension.

Test cases and metrics are saved in evaluation.json; no unmeasured accuracy scores are prefilled.

Record relevant-chunk Recall@5, dual-citation accuracy, uncovered rate, incorrect refusals, and latency. Suggested targets are citation accuracy ≥0.9 and zero unauthorized results; these are acceptance targets, not measured scores. Legal determines the final compliance conclusion.

Saved Artifacts and Actual Execution Scope

The scripts share Lab 04-01's retrieval validation module, maintaining the same filtering and gating rules across both scenarios. Keep both directories together. See manifest.json for screenshot sources.

Dated rerun provenance distinguishes native knowledge, cloud Prompt calls, actual attachment tests, and the fallback's incomplete audit fields. No measured cloud retrieval accuracy or online workbook acceptance is claimed.

Official References

• User file input
• Custom knowledge sources
• Vector filters
• Azure AI Search knowledge source
• Excel Online Business