Documenting Your IFS Database: Tools and Approaches

The IFS database is an important implementation truth for Apps 10 and for IFS internals, but it is not the whole contract. Source models, generated APIs, projection metadata, configuration exports, security, report definitions and integration contracts explain behaviour that a reverse-engineered schema cannot. In an IFS-managed Cloud service, customers also should not assume direct production-database access. Documentation therefore needs both an application/API view and—where access is authorised—a database view.

This gap in documentation costs time. Developers spend hours reverse-engineering logic. DBAs struggle to explain what a cryptic column does. New team members onboard slowly. Upgrades become risky because nobody knows what was customized. And when compliance audits arrive, you're scrambling to prove data lineage.

This guide shows you how to keep your IFS database documented—not as a one-time project, but as a living practice. We'll explore the tools, query patterns, and processes that work for IFS-specific objects, including how to document through upgrades without losing your institutional knowledge.

Why Database Documentation Matters for IFS

IFS is not a simple transactional database. Its metadata model, dictionary tables, and custom extensions create layers of complexity that generic database tools often miss. Here's what's at stake:

Knowledge Retention: When a consultant or DBA leaves, their understanding of your schema customizations, custom objects, and field mappings walks out with them. Documentation captures that knowledge.

Update Risk: IFS Cloud has release and service-update cadences that are not captured by a universal “every 12 months” rule. Knowing which source/configuration artefacts, projections, reports and external data contracts depend on changed objects tells you what to analyse and test.

Data Governance: Regulations like SOX, GDPR, and industry-specific mandates require you to document data flows, retention policies, and access rules. Your IFS schema is part of that audit trail.

Development Velocity: When new developers can reference what each table does, what custom fields are added, and how views have been extended, they ramp up faster and make fewer mistakes.

Integration Clarity: If you're feeding data to BI tools, ERP modules, or third-party systems, those teams need to understand your schema. Bad documentation = bad integrations.

The IFS Data Model: What You're Actually Documenting

Before choosing tools, understand what IFS stores and how it differs from a standard Oracle database.

Logical-unit views and base tables: Public/readable objects such as CUSTOMER_INFO, PURCHASE_ORDER and release-specific reporting views are distinct from internal persistence tables commonly ending _TAB. Do not invent generic names such as ORDER_HEADER/INVOICE_LINES, and do not treat _TAB objects as integration contracts.

IFS metadata: Entity, projection, client, configuration and dictionary metadata describe attributes, operations and presentation. FNDOBJ$ is an Oracle data-dictionary object name sometimes cited in generic Oracle material, not an IFS application table to query for this purpose. Use supported IFS metadata/API Explorer/Developer Studio sources for the target release.

Custom Objects: Source-modelled customer entities and Apps 10 configuration/custom objects have different artefacts and lifecycle rules. Preserve their source/configuration exports and reconcile them with Update Analyzer and the target release; “persist across upgrades” is not a substitute for compatibility testing.

Views and Packages: IFS logical-unit views and public/generated API packages are release-specific contracts. _TAB normally denotes a persistence table, not a view prefix; _STO/staging conventions are not universal public APIs.

Extensions and Modifications: Customer source/configuration, calculated attributes, PL/SQL packages, reports and integrations need explicit ownership. Avoid in-place comments or changes on IFS-delivered database objects because a rebuild/update can replace them.

Tool Comparison: What Works for IFS

Choosing the right documentation tool means balancing automation, IFS-specific knowledge, and team workflow. Here's how the top options stack up.

SchemaSpy: Open-Source ERD Generation

What it does: Analyzes database metadata and generates interactive HTML documentation with entity-relationship diagrams.

Why it works for IFS:

• Free and open-source, with strong Oracle support
• Generates relationship diagrams automatically from foreign keys
• Outputs clean, searchable HTML that teams can host internally
• No licensing, no vendor lock-in

Limitations:

• Doesn't understand IFS metadata or custom objects natively
• Diagrams can become overwhelming with hundreds of IFS tables
• No editing interface for adding business descriptions
• Requires manual effort to annotate what SchemaSpy generates

