# Data foundations: a document, a record and a searchable copy

Original LockedIn Labs learning exercise. Every record is synthetic.
Allow about 25 minutes. No account, API key, cloud resource or model call is needed.

## 1. Understand what you are building

Field Desk keeps a procedure document, a ticket record and a search candidate.
They answer different questions:

| Information | Role | Question it answers |
| --- | --- | --- |
| Procedure P-17, revision 2 | Source object | What does this version of the procedure say? |
| Ticket T-104, status open | Relational row | What is the current state of this ticket? |
| Excerpt pointing to P-17, revision 1 | Derived search entry | What passage might help answer the question? |

An object can contain structured JSON or CSV as well as a PDF. The distinction
is how you store and access it, not whether its bytes look organized. A database
can itself provide text and vector indexes. You do not need three products
merely because this example separates three responsibilities.

Sketch this before running the code:

    procedure object --ingestion--> search copy --candidate--> evidence check
           |                                                       |
           +---- current revision and access ---------------------+
                                                                   |
    ticket table <-- SQL query -- application       checked excerpt or exclusion

The source is revision 2 and the search candidate is revision 1. Predict the
outcome before inspecting the program's output. Can an up-to-date search copy
still be denied? What if the source was deleted?

## 2. Run a small local example

Use Python 3 with its `sqlite3` standard-library module. Run
`python3 -c "import sqlite3; print(sqlite3.sqlite_version)"` to check availability.
If your Python distributor omitted that module, use a Python installation that
includes it; no third-party package is required for this exercise.

Copy the following into a new file you own, `data_foundations.py`, then run
`python3 data_foundations.py` (`python data_foundations.py` on systems using that
command). The program uses an in-memory SQLite database, makes no network
requests and creates no database files. The document dictionary represents an
object store for teaching; the candidate is hand-authored, not a real search or
embedding result. SQL is real in this exercise.

```python
import sqlite3

# An object has content and metadata. This is a local teaching representation.
documents = {
    "P-17": {
        "revision": 2,
        "scope": "training",
        "text": "Revision 2: attach the review note to the ticket.",
    }
}

# This candidate represents an index that has not caught up with the source.
candidate = {
    "source_id": "P-17",
    "revision": 1,
    "excerpt": "Revision 1: place the review note in the shared folder.",
}

db = sqlite3.connect(":memory:")
db.execute("""
    CREATE TABLE tickets (
        ticket_id TEXT PRIMARY KEY,
        scope TEXT NOT NULL,
        status TEXT NOT NULL,
        procedure_id TEXT NOT NULL
    )
""")
db.execute("INSERT INTO tickets VALUES (?, ?, ?, ?)",
           ("T-104", "training", "open", "P-17"))

# The scope stands for trusted application context, not a model-supplied value.
trusted_scope = "training"
row = db.execute(
    "SELECT ticket_id, status, procedure_id FROM tickets "
    "WHERE ticket_id = ? AND scope = ?", ("T-104", trusted_scope)
).fetchone()
assert row == ("T-104", "open", "P-17")
print("Record query:", row)


def inspect_evidence(entry, scope):
    source = documents.get(entry["source_id"])
    if source is None:
        return "excluded: missing source"
    if source["scope"] != scope:
        return "excluded: denied access"
    if source["revision"] != entry["revision"]:
        return "excluded: stale revision"
    # For this whole-document example, compare content too. A production chunk
    # pipeline needs trusted ingestion and its own source/chunk integrity checks.
    if source["text"] != entry["excerpt"]:
        return "excluded: content mismatch"
    return "usable: " + entry["excerpt"]


stale = inspect_evidence(candidate, trusted_scope)
assert stale == "excluded: stale revision"
print("1. Before refresh:", stale)

# Refresh from the authoritative source, not from a generated answer.
candidate = {
    "source_id": "P-17",
    "revision": documents["P-17"]["revision"],
    "excerpt": documents["P-17"]["text"],
}
current = inspect_evidence(candidate, trusted_scope)
assert current.startswith("usable:")
print("2. After refresh:", current)

# The source owner now changes access. A refreshed copy is still not authority.
documents["P-17"]["scope"] = "restricted"
denied = inspect_evidence(candidate, trusted_scope)
assert denied == "excluded: denied access"
print("3. Access revoked:", denied)

# Deleting this source does not automatically erase our deliberately stale copy.
del documents["P-17"]
missing = inspect_evidence(candidate, trusted_scope)
assert missing == "excluded: missing source"
print("4. Source deleted:", missing)
print("Index copy still exists:", candidate["source_id"])

db.close()
```

