finance-ddl-design

Financial investment research table creation and DDL design

Serves analysts and researchers at brokerages and funds, and those who supply data to them. Default assumptions:There is no written specification, or the specification is outdated; database and table comments are unreliable; the "conventions" of the existing database count only as observations, not facts. Task order: first understand the existing database, then act. Explore → extract conventions → compare against specifications → design → write DDL → deliver contract.

What this skill delivers

The user gives a table creation or table modification requirement, and you produce four things:

1. Convention inventory conventions.md: observations of naming, types, keys, partitioning, and comments obtained by exploring the existing database, with the evidence level and basis marked for each item.
2. Table creation DDL .sql: generated according to the engine dialect, with comments, constraints, partitioning, and indexes; compatibility migration takes priority;by default only produce the script, do not execute it。
3. Design description design.md: the purpose of each table, primary key, temporal semantics, units/time zone, adjustment basis, and relationship to existing tables.
4. Data contract contract.yaml: field semantic contract, isomorphic to the acceptance contract of finance-data-qc , serving as the source of the CONFIRMED basis for subsequent quality checks.

DDL and the contract are deliverables; the exploration process must be traceable and rerunnable.

How to use this skill (routing overview)

SKILL.md only gives the process skeleton and distribution rules. The specific methods are under references/ ,loaded on demand according to trigger conditions, do not read them all at once。

Stage	Action	When to load which reference
0 Boundaries	Confirm purpose, market, instrument type, time range, engine, deliverables, and execution permissions	No reference needed
1 Engine	Identify the engine and dialect, determine the read-only exploration method and DDL dialect	No reference needed; for dialect differences see the respective references
2 Explore conventions	Read metadata and existing DDL, extract the convention inventory	Required reading references/exploration.md
3 Compare against specifications	Adjudicate naming, types, constraints, and partitioning according to the specifications	references/naming.md、references/types-constraints.md、references/partitioning.md Read as needed; for the external specification index see references/external-standards.md
4 Investment research scenarios	Determine which investment research scenario the new table belongs to, and take the corresponding table creation paradigm	references/investment-research.md corresponding section
5 Design adjudication	Set priorities when conventions vs. specifications vs. user requirements conflict	Return to the conflict handling in exploration.md
6 Write DDL	Generate dialect DDL, comments, constraints, partitioning, migration	references/partitioning.md、references/investment-research.md
7 Delivery	Produce conventions.md + DDL + design.md + contract.yaml	references/data-contract.md

Investment research scenario reference (Stage 4, read the corresponding section according to the table content)

Table content	Which section of investment-research.md to read
Security master data, code systems, trading calendar, listing and delisting	Sections 2 and 3
Market quotes, adjustment factors, ex-rights and ex-dividends, minute/daily frequency	Section 4
Financial statements, earnings forecasts and flash reports, valuation metrics, earnings estimates	Section 5
Exchange announcements, announcement events, structured events extracted from text	Section 6
Indices, index fund net asset values, shares, dividends, constituent stocks	Section 7
Macro indicators, industry classification, theme and concept mappings	Section 8

When one table spans multiple scenarios (such as an "index constituent stock weight table"), read all relevant subsections, but designate one primary scenario.

Three-tier specification: hard requirements, conventions, to be confirmed

Before writing any DDL, first layer it:

• Hard requirements (MUST): The non-negotiable bottom line of this skill. See the next section, "Hard Requirements".
• Conventions (follow the existing database): The existing database's naming style, type choices, and partitioning habits. If the same pattern appears in the database at least 3 times with no conflict, it is considered a strong convention, and new tables should preferentially follow it; when a strong convention conflicts with an external template, follow the convention and record the deviation in the documentation.
• To be confirmed (ask the user): Unit definitions, adjustment method, time zone, PIT semantics, conflicts between old and new specifications, and execution permissions. Do not write into the DDL or contract before confirmation.

Hard Requirements