Best for: Apps 10/on-premises or another customer-controlled database where read-only catalogue access is approved. It is not a generic route into an IFS-managed Cloud production database.

Getting started:

Prefer an Oracle wallet/external password store or the tool's supported secret mechanism so credentials are not exposed in shell history or process listings. Grant this account only catalogue/read access needed for the selected objects.

DataGrip: IDE-Driven Documentation

What it does: JetBrains' database IDE offering schema inspection, diagram generation, and reverse engineering.

Why it works for IFS:

• Integrates with popular development environments
• Excellent schema comparison (useful when testing upgrades)
• Can generate diagrams visually from the IDE
• Supports custom SQL scripts and stored procedure browsing

Limitations:

• Requires a paid license (though affordable for development teams)
• Generates diagrams but doesn't export documentation as easily as dedicated tools
• Not purpose-built for documentation, so workflow is less streamlined
• Metadata editing is minimal

Best for: Dev teams already using IntelliJ/ReSharper who want quick schema inspection without switching tools.

Oracle SQL Developer: Native Oracle Tooling

What it does: Oracle's own database management tool, with built-in schema documentation generation.

Why it works for IFS:

• Official Oracle support; understands Oracle-specific objects (packages, triggers, synonyms)
• Right-click "Generate DB doc" creates instant HTML reports
• Free from Oracle
• Can document stored procedures, constraints, and indexes

Limitations:

• Documentation output is basic—no interactive HTML or collaboration features
• No editing interface; you have to manually add comments in the database
• Doesn't understand IFS logical units, custom objects, or metadata layers
• Limited customization of output format

Best for: DBAs who need a quick baseline and are comfortable with minimal formatting.

Dataedo: Complete Metadata Repository

What it does: A dedicated database documentation platform with a repository, visual designer, and HTML export.

Why it works for IFS:

• Rich metadata storage independent of the database
• Team collaboration: multiple users can edit descriptions simultaneously
• Automatic change detection and versioning
• Beautiful, interactive HTML documentation
• Glossary and data lineage tracking
• Git integration for version control

Limitations:

• Paid tool (starting at enterprise pricing)
• Requires separate setup and learning curve
• IFS-specific metadata still requires manual interpretation
• Data sync must be scheduled or triggered manually

Best for: Organizations with mature data governance, large teams, and budget for a dedicated tool.

DbSchema: Visual Design + Documentation

What it does: Visual database designer and documentation generator with schema synchronization.

Why it works for IFS:

• Drag-and-drop schema visualization
• Auto-generates HTML documentation from designs
• Real-time schema synchronization (changes in database reflect in design)
• Git integration for collaborative workflows
• Cross-platform (Windows, macOS, Linux)

Limitations:

• Paid licensing model
• Designed more for design-first workflows than reverse-engineering legacy systems
• IFS-specific metadata requires manual mapping
• Learning curve steeper than simpler tools

Best for: Teams modernizing their IFS schema and wanting design + documentation in one tool.

Building an IFS Data Dictionary: Queries and Patterns

Regardless of which tool you choose, you need to populate metadata. Here are practical SQL queries for extracting IFS-specific information.

Documenting Custom Objects

Do not infer ownership from a supposed universal CO_/COU_ prefix. Start with the customer solution repository, Apps 10 custom-object/configuration exports, installation manifests and an owned object register. In an authorised Apps 10 database, reconcile that register against the catalogue:

Custom Fields Inventory

Track custom fields added to base tables:

For source-modelled Cloud attributes and configuration fields, the customer source/configuration inventory and projection metadata are normally more useful than guessing physical column patterns.

View Dependency Mapping

Understand what views depend on what tables:

Documenting Stored Procedures

Extract business logic for reference:

Column Comments: The Foundation Layer

Column comments are useful on customer-owned objects in an authorised database, but they are not an upgrade-safe way to modify IFS-delivered tables/views. For source-modelled Cloud entities, put descriptions in the model/source and documentation repository so the build owns them. Keep business glossary and lineage outside the database as well.

Setting Column Comments

The object names above are explicitly customer-owned examples, not standard IFS objects.