Expected output:

```text
Record query: ('T-104', 'open', 'P-17')
1. Before refresh: excluded: stale revision
2. After refresh: usable: Revision 2: attach the review note to the ticket.
3. Access revoked: excluded: denied access
4. Source deleted: excluded: missing source
Index copy still exists: P-17
```

The checks are sequential and deliberately small. This is not a production
authorization service or proof against concurrent permission changes. Real
retrieval must apply the trusted caller's scope, protect ingestion, handle
update races and perform revision/access checks before data reaches the model.
The application also needs a policy for removing derived entries, caches and
backups. Removing one source alone is not complete deletion.

## 3. Explain the SQL before making it harder

`SELECT ticket_id, status, procedure_id` chooses columns to return. `FROM tickets`
chooses the table. `WHERE ticket_id = ? AND scope = ?` narrows the rows. The
question marks are parameter placeholders: values are passed separately from
SQL text. An exact record query is not a semantic search for similar wording.

Try these changes in your own file:

1. Change the queried ticket to T-999. `fetchone()` returns `None`; update your
   assertion and explain missing data without inventing a status.
2. Restore T-104. Use a parameterized `UPDATE` to change its status to resolved,
   then query it again. The ticket changes; the procedure text does not.
3. Keep a current revision number but alter the candidate's excerpt. Predict
   the content-mismatch result. A plausible ID and revision are not enough
   when ingestion or the candidate itself is untrusted.
4. Write which system owns procedure content, ticket state and permission
   decisions. Give each a change and deletion path.

Keep the source ID and revision with each excerpt in a real pipeline. When
multiple records must change together, investigate database transactions. When
objects and a separate index must change together, plan for failures and
reconciliation across those services; one SQL transaction does not magically
cover every external system.

## 4. Connect the concepts to your cloud

| Role | Google Cloud example | AWS example | Azure example |
| --- | --- | --- | --- |
| Store procedure objects | Cloud Storage | Amazon S3 | Blob Storage |
| Operate a relational database | Cloud SQL | Amazon RDS | Azure SQL Database |

These examples identify roles, not interchangeable APIs or complete architecture
recommendations. Select a search service or database index according to your
query, permission, update and operational requirements. Azure AI Search is one
example of a separate search service. The vendor lessons continue with those
implementation decisions:

- [Google Cloud data lesson](https://training.lockedinlabs.ai/app/academy?course=gcp-infrastructure&lesson=data&view=focus)
- [AWS data lesson](https://training.lockedinlabs.ai/app/academy?course=aws-infrastructure&lesson=data&view=focus)
- [Azure data lesson](https://training.lockedinlabs.ai/app/academy?course=azure-infrastructure&lesson=data&view=focus)
- [Return to the visual 101](https://training.lockedinlabs.ai/training/fde-101?topic=data#concept)

Those Academy lessons use your existing training access. This workbook stays
free. Cleanup: all runtime data disappears when this program exits; keep or
remove your own script as desired. No cloud cleanup or billing action is needed.

Primary references, checked 20 October 2026:
[Python SQLite](https://docs.python.org/3/library/sqlite3.html),
[PostgreSQL tables](https://www.postgresql.org/docs/current/tutorial-table.html),
[Cloud Storage](https://docs.cloud.google.com/storage/docs/introduction),
[Cloud SQL](https://docs.cloud.google.com/sql/docs/introduction),
[Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html),
[Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html),
[Blob Storage](https://learn.microsoft.com/en-us/azure/storage/blobs/storage-blobs-introduction),
[Azure SQL Database](https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview?view=azuresql),
[Azure AI Search indexes](https://learn.microsoft.com/en-us/azure/search/search-what-is-an-index).