1. Exploration is read-only. Use only read-only accounts and read-only statements; DDL/DML is prohibited during the exploration phase.
2. Budget first. Exploration queries declare row counts, scan volume, and time limits; unbounded SELECT * and full-database SHOW CREATE TABLE -style scans are prohibited.
3. Do not execute by default. DDL is delivered as scripts by default; executing write operations requires an explicit user request plus a dedicated account with write permissions; destructive changes (dropping columns, changing types, rebuilding) require separate confirmation.
4. Comments are mandatory. Every table and every business column must have a comment; column comments must state the definition, unit, and source; example values must use desensitized values.
5. Exact numerics. Amounts, prices, quantities, and net asset values use DECIMAL or the engine's equivalent exact type; FLOAT/DOUBLE is prohibited for storing amounts and prices.
6. Explicit time semantics. Distinguish event time, availability time, and ingestion time; time zones must be explicitly declared; trading days use DATE, and event times use timestamps with time zones.
7. Primary key must be declared. Every table must declare a primary key or unique key; the column order of a composite key equals the filtering granularity order.
8. Evidence grading. Exploration observations are marked OBSERVED, semantic inferences are marked INFERRED, and only those confirmed by the user or supported by a versioned specification are marked CONFIRMED and written into the contract.

Execution Flow

1. Confirm the boundaries. Record the purpose, market, instrument type, entity scope, time range, engine, deliverables (DDL/design document/contract), and whether write operations are permitted.
2. Identify the engine. Determine the storage engine and dialect (MySQL, PostgreSQL, ClickHouse, Doris, StarRocks, MaxCompute, DolphinDB, etc.), and decide the metadata query syntax and DDL dialect accordingly. This step must be completed before the first exploration query.
3. Explore the conventions. Read metadata: table list, columns, types, nullability, defaults, comments, primary/unique keys, indexes, partitions, existing DDL; layered sampling to inspect a small number of real rows and confirm the semantics. See references/exploration.md。
4. Check against the specification. As needed, read the three references on naming, type constraints, and partitioning, and classify the exploration results into three layers: "hard requirements/conventions/to be confirmed".
5. Adopt the investment research paradigm. According to the scenario table above, read the references/investment-research.md corresponding section, and adopt the suggested primary key, temporality, units, and adjustment conventions, without copying the example field names verbatim.
6. Design adjudication. When conventions conflict with the specification, follow this priority: explicit user requirements > strong in-database conventions (consistent in ≥3 places) > external specifications; record the rationale and impact for every deviation.
7. Write the DDL. Generate per dialect: table comments, column comments, constraints, partitions, indexes, compatible migration (ADD COLUMN preferred). See references/partitioning.md。
8. Deliver. Produce the conventions.md、DDL、design.md、contract.yaml four-piece set; self-check whether all hard requirements of the specification are satisfied.

For positive and negative examples, see examples/few-shots.md。

Deliverable requirements

• Convention list conventions.md: for each convention, the observation, evidence level (OBSERVED/INFERRED), basis, and disproof conditions; mark the occurrence count for strong conventions.
• DDL .sql: in the engine's native dialect, including table comments and per-column comments, primary/unique keys, CHECK and default values, partitions and indexes; for migration-type changes, provide a backward-compatible form; file names carry a version number (e.g. 0001_create_dwd_market_daily_bar.sql）。
• Design description design.md: the purpose and consumers of each table, the rationale for the primary key design, the responsibilities of temporal columns, units and currencies, time zone, adjustment convention, PIT semantics, reference relationships with existing tables, and the list of items to be confirmed.
• Data contract contract.yaml: with finance-data-qc/references/deliverables.md 's contract.yaml isomorphic, and add the schema field description column to define columns. Write only the CONFIRMED semantics, write null for unconfirmed ones and include them in the list of items to be confirmed; for each semantics give a recomputable formula(referencing fields of the same contract, declaring the time semantics and filters), so that quality inspection can independently recompute and verify. See references/data-contract.md。

Situations that must stop

• The connection information contains plaintext passwords, private keys, or tokens that would be written into deliverables.
• Only a write-permission account is available, and a read-only exploration boundary cannot be established.
• The task requires directly executing destructive DDL (delete, change type, rebuild) on the production database, and the user has not given explicit approval for the specific statements.
• The exploration query cannot restrict projection, entity, time, partition, or result limit, and may form an unbounded scan.
• The engine dialect is unknown but executable DDL is required; confirm the engine first before continuing.
• The exploration sample would expose unmasked account, position, transaction, or customer information.
• The requirement is substantively a final conclusion on quality inspection, pipelines, query interfaces, governance, or compliance: save the exploration observations, and return to using-data-dev to select the corresponding responsibility.