Querying Comments

Tools and developers can retrieve these comments:

Pro tip: Establish a comment template so your team writes consistently:

[Business Purpose]: One-line description of what the column stores.

[Data Type & Constraints]: Any non-obvious validation rules or ranges.

[IFS Metadata]: Logical unit, attribute name, or custom object reference.

[Change History]: Notable modifications, especially across upgrades.

[Usage]: Where this column is read/written by reports, integrations, or custom code.

[Owner]: Contact for questions (optional, for highly specialized fields).

Documentation Strategy: IFS Upgrade-Safe Approach

IFS upgrades are major events. Your documentation strategy must survive them.

Separation of Concerns

Keep documentation in two separate places:

1. Owned source/database metadata: model descriptions and comments on customer-owned objects. Never assume comments applied to IFS-delivered objects will survive a rebuild.
2. External Documentation (Wiki, Confluence, Markdown): Business glossary, data lineage, custom object diagrams, and cross-module relationships.

This way, when you upgrade:

• The customer build/source recreates owned metadata, and database extraction provides evidence of what was actually installed.
• External docs can be reviewed against the new schema without loss.

Pre-Upgrade Documentation Audit

Before every IFS upgrade, run this process:

1. Baseline your current schema:

2. Export column comments:

3. List all custom objects (via IFS admin or developer studio)

4. Commit to Git:

Post-Upgrade Reconciliation

After IFS deployment:

1. Generate post-upgrade schema documentation
2. Diff against pre-upgrade baseline
3. Verify custom objects still exist and weren't modified
4. Regenerate customer-owned metadata from source; do not automatically reapply DDL comments to IFS Core objects
5. Update documentation with new tables/columns added by IFS
6. Commit changes:

Team Wiki Documentation: Confluence Example

A tool like Confluence or Obsidian complements automated documentation:

Confluence Org Structure:

Database Documentation (Space)
├── Overview & Architecture
│   ├── IFS Module Breakdown
│   └── Custom Extension Catalog
├── Data Dictionary
│   ├── Core Tables
│   ├── Custom Objects
│   └── Views & Materialized Views
├── Data Lineage & Integration
│   ├── BI & Analytics Feeds
│   ├── External System Integrations
│   └── EDI/API Contracts
├── Runbooks
│   ├── How to Add a Custom Field
│   ├── How to Extend a Logical Unit
│   └── Upgrade Troubleshooting
└── Metadata & Change Log
    └── Schema Changes by Release

Sample Page: "Custom Objects Registry"

Living Documentation Workflow

1. Developers document custom objects as they deploy them (Confluence + database comments)
2. DBAs validate and add operational notes (on-call runbooks, backup/restore impacts)
3. Quarterly review: DBA + architect compare Confluence against actual schema; update discrepancies
4. Git-tracked: Confluence exports (or Markdown docs) committed alongside schema snapshots

Automation: CI/CD Integration

Documentation shouldn't be manual busywork. Integrate it into your deployment pipeline:

Example: Post-Deployment Documentation Step

For an unattended runner, have the CI secret store create a permission-restricted temporary SchemaSpy properties file containing schemaspy.p=..., expose its path as SCHEMASPY_CONFIG, and remove it when the job finishes. Do not use the interactive -pfp prompt in CI or place the password in a command-line argument.

Run this only where database catalogue access is supported. In managed IFS Cloud, generate the equivalent inventory from customer source, configuration exports, API metadata and supported observability instead. Pin SchemaSpy/Oracle client versions, escape CSV correctly, and redact sensitive comments before publishing the artefact.

Best Practices & Checklist

What Every IFS Project Should Document

• Base tables: What each IFS LU stores; critical fields; indexes
• Custom objects: What they do; which base tables they extend; owner contact
• Custom views: Purpose, base tables, refresh schedule (if materialized)
• Stored procedures: What they calculate/update; inputs/outputs; dependencies
• Owned metadata: Descriptions/comments on customer-owned objects and attributes; any sanctioned legacy Core modification documented externally and in source/change records
• Data lineage: Where data flows to (BI, ERP, EDI, integrations)
• Upgrade notes: Version history of schema changes; known issues
• Access control: Who owns what; sensitivity levels (if applicable)

Maintenance Cadence

Avoiding Documentation Debt

• Don't document everything perfectly upfront. Start with custom objects and critical base tables.
• Do add comments as you modify schema. Make it part of the change request.
• Don't rely on external docs alone. Use database comments as a fallback.
• Do version-control your documentation artifacts (schema snapshots, CSV exports, markdown).
• Don't let documentation lag behind code. Enforce "docs updated" as part of deployment gates.
• Do rotate ownership. Assign documentation review to different team members quarterly so knowledge spreads.

Putting It Together: A Practical Workflow

Here's a realistic example for a mid-sized IFS implementation:

Week 1: Baseline Setup

• Install SchemaSpy; generate initial schema documentation
• Create Confluence space; build table of contents
• Write column comments for all custom fields (scripted where possible)
• Commit schema snapshots to Git

Week 2–4: Document Custom Objects

• List all custom LUs deployed in the system
• For each: create a Confluence page with purpose, tables, owner, deployment date
• Query and document views, stored procedures, and scripts
• Build data lineage diagrams (even simple ones in draw.io or Lucidchart)

Ongoing: Maintenance

• Every deployment: regenerate SchemaSpy; commit changes; update Confluence notes
• Monthly: QA spot-checks 5–10 entries in Confluence; fix any stale info
• Quarterly: DBA reviews all custom objects; archives unused ones
• Per upgrade: baseline before, reconcile after, publish upgrade notes

Tools Stack:

• SchemaSpy (free): HTML diagrams, baseline comparison
• Confluence (Atlassian): Team wiki, change tracking, collaborative editing
• Git (GitHub/GitLab): Version control for schema exports, markdown docs
• Oracle SQL Developer (free): Ad-hoc queries, stored procedure browsing
• Column comments (database): Self-documenting schema

Advanced: Custom Queries for IFS-Specific Scenarios

Beyond standard column comments, IFS projects benefit from specialized documentation queries for their unique structure.

Finding All Extensions to a Base Logical Unit

If you've extended CUSTOMER_INFO with custom fields, tracking them across multiple custom objects requires structured queries:

The concrete _CFV name is generated per logical unit/update and must be verified. Store results alongside the configuration export and release baseline; do not assume generic CF_% or ATTR% columns are IFS custom fields.

Materialized View Refresh Tracking

Many IFS implementations use materialized views for reporting. Document their refresh schedule and dependencies:

Pair this with a Confluence page listing:

• Scheduled refresh times (if any)
• Manual refresh procedures
• Known performance impacts during refresh
• Alert contacts if refresh fails

Stored Procedure Dependency Graph

Track which procedures call which other procedures, and which update which tables:

Export this to a CSV and visualize in a simple tool like draw.io or Lucidchart. Your team will immediately see which deployments might impact what.

Common Documentation Mistakes to Avoid

1. Treating Documentation as a One-Time Artifact

The single biggest mistake: document once, then ignore it. Within 6 months, docs are stale. Within 12 months, they're misleading.

Solution: Treat documentation like code. Every schema change = documentation update. Make it part of your change control process, not optional.

2. Documenting Everything Exhaustively

Another extreme: trying to document every single IFS table. You'll give up after week two.

Solution: Start with the 20% that matters: custom objects, critical extensions, views, and procedures. Document the rest gradually.

3. Separating Tool Documentation from Database Comments

If documentation lives only in Confluence or Dataedo, it dies when you upgrade. If it lives only in database comments, your team doesn't see the business context.

Solution: Comments live in the database (persistent, version-safe). Business context and runbooks live in your wiki. Tools query both sources.

4. No Ownership or Accountability

If "everyone" owns documentation, nobody does. It decays immediately.

Solution: Assign a documentation owner (usually senior DBA or architect). They champion the practice; others contribute. Rotate quarterly so knowledge spreads.

5. Skipping Pre-Upgrade Snapshots

You upgrade IFS, your custom procedures break, and you have no baseline to compare against.

Solution: Before every upgrade, run SchemaSpy, export column comments to CSV, commit to Git. After upgrade, diff and reconcile.

Scaling Documentation: Multi-Team Scenarios

Large implementations often have multiple teams (app team, analytics team, integration team). Each needs different documentation views.

Central Repository Model

Repository Structure:
├── /schema-snapshots/
│   ├── 2026-01-15-pre-upgrade/
│   ├── 2026-01-20-post-upgrade/
│   └── current/
├── /data-dictionary/
│   ├── core-tables.csv
│   ├── custom-objects.csv
│   └── materialized-views.csv
├── /runbooks/
│   ├── add-custom-field.md
│   ├── extend-logical-unit.md
│   └── troubleshoot-upgrade.md
└── /lineage/
    ├── bi-feeds.png
    ├── integration-endpoints.png
    └── data-quality-rules.md

Each team commits to sections they own. Weekly rollup documents the whole picture.

Consumption Patterns

Build a Measurable Business Case

When leadership asks why documentation deserves capacity, use an estate-specific worksheet rather than generic savings claims.

Measure the investment:

• Initial baseline hours: discovery, tooling, access, ownership mapping, and first publication
• Ongoing hours per month: reviews, approved changes, broken-link checks, and release updates
• Platform and training costs: licences, hosting, enablement, and support
• Loaded labour rate for each contributing role

Measure the return:

• Onboarding: time from joining to completing a representative change safely, before and after the documentation baseline
• Upgrade readiness: hours spent locating affected objects, unresolved findings, escaped defects, and rework across comparable updates
• Incident response: median time to identify the owning object, integration, and business rule; track resolution time separately
• Audit preparation: evidence-gathering hours, missing artefacts, and remediation actions
• Knowledge retention: number of critical objects with a named owner, source link, runbook, and tested recovery path

Calculate payback only from measured inputs:

Calculate a payback period only when the measured monthly net benefit is positive. Keep one-off investment separate from recurring cost, and do not count the same avoided effort twice. Treat the first calculation as a hypothesis. Recalculate it after an update cycle, a meaningful incident, and a new-starter onboarding exercise; do not promise a universal percentage saving or fixed payback period.

Plus intangible benefits:

• Team confidence ("I know what I'm touching")
• Knowledge retention (people leaving don't take institutional memory)
• Compliance confidence (auditors see rigor, not scrambling)

Key Takeaways

Database documentation is not optional for IFS. It's the bridge between technical schema and business logic, between upgrades and stability, between onboarding and expertise.

1. Start with metadata you own: Keep entity/model descriptions, source comments, and the documentation registry consistent. Use database comments only for customer-owned objects where that access is supported; do not edit IFS-delivered metadata.

2. Choose one primary visual tool: SchemaSpy (free), DataGrip (dev-focused), or Dataedo (enterprise-grade). The tool matters less than the discipline.

3. Add a team wiki: Confluence, Markdown in Git, or Obsidian for business context, runbooks, and change history.

4. Automate baseline snapshots: Before every upgrade; commit to Git; diff after deployment. This prevents knowledge loss.

5. Make it iterative: You don't document the whole system in month one. You build it over 3–6 months as you customize IFS. Celebrate small wins.

6. Assign ownership: Someone (usually a senior DBA or architect) champions documentation; others contribute. Rotate responsibility quarterly.

7. Enforce in CI/CD: Documentation generation is part of your deployment pipeline, not an afterthought. It's as important as unit tests.

8. Review quarterly: Once per quarter, compare your actual schema against documented schema. Fix discrepancies immediately.

9. Involve the business: Don't just document technical tables. Document what each custom object means in business terms. That's where your real ROI is.

IFS implementations that prioritise database documentation can recover faster from incidents, upgrade with clearer evidence, and onboard new team members more consistently. Track incident diagnosis time, upgrade defects, and onboarding milestones to demonstrate whether the documentation investment is paying off.

Start today. Pick one tool, write one set of column comments, and commit to Git. Document one custom object. Then build from there. Your future self—and your team—will thank you.